What syntax for MySQL would I use to drop multiple tables that have a similar pattern to them? Something like:
DROP TABLES FROM `Database1` LIKE "SubTable*"
Add a comment
|
4 Answers
Since DROP TABLE was supported by prepared statements, it can be done in this way -
SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables FROM information_schema.tables
WHERE table_schema = 'Database1' AND table_name LIKE 'SubTable%';
SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
Comments
As noted on this question, the responses given here (Angelin and Devart) won't work in all circumstances without first increasing the limit of group_concat, as per below:
SET group_concat_max_len = 1024 * 1024 * 10;
Comments
No. But you can select tables names from information_schema database:
select table_name
from information_schema.tables
where table_schema = 'Database1'
and table_name like 'SubTable%'
And after that iterate the table names in result set and drop them
4 Comments
rollingcodes
How would I iterate that using MySQL?
Warren Sergent
It is a shame that this was marked as the correct answer, when both the other answers given actually provide methods for doing exactly as required.
zerkms
@Warren Sergent - spakatak.com: there is no "something like" OP required. There is something that takes info from
information_schema and generates queries, as I stated in my answer. You know, sometimes answers implies you need to think for a second, not just to eat free fish.rollingcodes
thanks @zerkms i followed your train of thought and simply looped through the table names listed in the information_schema. worked like a charm
mysql> SELECT CONCAT( "DROP TABLE ", GROUP_CONCAT(TABLE_NAME) ) AS stmt
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME LIKE "ur condition" into outfile '/tmp/a.txt';
mysql> source /tmp/a.txt;
