4

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?

2
  • Even if you find a solution that technically works this really, really feels like hitting a nail with the wrong hammer. If you're familiar w/ shell scripting I imagine you have another scripting language up your sleeve. Perl? Python? Php? Use their libraries for a more elegant solution.
    – atxdba
    Feb 2, 2012 at 3:54
  • Agreed! But this was a one-off quick data fix. (see my comment to @RolandoMySQLDBA below) Feb 2, 2012 at 10:23

2 Answers 2

3

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

1
  • 1
    Thanks. I went for something very similar: while read i ; do if [[ -z $(mysql_one -A -B -N -e "SELECT id FROM myTable WHERE id="$i") ]]; then echo "$i" ; fi; done <ids-list >ids-not-found Feb 2, 2012 at 10:27
2

you can try this too as one-liner

(( $(mysql ... -e "SELECT COUNT(1) FROM table WHERE yourcondition") ))

will return 1 (error) on 0 rows and 0 (ok) on rows found.

https://tldp.org/LDP/abs/html/dblparens.html

1
  • 1
    Thanks MatteoBee! I like that this is succinct, however, it's not clear from reading it what is happening, unless you deal with bash all the time and are familiar with the rather quirky (( )). So for the sake of Kernighan's law I'll stick with [[ -z ... ]] :-) Dec 22, 2021 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.