MySQL array 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 is not working! What is equivalent for something similar in SQL? I can pull out a simple Python script to do this, but just wondering if we can do something with SQL directly. I am using MySQL. Thank!

Answer 1

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 an operator like this -

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

Answer 2

A slight 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 several times until the console output is NULL

Changes:

• backtick (`) wraps the table name (if it contains non-standard characters)
• added LIMIT to avoid the truncation problem that I commented on
• added "print" ( select @tables; ) to have some control when to terminate the script

Answer 3

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

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

Answer 4

It was useful for me to add IFNULL solutions to Devart to avoid generating an error if there are no tables matching the query.

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