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_'
-
Similar question - stackoverflow.com/questions/4958424/…Devart– Devart2011-07-20 11:07:20 +00:00Commented Jul 20, 2011 at 11:07
Add a comment
|
8 Answers
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;
1 Comment
Warren Sergent
Thank you for this - without the group_concat_max_len increase, Angelin's answer would never have worked for my scenario.
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;
Comments
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
1 Comment
baptx
I 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 just DROP 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 with sudo mysqldump > dump.sql and then logged in as root through Unix socket with sudo mysql before selecting the database with use db_name and executed the SQL file with \. drop.sql.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**%';
Comments
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_ ..........;
Comments
For the great mysqldump solution it's better to use the option --skip-quote-names
mysqldump --skip-quote-names -u user -p database > dump.sql
grep "DROP TABLE wp_" dump.sql > drop.sql
mysql -u user -p database < drop.sql
You get rid of backticks in table names. The grep part won't work in some enviroments with the backticks.
1 Comment
baptx
You can make the backtick work with grep if you escape it with the character "\": stackoverflow.com/questions/6758652/…
Go to c:\xampp\mysql\data\your folder
Select multiple tables that you want remove and then press delete button
Thanks
1 Comment
Jeremy Harris
This makes the assumption that the original poster is using XAMPP and has it installed to that directory.
Dropping single table in mysql:
DROP TABLE TABLE_NAME;
