From mysql prompt:
$ /usr/bin/mysql -u root
...
...
mysql> select * from NMS.main;
+------+-------+
| id | data |
+------+-------+
| 1 | slow |
| 3 | pizza |
| 4 | bash |
| 6 | awk |
+------+-------+
4 rows in set (0.00 sec)
mysql> select id,data from NMS.main
-> where id in (3,4);
+------+-------+
| id | data |
+------+-------+
| 3 | pizza |
| 4 | bash |
+------+-------+
2 rows in set (0.00 sec)
Now if we have to automate the same stuffs inside a bash script, this is how we can achieve that.
$ cat execmysql.sh
#!/bin/sh
ids="3,4"
table="NMS.main"
qry="select id,data from $table where id in ($ids)"
echo "Executing the following query"
echo $qry
/usr/bin/mysql -u root << eof
$qry
eof
Executing:
$ ./execmysql.sh
Executing the following query
select id,data from NMS.main where id in (3,4)
id data
3 pizza
4 bash
Posts
8 comments:
Small UTF-8 conversion script I came up with some time ago for mysql 5.x+ databases:
#!/bin/bash
P=password
D=database
U=username
mysql -u$U -p$P <<< 'alter database '$D' default character set utf8;'
mysql -u$U -p$P -D$D <<< 'show tables;' | while read x; do mysql -u$U -p$P\
-D$D <<< 'alter table '$x' convert to character set utf8;'; done
Of course one can use any other encoding too.
Thanks for small and concise post ;)
thank you from morrocco ;)
thank you for your post ;)
Jamal
Nice! My 2 cents contribution to catch an eventual error, let's say a duplicate entry:
#!/bin/bash
mysqlupdate=`(mysql -u $user -p$password -e "INSERT INTO company.employees VALUES (NULL,'John')") 2>&1`
if [[ "$mysqlupdate" =~ 'ERROR 1062' ]]
then
echo "There is already a employee called John. There cannot be two Johns in the same company says a new corporate rule"
else
echo 'John has been added as a new employee'
fi
thanks for the quick tip.Quite useful:)
Very useful!
I was struggling with mysql not liking my -e $sqlcmd, and your answer means i don't have to resort to exporting to a file either.
Very useful,
mysql -e $sqlcmd
wasn't working and this resolved it without having to resort to creating a file.
Post a Comment