Bash Script to Insert Values in MySQL

Insert into mysql from Bash script

You can pass the commands in a here-document, like this:

mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME << EOF
INSERT INTO $TABLE (\`id\`, \`day\`, \`time\`, \`rank\`) VALUES (NULL, "$day", "$time", "$rank");
EOF

Notice that the ` need to be escaped.
I also removed the QUIT command,
as it's unnecessary (good tip @Ven, thanks).

Actually, since those column names don't contain special symbols,
you don't actually need to quote them, and write the INSERT query a bit simpler, like this:

mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME << EOF
INSERT INTO $TABLE (id, day, time, rank) VALUES (NULL, "$day", "$time", "$rank");
EOF

Using shell script to insert data into database

Here's a simpler example that reproduces your problem:

for el in foo bar
do
  echo "$el"
done | head -n 1

echo "This is blank: $el"

This happens because the for loop and your mysql statement are not connected in any way. You have to get the data from your loop/pipeline to mysql.

The simplest way of doing this might be a while read loop:

for el in foo bar
do
  echo "$el"
done | head -n 1 | while read -r line
   do
      echo "This is not blank: $line"
   done

In your example, this would be:

#!/bin/bash
N=1
ARRAY=( adssa asdsa fdgfd vcbxcxv )
for el in "${ARRAY[@]}"
do echo $el
done | shuf | head -$N | while read -r line
do 
mysql -u root -pPass somebase << EOF
  INSERT INTO sometable (name) VALUES ('$line');
  SELECT * FROM site_user;
EOF
done

The simpler way would be:

#!/bin/bash
n=1
array=( adssa asdsa fdgfd vcbxcxv )
printf "INSERT INTO sometable (name) VALUES ('%s');\n" "${array[@]}" | \
  shuf | head -n $n | mysql -u root -pPass somebase

Using shell script to insert data into remote MYSQL database

The insert statement has to be sent to mysql, not another line in the shell script, so you need to make it a "here document".

mysql --host=randomhost --user=randomuser --password=randompass randomdb << EOF
insert into table (field1,field2,field3) values('http://www.site.com/$hash','$file','$size');
EOF

The << EOF means take everything before the next line that contains nothing but EOF (no whitespace at the beginning) as standard input to the program.

How to insert content of a file in different fields in mysql database using shell script?

This would be far easier if you use LOAD DATA INFILE (see the manual for full explanation of syntax and options).

Something like this (though I have not tested it):

inotifywait -m /home/a/b/c -e create -e moved_to |
while read path action file; do
  for filename in `ls -1 /home/a/b/c/*.txt`  
  do
     mysql datatable -e "LOAD DATA LOCAL INFILE '$filename' 
       INTO TABLE table_entries (name, address, contact_no, email) 
       SET file='$filename'"
  done
  find /home/a/b/c -type f -name "*.txt" -delete
done

edit: I specified mysql datatable which is like using USE datatable; to set the default database. This should resolve the error about "no database selected."

The columns you list as (name, address, contact_no, email) name the columns in the table, and they must match the columns in the input file.
If you have another column in your table that you want to set, but not from data in the input file, you use the extra clause SET file='$filename'.

You should also use some error checking to make sure the import was successful before you delete your *.txt files.

Note that LOAD DATA INFILE assumes lines end in newline (\n), and fields are separated by tab (\t). If your text file uses commas or some other separator, you can add syntax to the LOAD DATA INFILE statement to customize how it reads your file. The documentation shows how to do this, with many examples: https://dev.mysql.com/doc/refman/5.7/en/load-data.html I recommend you spend some time and read it. It's really not very long.

Inserting data in mysql with shell script

Thanks for your help. I have tried adding your line in my script and it was primarily giving some errors then I changed the command like below -

mysql -u root --execute="source /usr/local/insert.sql; \q"

Above line helped me to execute my command.

Thanks to all for being this much helpful.

Regards,
Shah9il

---

Related Topics