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

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

This should work

if [ $(mysql -u root -p -e \
  "select count(*) from information_schema.tables where \
     table_schema='db_name' and table_name='table_name';") -eq 1 ]; then
echo "table exist"
   exit 1
else
   echo "table doesn't exist"
   exit 1
fi

I think below will do your work,

#!/bin/bash

if [ $(mysql -u user -ppassword -hserver dbname -sse "select count(*) from test_dec;") -gt 0 ];
then
        echo "table not empty"
else
        echo "table empty"
fi

3 changes from your script,

1. enclose whole mysql statement part inside $(..) --> to make LHS the result of what goes inside $(...)
2. change -e to -sse in mysql connection statement --> to get only result of query to output without header and table structure.
3. Change != to -gt --> operator for integer comparison in bash

This works for me when testing if we need to re-import a database.

databasename="mydatabasename";
tablename="tableweneed";
dbhost="localhost";
username="user";
password="password";

dbtest=$(mysql -h ${dbhost} -u ${username} -p${password} -s -N -e "select count(*) as tablecount from information_schema.tables WHERE table_schema='${databasename}' and table_name='${tablename}'")

if [ "$dbtest" == 1 ]; then
    echo "Database is ok"
else
    echo "Database is being re-imported"
    mysql -h ${dbhost} -u ${user} -p${password} ${databasename} < /somefolderonmysystem/importdb.sql
fi

After trying a few different methods, this is what we used:

if [[ $(mysql --execute "SHOW TABLES FROM ${DB_NAME} LIKE '${DB_PREFIX}options';") -gt 0 ]]; then

...and yes, we define mysql as a function earlier in the script, and the database name and database prefix are also defined in a configuration file (this is part of our SlickStack project).

function mysql {
    command mysql --user=root --host=localhost --protocol=socket --port=3306 --force "$@"
}

You can change the name of this function in your bash script as desired.

Example: https://github.com/littlebizzy/slickstack/blob/master/bash/ss-install-wordpress-config.txt