Get Output of Mysql Query in Bash/Terminal

When performing a non-READ MySQL query from the terminal or a bash script, it’s normal to get no output.

bash: mysql -e "DELETE FROM table LIMIT 1" # no output
 
bash: mysql -e "SELECT ROW_COUNT();" # has output
+-------------+
| ROW_COUNT() |
+-------------+
|           0 |
+-------------+

In my case, I need a bash script to delete a vast number of records. When no rows are affected, then need to perform PURGE and OPTIMIZE.

To achieve this, I require the output when running the DELETE query to look like this:

Query OK, 1 rows affected (0.023 sec)

However, as stated above, non-READ queries produce no output when executed from the terminal or bash.

Fortunately, the solution is simple: just add the -vv flag to mysql -e to make it more verbose.

bash: mysql -e "DELETE FROM table LIMIT 1"
 
--------------
DELETE FROM table LIMIT 1
--------------
 
Query OK, 1 row affected (0.008 sec)
 
Bye

Now, I can retrieve the affected rows from the bash script.

check_affected_rows() {
    local SQL_COMMAND="$1"
    local OUTPUT
    local ROWS_AFFECTED
 
    # Execute the SQL command and capture the output
    OUTPUT=$(mysql -vv -e "$SQL_COMMAND" 2>&1)
 
    # Check if the query was successful
    if [[ "$OUTPUT" =~ "ERROR" ]]; then
        echo "Error executing SQL command:"
        echo "$OUTPUT"
        exit 1
    fi
 
    ROWS_AFFECTED=$(echo "$OUTPUT" | grep -oP 'Query OK, \K[0-9]+')
    echo "$ROWS_AFFECTED"
}
 
ROWS_AFFECTED=$(check_affected_rows "DELETE FROM table WHERE created_at < NOW() - INTERVAL 3 DAY ORDER BY id LIMIT 10;")
 
if [ "$ROWS_AFFECTED" -gt 0 ]; then
    # Do something when has rows affected
else
    # Do something when has no rows affected
fi