Mysql – thesql -e option with variable data – Pass the variable value to insert sql statement in shell script

MySQLshell-scripting

The following shell script is not inserting the data to the table. How to pass the variable value to insert sql statement in a shell script.

id=0
while true
do
id=`expr $id + 1`;
mysql -u root -ptest --socket=/data/mysql1/mysql.sock -e 'insert into mytest1.mytable2(id,name) values (' $id ',"testing");'
echo $id  >> id.txt
done

I have modified the script as below and tried, and still having the issue

id=0
while true
do
id=`expr $id + 1`;
# mysql -u root -ptest --socket=/data/mysql1/mysql.sock1 -e 'insert into mytest1.mytable1(name) values ("amma");'
mysql -u root -ptest --socket=/data/mysql1/mysql.sock -e 'insert into mytest1.mytable2(id,name) values ( $id ,"testing");'
echo $id  >> id.txt
done

error :

]$ ./insert 
ERROR 1054 (42S22) at line 1: Unknown column '$id' in 'field list'

Best Answer

This one should work:

id=0
while true
do
    id=`expr $id + 1`;
    mysql -u root -ptest --socket=/data/mysql1/mysql.sock -e "insert into mytest1.mytable2(id,name) values ($id, \"testing\");"
    echo $id  >> id.txt
done

Variables within double quotes are interpolated, while those found between single quotes are not.

Related Topic