MySQL Select If Table Exists

Question

I need to run a count query on a table but only if that table exists,

SELECT 
CASE WHEN (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'DATABASENAME' AND TABLE_NAME = 'testtable') < 1 THEN '0' 
ELSE (SELECT COUNT(*) FROM testtable) END;

The above query should return 0 if the table doesn't exist, but if it does it should get the count.

This returns an error saying "testtable" doesn't exist, we know it doesn't exist as the information_schema count returns 0.

Is this possible in MySQL?

Answer 1

You can try this :

       SET @val := CASE (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'DATABASENAME' AND TABLE_NAME = 'testtable')
           WHEN 0 THEN 0
           ELSE (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'DATABASENAME' AND TABLE_NAME = 'testtable')

END;
        SELECT @val;

It will return 0, if there is no such table and if such table exists , it will return the count, it may be better if you take it into function.