Verification: a143cc29221c9be0

Mysqli if table exists php



So, this answer has been marked down at least twice as of the time I am writing this message. Assuming that I had made some gargantuan error, I went and I ran some benchmarks, and this is what I found that my solution is over 10% faster than the nearest alternative when the table does not exist, and it over 25% faster when the table does exist:

:::::::::::::::::::::::::BEGINNING NON-EXISTING TABLE::::::::::::::::::::::::::::::
23.35501408577 for bad select
25.408507823944 for select from schema num rows -- calls mysql_num_rows on select... from information_schema.
25.336688995361 for select from schema fetch row -- calls mysql_fetch_row on select... from information_schema result
50.669058799744 for SHOW TABLES FROM test
:::::::::::::::::::::::::BEGINNING EXISTING TABLE::::::::::::::::::::::::::::::
15.293519973755 for good select
20.784908056259 for select from schema num rows
21.038464069366 for select from schema fetch row
50.400309085846 for SHOW TABLES FROM test

I tried running this against DESC, but I had a timeout after 276 seconds (24 seconds for my answer, 276 to fail to complete the description of a non existing table).

For good measure, I am benchmarking against a schema with only four tables in it and this is an almost fresh MySQL install (this is the only database so far). To see the export, look here.


This particular solution is also more database independent as the same query will work in PgSQL and Oracle.

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.