Alvin SIU Alvin SIU - 1 year ago 50
SQL Question

Can MySQL give out non-zero return code when non-empty result set?

I am using Linux and MySQL 5.6

I run the mysql this way:

mysql --user=XXX --password=XXX < myfile.sql

Here is the content of myfile.sql

select * from account where balance < 0

What I want to do is that:

when there is at least one account with negative balance, give out a non-zero return code.

Then my script can check this situation like this:

mysql --user=XXX --password=XXX < myfile.sql
if [[ $? -gt 0 ]]
echo ERROR: Negative balance

Can I achieve this ?

Or, is there any alternate methods.

Answer Source

Change your SQL to count the rows, rather than return the data:

select COUNT(*) as total from account where balance < 0

Then capture the output which is the resulting row data (rather than the returned result code in $? which is just the success of the query execution), skip the first line (the column alias 'total') with sed, then the second line is your count of rows.

out=$(/opt/local/lib/mysql5/bin/mysql --user=XXX --password=XXX < myfile.sql | sed -n 2p)
if [[ $out -gt 0 ]]
    echo ERROR: Negative balance

I've tested this in MacOSX not Linux, but the principle should be the same.

(And of course - you're not going to embed your database password in the source code, are you!)