-   Linux - General (
-   -   mysql in bash script (

slibbe 11-30-2011 09:19 AM

mysql in bash script

when I want to query some mysql database from bash, I can use the following construction as long as the query is simple:
QRY="$(mysql -u$USER -p$PASS $DB -Bse 'select * from table where userid=10')"

How do I manage to do something similar when the query itself allready has quotes? Like:
select * from table where created='2011-10-10 09:00:05' .
Now QRY="$(mysql -u$USER -p$PASS $DB -Bse 'select * from table where created=`2011-10-10 09:00:05` and userid=10')" doesn't work.



corp769 11-30-2011 09:58 AM

Instead of having 2011-10-10 09:00:05 in quotes, you should store it in a variable instead. Also, you used backticks in your example, instead of single quotes; Anything within backticks will be executed.

slibbe 12-01-2011 08:29 AM


SEL="select username, articleid"
WHC2="created='2011-10-10 09:00:05'"
Q1="$SEL from $TBL"
Q2="where $WHC1 and $WHC2"
QRY="$Q1 $Q2"
echo $QRY

does echo
select username, articleid from table where userid=10 and created='2011-10-10 09:00:05'

However, a line like

mysql -u$USER -p$PASS $DB -e "$QRY"

mysql -u$USER -p$PASS $DB -e $QRY
doesn't work whereas the double quotes are substituted by single quotes in the first line and omitted in the second.

slibbe 12-02-2011 09:37 AM

I got it!

Instead of

QRY="$(mysql -u$USER -p$PASS $DB -Bse 'select col1,col2,col3,datetime from table where col1=5 and datetime like '2010-04-01%' ')"
I can use

QRY=$(mysql -u$USER -p$PASS $db -e "select col1,col2,col3,datetime from table where col1=5 and datetime like '2010-04-01%'")
Don't know why the double quotes in the first line would be needed anyway. However if they are, you can do
'\''2010-04-01%'\'' instead of '2010-04-01%' (all single quotes).

Couldn't stand it not to find an answer for such a common problem :)

All times are GMT -5. The time now is 01:56 AM.