From a shell script, how can I check whether a table in MySQL database exists or not?

I am trying to write a script which allows a user to select the what manipulation he needs to do on a table. I want to check if the table exists or not. If it exists I will continue the other things or else I exit saying table doesn't exist. How might I achieve this.

Solution 1:

if [ $(mysql -N -s -u root -p -e \
    "select count(*) from information_schema.tables where \
        table_schema='db_name' and table_name='table_name';") -eq 1 ]; then
    do something
else
    echo "table <table_name> does not exist"
    exit 1
fi

• -N to skip column names
• -s for nontabular output

Solution 2:

In MySQL you can use -e from the shell

mysql -e "desc main_db.clients" > /dev/null 2>&1
echo $?

Solution 3:

If you want a method that doesn't involve running a mysql client and logging in to the mysql server, you can do this:

if ls -l /var/lib/mysql/database/table.frm 2> /dev/null > /dev/null
then
  echo Table exists
else
  echo Table does not exist
fi

You will have to replace the path to your data directory, your database name and your table name in the first line.

Caveats:

1. This will not work if you are using InnoDB and are not using innodb-file-per-table. i.e. The script can say the table does not exist even though it does.
2. It's possible for that file to exist for various reasons including copying tables around using the filesystem or switching from MyISAM to InnoDB. i.e. The script can say that the table does exist even though it doesn't.

It's not as reliable as the other methods presented already because of the reasons above but it avoids the mysql client so it may still be worthwhile.

Note that all methods presented here rely on your getting your own permissions (filesystem or MySQL) correct. If you get them wrong you will most likely get a false negative.