SQL：删除具有前缀的表

Question

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

注意：需要在phpMyAdmin中执行。

- Deniz Zoeteman

Answer 1

你无法仅通过一个MySQL命令完成它，但是你可以使用MySQL来构建该语句：

在MySQL shell或通过PHPMyAdmin中，使用以下查询语句：

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

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

编辑：此处需要声明 - 上面生成的语句将删除该前缀下所有数据库中的所有表格。如果您想将其限制为特定数据库，请修改查询以如下方式进行，并将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_%';

- Andre Miller

---

这个方法可以工作，但是只能删除大约20张表。我有一千多张要删除（自动生成的测试表）。为什么它不能给我所有的东西？ - Elijah Lynn

@ElijahLynn 限制来源可能是由于 GROUP_CONCAT 的最大长度。你可以扩大它，例如在这里查看（https://dev59.com/03E85IYBdhLWcg3w3Xr6） - Asaf David

自己的备忘录。增加最大字符数：SET SESSION group_concat_max_len = 999999999;。需翻译成中文时，翻译为："注意事项。增加最大字符数：SET SESSION group_concat_max_len = 999999999;"。 - Mohammed Joraid

请注意，在某些情况下，需要转义表前缀名称中的下划线 [...] LIKE 'myprefix\_%';。 - Magictallguy

这会返回许多重复的表名。我认为 Pankaj Khurana 发布的答案更好。 - Jeremy

Answer 2

之前的回答中有些非常好。我将它们的想法与一些其他网络回答的观点汇总在一起。

我需要删除所有以“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;

我希望这对其他MySQL/PHP程序员有所帮助。

- Bradley Slavik

---

这正是正确的答案。其他答案使用多个步骤或需要手动完成。不知道为什么这个答案在4年后没有更多的投票！ - gbe

这是正确的答案，适用于使用mysql命令行的人。无需复制粘贴，直截了当。 - Grogu

好东西，但对于带有连字符的表名无法正常工作。 - HyperActive

在许多Web Hosting平台上，您无法访问MySQL命令行，因此这个建议相对不太受欢迎。此外，许多人会更愿意在实际删除之前看到将要被删除的内容！ - deep64blue

Answer 3

show tables like 'prefix_%';

将结果复制并粘贴到文本编辑器中，或者将查询输出到文件中，使用一些搜索和替换操作来删除不需要的格式，并用逗号替换\n。在开头添加drop table，并在结尾加上;。

你将得到类似于以下内容：

drop table myprefix_1, myprefix_2, myprefix_3;

- Daniel

---

我在使用的 Web 主机上无法访问 information_schema.tables，所以这是解决问题的方法，谢谢！ - Florent Roques

Answer 4

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

注意：此代码依赖于平台，它适用于MySQL，但肯定可以通过轻微更改实现在Postgre、Oracle和MS SQL上。

- Alex Rashkov

---

错误 1064 (42000)：您的 SQL 语法有误；请检查与您的 MySQL 服务器版本相对应的手册，以获取正确的语法使用方式，在第 1 行附近出现了 '@tbls'。 错误 1243 (HY000)：未知的准备语句处理程序 (stmt) 给 EXECUTE。 错误 1243 (HY000)：未知的准备语句处理程序 (stmt) 给 DEALLOCATE PREPARE。 - Roni Tovi

语法错误：在 PREPARE stmt FROM 'DROP TABLE @tbls' 处。 - ledawg

给EXECUTE的未知准备好的语句处理程序（stmt） - undefined

Answer 5

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

- Pankaj Khurana

Answer 6

我通过编辑查询成功地删除了表格，如下所示：

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;

- Duong Vo

---

这是我找到的唯一一个在一个shebang中完成整个操作并解决外键约束的方法。 - Evan Mattson

Answer 7

这是另一种使用GROUP_CONCAT的解决方案，因此它将执行一个DROP查询，例如
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;

- Mohamad Hamouday

Answer 8

您可以使用MySQL中的一个命令来完成这个操作：

drop table myprefix_1, myprefix_2, myprefix_3;

尽管如此，您可能需要在代码中动态构建表列表。

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

- Asaph

Answer 9

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

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;

- Danny Beckett

---

当 @del 为空时，它会在 prepare stmt 中返回一个 MySQL 语法。我该如何避免这种情况？ - Hanh Nguyen

Answer 10

我发现对于我来说，准备好的语句有点难以使用，但当你有很多表时，设置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_%';

然后仔细剪切并粘贴生成的长DROP语句。

- drchuck

---

使用\G代替;来结束查询可以得到更干净的输出。 - Stuart Cardall