MySQL: how to drop multiple tables using single query?
Solution 1
Just sharing one of the solutions:
mysql> SELECT CONCAT( "DROP TABLE ",
GROUP_CONCAT(TABLE_NAME) ) AS stmtFROM 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;
Solution 2
I've used a query very similar to Angelin's. In case you have more than a few tables, one has to increase the max length of group_concat
. Otherwise the query will barf on the truncated string that group_concat
returns.
This is my 10 cents:
-- Increase memory to avoid truncating string, adjust according to your needs
SET group_concat_max_len = 1024 * 1024 * 10;
-- Generate drop command and assign to variable
SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema='databasename' AND table_name LIKE 'my_table%';
-- Drop tables
PREPARE str FROM @dropcmd; EXECUTE str; DEALLOCATE PREPARE str;
Solution 3
Simple solution without risk of error:
mysqldump
create a file that contains DROP
command like
DROP TABLE IF EXISTS `wp_matable`;
a 'grep
' with "DROP TABLE wp_
" give us the commands to execute
so drop is made by theses trhee lines (you can edit drop.sql to check which tables would be dropped before)
mysqldump -u user -p database > dump.sql
grep "DROP TABLE `wp_" dump.sql > drop.sql
mysql -u user -p database < drop.sql
Solution 4
Be careful with "_", need to be written with "\" before in Mysql like:
SELECT CONCAT('DROP TABLE',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema='databasename' AND table_name LIKE '**my\\_table**%';
Solution 5
A less complicated solution when a large number of tables are needed to be deleted -
SELECT GROUP_CONCAT(table_name SEPARATOR ", ")
-> AS tables
-> FROM information_schema.tables
-> WHERE table_schema = "my_database_name"
-> AND table_name LIKE "wp_%";
+-------------------------------------------------------------------------
| tables
+-------------------------------------------------------------------------
| wp_t1, wp_t2, wp_t3, wp_t4, wp_t5, wp_t6, wp_t7, wp_t7, wp_ ..........
+-------------------------------------------------------------------------
Copy the table names. Then use -
DROP TABLE
-> wp_t1, wp_t2, wp_t3, wp_t4, wp_t5, wp_t6, wp_t7, wp_t7, wp_ ..........;
Angelin Nadar
Everybody knows that something cant be done and then somebody turns up and he doesn't know that it can't be done and he does it.
Updated on July 09, 2022Comments
-
Angelin Nadar almost 2 years
I want to drop multiple tables with ease without actually listing the table names in the drop query and the tables to be deleted have prefix say 'wp_'
-
Warren Sergent over 10 yearsThank you for this - without the group_concat_max_len increase, Angelin's answer would never have worked for my scenario.
-
Jeremy Harris almost 8 yearsThis makes the assumption that the original poster is using XAMPP and has it installed to that directory.
-
baptx about 4 yearsI am not sure if there is an error in your answer but to make the grep command work, I had to write
DROP TABLE IF EXISTS
instead of justDROP TABLE
and I had to escape the backtick with the "\" character. For example:grep "DROP TABLE IF EXISTS \`table_prefix_" dump.sql > drop.sql
. I did the export simply withsudo mysqldump > dump.sql
and then logged in as root through Unix socket withsudo mysql
before selecting the database withuse db_name
and executed the SQL file with\. drop.sql
. -
baptx about 4 yearsYou can make the backtick work with grep if you escape it with the character "\": stackoverflow.com/questions/6758652/…