MySQL bulk drop table where table like?

Question

DROP TABLE (
SELECT table_name
FROM information_schema.`TABLES`
WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%');

I know this doesn't work! What is the equivalent for something like this in SQL? I can whip out a simple Python script to do this but was just wondering if we can do something with SQL directly. I am using MySQL. Thank you!

Answer 1

A minor improvement to @Devart's answer:

SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.`', table_name, '`') INTO @tables FROM
(select * from
 information_schema.tables 
  WHERE table_schema = 'myDatabase' AND table_name LIKE 'del%'
  LIMIT 10) TT;

SET @tables = CONCAT('DROP TABLE ', @tables);
select @tables;
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

This script should be executed repeatedly until the console's output is NULL

The changes are:

1. backtick (`) wrapping the table name (if it contains non standard characters)
2. added a LIMIT to avoid the truncation issue I commented about
3. added a "print" (select @tables;) to have some kind of control when to stop executing the script

Answer 2

I found it useful to add an IFNULL to Devart's solutions to avoid generating an error if there are no tables matching the query.

SET @tables = IFNULL(CONCAT('DROP TABLE ', @tables),'SELECT NULL;');

Answer 3

If you just need to quickly drop a bunch of tables (not in pure SQL, so not directly answering this question) a one line shell command can do it:

echo "show tables like 'fsm%'" | mysql | tail +2 | while read t; do echo "drop table \`$t\`;"; done | mysql

Answer 4

You can use prepared statements -

SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name,'`') INTO @tables FROM information_schema.tables 
  WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%';

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

It will generate and execute a statement like this -

DROP TABLE myDatabase.del1, myDatabase.del2, myDatabase.del3;