Check if MySQL table exists without using "select from" syntax?

Question

If you want to be correct, use INFORMATION_SCHEMA.

SELECT * 
FROM information_schema.tables
WHERE table_schema = 'yourdb' 
    AND table_name = 'testtable'
LIMIT 1;

Alternatively, you can use SHOW TABLES

SHOW TABLES LIKE 'yourtable';

If there is a row in the resultset, table exists.

SELECT count(*)
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = 'your_db_name') AND (TABLE_NAME = 'name_of_table')

if you get a non-zero count, the table exists.

A performance comparison:

• MySQL 5.0.77, on a db that has about 11,000 tables.
• Selecting a non-recently-used table so it's not cached.
• Averaged over 10 tries each. (Note: done with different tables to avoid caching).

322ms: show tables like 'table201608';

691ms: select 1 from table201608 limit 1;

319ms: SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'mydb') AND (TABLE_NAME = 'table201608');

Note if you're running this a lot -- like over many HTML requests in a short period -- the 2nd will be way faster since it'll be cached an average 200 ms or faster.

You can query the INFORMATION_SCHEMA tables system view:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'testtable';

If no rows returned, then the table doesn't exist.

After reading all of the above, I prefer the following statement:

SELECT EXISTS(
       SELECT * FROM information_schema.tables 
       WHERE table_schema = 'db' 
       AND table_name = 'table'
);

It indicates exactly what you want to do and it actually returns a 'boolean'.

Here is a table that is not a SELECT * FROM

SHOW TABLES FROM `db` LIKE 'tablename'; //zero rows = not exist

Got this from a database pro, here is what I was told:

select 1 from `tablename`; //avoids a function call
select * from INFORMATION_SCHEMA.tables where schema = 'db' and table = 'table' // slow. Field names not accurate
SHOW TABLES FROM `db` LIKE 'tablename'; //zero rows = does not exist

Rather than relying on errors, you can query INFORMATION_SCHEMA.TABLES to see if the table exists. If there's a record, it exists. If there's no record, it doesn't exist.