How to check if a table exists in a MySQL database using shell script?

Question

i am trying to check whether a table is empty or not using shell script the code that i have is

#!/bin/bash

if [ "mysql -u user -ppassword -hserver dbname -e 'select count(*) from test_dec;'" != 0 ];
then
        echo "table not empty"
else
        echo "table empty"
fi

but when i run this it always displays "table not empty" even if the output of the query is 0.

user@server$ ./table_check.sh
table not empty

what is wrong here?

Answer 1

This is my version of the script and it will first check if table exists and if yes will check if the table is empty.

BASH

#!/bin/bash

# Prepare variables
TABLE=$1
SQL_EXISTS=$(printf 'SHOW TABLES LIKE "%s"' "$TABLE")
SQL_IS_EMPTY=$(printf 'SELECT 1 FROM %s LIMIT 1' "$TABLE")

# Credentials
USERNAME=YOUR_USERNAME    
PASSWORD=YOUR_PASSWORD
DATABASE=YOUR_DATABASE_NAME

echo "Checking if table <$TABLE> exists ..."

# Check if table exists
if [[ $(mysql -u $USERNAME -p$PASSWORD -e "$SQL_EXISTS" $DATABASE) ]]
then
    echo "Table exists ..."

    # Check if table has records    
    if [[ $(mysql -u $USERNAME -p$PASSWORD -e "$SQL_IS_EMPTY" $DATABASE) ]]
    then
        echo "Table has records ..."
    else
        echo "Table is empty ..."
    fi
else
    echo "Table not exists ..."
fi

USAGE

First before using this script you need to replace YOUR_USERNAME, YOUR_PASSWORD and YOUR_DATABASE_NAME with the corresponding values. Then:

# bash SCRIPT_NAME TABLE_TO_CHECK
  bash my_script my_table

where SCRIPT_NAME( my_script ) is the name of the file holding the above script content and TABLE_TO_CHECK( my_table ) is the name of the table that you want to check for.

EXPECTED OUTPUT

Checking if table <my_table> exists ...  
Table exists ...   
Table is empty ... 

COUPLE OF WORDS ABOUT THE CODE

Store the first argument from the command line in variable TABLE

TABLE=$1

---

Prepare two variables that will hold the SQL queries used to check. Note that printf is used to insert the table name in the variables, because $('SHOW TABLES LIKE "$TABLE"') is not going to work.

SQL_EXISTS=$(printf 'SHOW TABLES LIKE "%s"' "$TABLE")
SQL_IS_EMPTY=$(printf 'SELECT COUNT(*) as records FROM %s' "$TABLE")

---

Check if table exists. SHOW TABLES LIKE "table_name" will return empty string if the table does not exist and the if statement will fail. Usage of the $ like $(echo 1 + 2) means - evaluate whatever is inside the parentheses and return that as a value.

if [[ $(mysql -u $USERNAME -p$PASSWORD -e "$SQL_EXISTS" $DATABASE) ]]

---

Finally we check if table is empty. Using the previous approach. Basically we check if MySQL will return empty string (for empty tables), otherwise the query will return some text as a result and we can consider the table not empty.

if [[ $(mysql -u $USERNAME -p$PASSWORD -e "$SQL_IS_EMPTY" $DATABASE) ]]