1

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 ]]
then
    echo ERROR: Negative balance
fi

Can I achieve this ?

Or, is there any alternate methods ?

2016-05-28 update:

Actually, my real life case is complicated. I use this simple example is for easy expressing my problem.

Actually is there anything like the [ return ] statement in the shell script function so that I can give out different return codes according to different situations ?

For the accepted answer below, look like MySQL cannot generate a self-defined return code. Putting a count(*) as the last line of the output display seems to be an alternative.

Alvin SIU
  • 1,022
  • 10
  • 28

1 Answers1

2

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 ]]
then
    echo ERROR: Negative balance
fi

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!)

scipilot
  • 6,681
  • 1
  • 46
  • 65