MySQL – Delete/Drop all tables with specific prefix

Drop Table with predix or wildcardMySQL is great database especially for small and mid size web applications. You can do loads of stuff with it but not everything is possible with regular SQL query.

Imagine you have a database and you want to delete set of tables. Most of the database, if not all, I create I use table prefix like tbl_users, tbl_orders, etc. This is mainly done for improving database security which could be serious problem for Open Source applications where the database scheme is publicly accessible and this could be used for attacking your website with SQL Injection techniques.  The prefix makes your tables have relatively unique or difficult to guess names but there other reasons as well.

But imagine you have a database with tables with different prefixes and you want to delete/drop just one of the sets: e.g. set1_users and set2_users.

In many other applications you can use wildcard or even Regular Expressions to match specific records but that’s not case in SQL or MySQL.  at least not for the DROP TABLE clause. So how to solve the problem?

You cannot do it with just a single MySQL command, however you can use MySQL to construct the statement for you.
[cc escaped=”true” lang=”sql”]SELECT CONCAT( ‘DROP TABLE ‘, GROUP_CONCAT(table_name) , ‘;’ ) AS statement
FROM information_schema.tables
WHERE table_name LIKE ‘set1_%’;[/cc]
This won’t delete the tables but it will produce the SQL that can be executed. Be aware that information_schema.tables includes all tables of all databases. So to extend further the SQL command we can specify database name like that:
[cc escaped=”true” lang=”sql”]SELECT CONCAT( ‘DROP TABLE ‘, GROUP_CONCAT(table_name) , ‘;’ ) AS statement
FROM information_schema.tables
WHERE table_schema = ‘database_name’ AND table_name LIKE ‘set1_%’;[/cc]
All that looks pretty good with exception of one thing, if you want to automate it you’ll need server side scripting so you can’t execute it just as SQL. The solution is using STATEMENTS which will allow you to define the SQL and execute it by passing the tables you want to delete. Here is an example:
[cc escaped=”true” lang=”sql”]SET @tables = (SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_name LIKE ‘set1_%’);
PREPARE drop_statement FROM ‘DROP TABLE @tables’;
EXECUTE drop_statement USING @tables;
DEALLOCATE PREPARE drop_statement;[/cc]
So now this looks perfect. Exactly what we needed. Imagine you want to create an automated build with Phing and you want to delete specific tables every time you run “phing build:build” so this SQL would make it possible using EXCUTE and PREPARE statements.

If you have any ideas or better solutions feel free to add a comment!


Just a guy with strong interest in PHP and Web technologies

Tagged with: , , ,
8 comments on “MySQL – Delete/Drop all tables with specific prefix
  1. Chris Dean says:

    Excellent Write-up, Thanks a lot

  2. Andru says:

    in extension to your post, this is a bash script that covers foreign key checks, group_concat limit and selective tables, one liner 🙂

    mysql -u DB_USER -pDB_PASS -e ‘SET SESSION group_concat_max_len = 10000000; SELECT CONCAT(“SET FOREIGN_KEY_CHECKS=0;”, “DROP TABLE “, GROUP_CONCAT(table_name), “;”) FROM information_schema.tables WHERE table_schema = “DB_NAME” and table_name like “%”‘ -s | xargs -I \ “@@” mysql -u DB_USER -pDB_PASS -D DB_NAME -e “@@”

  3. Bert says:

    Thank you!

    A small improvement that makes it less sensitive to special table names: replace “GROUP_CONCAT(table_name)” by “GROUP_CONCAT(CONCAT(‘`’,table_name,’`’))”

    It solved an error caused by having these table names: “table_name” and “table_name_01”.
    “table_name” was not considered a unique table or alias before adding the ticks.

  4. Constantin says:

    yes, it is a good solution, however it may cause some problems for databases with big amount of tables matching the pattern. Since the GROUP_CONCAT result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024 it can generate you a corrupt statement if the total length of table names matches will exceed the 1024 symbols. Just for your information! 😉

  5. Gerrit says:

    I get the following error in MYSQL:
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘@tables’ at line 1

  6. pixel fury says:

    I’ve been browsing online more than 4 hours today, yet I never found
    any interesting article like yours. It’s pretty worth enough for me.
    Personally, if all website owners and bloggers made good
    content as you did, the web will be much more useful than ever before.

  7. Wolfgang Siebeck says:

    IF you are not using INNODB so you have a folder with the name of the database and multiple files beginning with the tablename AND you can have root privileges THEN you can go to the folder (for example ‘/var/lib/mysql/dbname’) and execute ‘rm -f prefix*’ as root.
    Not beautiful, but it worked for me as a cleanup-operation after uninstalling phpbb …

  8. AGN says:

    I prefer using Python 3 and the official Python Connector to do things that the console can’t accommodate. A program written in Python (or any other programming language) to do the same thing is actually much more concise and much more readable than the SQL script you posted. Additionally, most programming languages these days come with a regex library as standard, which allows for more complex pattern matching.

    You could make things even simpler with pretty much any SQL abstraction libraries for your favorite programming language if you so wish.

Leave a Reply to Gerrit Cancel reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.