MySQL: how to drop multiple tables using single query?

Question

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_'

Areca

Similar question - #4958924 – Commutate

Answer 1

Just sharing one of the solutions:

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;

Areca

Answer 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;

Sputum

Thank you for this - without the group_concat_max_len increase, Angelin's answer would never have worked for my scenario. – Lozano

Answer 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

Damiondamita

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. – Willms

Answer 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**%';

Helmer

Answer 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_ ..........;

Loaf

Answer 6

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.

Liner

You can make the backtick work with grep if you escape it with the character "\": #6759152 – Willms

Answer 7

Go to c:\xampp\mysql\data\your folder

Select multiple tables that you want remove and then press delete button

Thanks

Carmeliacarmelina

This makes the assumption that the original poster is using XAMPP and has it installed to that directory. – Faulty

Answer 8

Dropping single table in mysql:

DROP TABLE TABLE_NAME;

Refugia