Search code examples
mysqldrop-table

Delete tables having exactly 10 rows in MySQL

Is it possible to delete tables with 10 records in a single statement using a statement like this?

.. DROP TABLE ... WHERE name IN 
(SELECT table_name FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_SCHEMA = 'my_db' AND table_rows = 10);
Solution

  • First of all, don't trust the table_rows in INFORMATION_SCHEMA.TABLES. It's an estimate, it's not precise. How could it be precise, given that at any given moment, there might be transactions uncommitted that either insert or delete rows? The number of rows in the table may or may not include those changes.

    Secondly, the DROP TABLE statement supports only a fixed list of tables to drop. Here's the syntax reference from http://dev.mysql.com/doc/refman/5.7/en/drop-table.html:

    DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

    This statement does not support a WHERE clause or any conditions. You must name the tables explicitly.

    You should develop the habit of reading documentation to find out what syntax is supported. This is the biggest complaint I have about questions on Stack Overflow: too many people skip reading reference documentation. 90% or more of the questions here would be unnecessary if they did.

    As @Barmar comments above, you can prepare a dynamic SQL statement with the list of tables you want to drop.

    Thirdly, dropping an indeterminate set of tables is very dangerous. You could drop the wrong tables, due to a bug in your code that finds the tables that match the criteria. This is sure to create a disaster of data loss you can't recover from. I never "automate" the dropping of tables.