MySQL SQL：删除带有前缀的表

SQL: deleting tables with prefix

提问by Deniz Zoeteman

How to delete my tables who all have the prefix myprefix_?

如何删除我所有都有前缀的表myprefix_？

Note: need to execute it in phpMyAdmin

注意：需要在phpMyAdmin中执行

回答by Andre Miller

You cannot do it with just a single MySQL command, however you can use MySQL to construct the statement for you:

您不能仅使用单个 MySQL 命令来完成此操作，但是您可以使用 MySQL 为您构建语句：

In the MySQL shell or through PHPMyAdmin, use the following query

在 MySQL shell 或通过 PHPMyAdmin，使用以下查询

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_name LIKE 'myprefix_%';

This will generate a DROP statement which you can than copy and execute to drop the tables.

这将生成一个 DROP 语句，您可以复制并执行该语句以删除表。

EDIT: A disclaimer here - the statement generated above will drop all tables in all databases with that prefix. If you want to limit it to a specific database, modify the query to look like this and replace database_name with your own database_name:

编辑：此处免责声明 - 上面生成的语句将删除具有该前缀的所有数据库中的所有表。如果您想将其限制为特定的数据库，请将查询修改为如下所示，并将 database_name 替换为您自己的 database_name：

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%';

回答by Bradley Slavik

Some of the earlier answers were very good. I have pulled together their ideas with some notions from other answers on the web.

一些早期的答案非常好。我将他们的想法与网络上其他答案的一些概念结合在一起。

I needed to delete all tables starting with 'temp_' After a few iterations I came up with this block of code:

我需要删除所有以 'temp_' 开头的表 经过几次迭代，我想出了以下代码块：

-- Set up variable to delete ALL tables starting with 'temp_'
SET GROUP_CONCAT_MAX_LEN=10000;
SET @tbls = (SELECT GROUP_CONCAT(TABLE_NAME)
               FROM information_schema.TABLES
              WHERE TABLE_SCHEMA = 'my_database'
                AND TABLE_NAME LIKE 'temp_%');
SET @delStmt = CONCAT('DROP TABLE ',  @tbls);
-- SELECT @delStmt;
PREPARE stmt FROM @delStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I hope this is useful to other MySQL/PHP programmers.

我希望这对其他 MySQL/PHP 程序员有用。

回答by Daniel

show tables like 'prefix_%';

copy the results and paste them into a text editor or output the query to a file, use a few search and replaces to remove unwanted formatting and replace \nwith a comma put a ;on the end and add drop table to the front.

复制结果并将它们粘贴到文本编辑器中或将查询输出到文件中，使用一些搜索和替换来删除不需要的格式并\n用逗号替换，将 a;放在末尾并将下拉表添加到前面。

you'll get something that looks like this:

你会得到看起来像这样的东西：

drop table myprefix_1, myprefix_2, myprefix_3;

回答by Alex Rashkov

@andre-millersolution is good but there is even better and slightly more professional that will help you execute all in one go. Still will need more than one command but this solution will allow you to use the SQL for automated builds.

@andre-miller解决方案很好，但还有更好、更专业的解决方案，可以帮助您一次性执行所有操作。仍然需要多个命令，但此解决方案将允许您使用 SQL 进行自动构建。

SET @tbls = (SELECT GROUP_CONCAT(TABLE_NAME) 
    FROM information_schema.TABLES
    WHERE TABLE_NAME LIKE 'myprefix_%');
PREPARE stmt FROM 'DROP TABLE @tbls';
EXECUTE stmt USING @tbls;
DEALLOCATE PREPARE stmt;

Note: this code is platform dependant, it's for MySQL but for sure it could be implemented for Postgre, Oracle and MS SQL with slight changes.

注意：此代码取决于平台，它适用于 MySQL，但肯定可以为 Postgre、Oracle 和 MS SQL 实现，只需稍作更改。

回答by Pankaj Khurana

SELECT CONCAT("DROP TABLE ", table_name, ";") 
FROM information_schema.tables
WHERE table_schema = "DATABASE_NAME" 
AND table_name LIKE "PREFIX_TABLE_NAME%";

回答by Duong Vo

I drop table successfully by edit query to like this

我通过编辑查询成功删除表

SET GROUP_CONCAT_MAX_LEN=10000;
SET FOREIGN_KEY_CHECKS = 0;
SET @tbls = (SELECT GROUP_CONCAT(CONCAT('`', TABLE_NAME, '`'))
           FROM information_schema.TABLES
          WHERE TABLE_SCHEMA = 'pandora'
            AND TABLE_NAME LIKE 'temp_%');
SET @delStmt = CONCAT('DROP TABLE ',  @tbls);
-- SELECT @delStmt;
PREPARE stmt FROM @delStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

回答by Mohamad Hamouday

Just another solution using GROUP_CONCATso it will execute one drop query like
DROP TABLE table1,table2,..

只是使用GROUP_CONCAT 的另一种解决方案，因此它将执行一个删除查询，如
DROP TABLE table1,table2,..

SET @Drop_Stm = CONCAT('DROP TABLE ', (
      SELECT GROUP_CONCAT(TABLE_NAME) AS All_Tables FROM information_schema.tables 
      WHERE TABLE_NAME LIKE 'prefix_%' AND TABLE_SCHEMA = 'database_name'
)); 
PREPARE Stm FROM @Drop_Stm; 
EXECUTE Stm;
DEALLOCATE PREPARE Stm;

回答by Danny Beckett

I just wanted to post the exact SQL I used - it's something of a mixture of the top 3 answers:

我只是想发布我使用的确切 SQL - 它是前 3 个答案的混合体：

SET GROUP_CONCAT_MAX_LEN=10000;

SET @del = (
    SELECT      CONCAT('DROP TABLE ', GROUP_CONCAT(TABLE_NAME), ';')
    FROM        information_schema.TABLES

    WHERE       TABLE_SCHEMA = 'database_name'
    AND         TABLE_NAME LIKE 'prefix_%'
);

PREPARE stmt FROM @del;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

回答by Asaph

You can do that in one command with MySQL:

您可以使用 MySQL 在一个命令中执行此操作：

drop table myprefix_1, myprefix_2, myprefix_3;

You'll probably have to build the table list dynamically in code though.

不过，您可能必须在代码中动态构建表列表。

An alternative approach would be to use the general purpose routine libraryfor MySQL 5.

另一种方法是使用MySQL 5 的通用例程库。

回答by drchuck

I found that the prepared statements were a little tricky to get working for me but setting the GROUP_CONCAT_MAX_LENwas essential when you have a lot of tables. This resulted in a simple three-step process with cut-and paste from the mysqlcommand line that worked great for me:

我发现准备好的语句对我来说有点棘手，但是GROUP_CONCAT_MAX_LEN当你有很多表时设置是必不可少的。这导致了一个简单的三步过程，从mysql命令行进行剪切和粘贴，这对我来说非常有用：

SET GROUP_CONCAT_MAX_LEN=10000;
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_name LIKE 'myprefix_%';

Then carefully cut-and-paste the resulting long DROPstatement.

然后小心地剪切并粘贴生成的长DROP语句。