Mysql – can thesql cli exit with error if no records returned

clientMySQL

I would like to execute a sql command in bash, and do something based on whether or not there was a result.

e.g.

$ LOOKUP=123; mysql -e \
    'SELECT id FROM table WHERE id='$LOOKUP dbname \
    && echo "Row $LOOKUP exists"
    || echo "Row $LOOKUP does not exists"

I can think of bodges (e.g. testing for ZLS) but wondered if there was a clean way?

Best Answer

Try changing the query to force a value

Instead of

SELECT id FROM table WHERE id=${LOOKUP}

you can use

SELECT COUNT(1) FROM table WHERE id=${LOOKUP}

Here is bash script to use that new query

LOOKUP=123
SQLSTMT="SELECT COUNT(1) FROM table WHERE id=${LOOKUP}"
RCOUNT=`mysql -u... -p... -AN -Ddbname -e"${SQLSTMT}"`
if [ ${RCOUNT} -eq 0 ]
then
    echo "Row ${LOOKUP} exists"
else
    echo "Row ${LOOKUP} does not exist"
fi

Here is an alternative

LOOKUP=123
SQLSTMT="SELECT CONCAT('Row ',rcount,' ',IF(rcount=0,'Exists','Does Not Exist')) FROM (SELECT COUNT(1) rcount FROM rolando WHERE id=${LOOKUP}) A"
mysql -u... -p... -AN -Ddbname -e"${SQLSTMT}" > /tmp/answer.txt
cat /tmp/answer.txt

Give it a Try !!!

Related Question