Check if a MySQL table exists

MySQL has a couple of ways (that I know of, there may be more) of working out if a table exists. This post looks at how to check if a table exists in the MySQL database.

Using show tables

The first way is using the “show tables” function. If your database (called “test” in this example) had three tables name “test1”, “test2” and “another_test”, running “show tables” would display this:

+------------------------+
| Tables_in_test         |
+------------------------+
| another_test           |
| test1                  |
| test2                  |
+------------------------+
3 rows in set (0.01 sec)

You can use show tables like this to see if a single table exists:

mysql> show tables like "test1";

which would return:

+------------------------+
| Tables_in_test (test1) |
+------------------------+
| test1                  |
+------------------------+
1 row in set (0.00 sec)

If you ran show tables on a table that didn’t exist you would get this:

mysql> show tables like "test3";
Empty set (0.01 sec)

So that’s one way of checking if a table exists in MySQL. You can use your programming language of choice to connect to the database, run a query like the above and then check if there are any rows to see if the table exists.

Note that you can also do e.g. “show tables like ‘test%'” which using the above tables would return both test1 and test2 if you needed this for another purpose.

Using the information schema

From MySQL 5.0 there is an information schema database which contains the information about the databases. This database can be used to determine various information, including whether or not a table exists in a given database in MySQL.

The syntax for this purpose would look like this:

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = '[database name]' 
AND table_name = '[table name]';

Using our examples above and checking to see if the “another_test” table exists we would do this:

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = 'test' 
AND table_name = 'another_test';

This seems to me to be a better way of checking for a table’s existence than the show tables method and is the one I prefer when using MySQL 5.0 or higher.