如何用一个命令在MySQL数据库中清空所有表格？

Question

是否有一条查询（命令）可以在一次操作中截断数据库中的所有表？我想知道是否可以用一条单独的查询来完成这个任务。

- devang

---

清空所有表格？你是指清空数据库中所有表格的所有列吗？这是哪种查询语言？（例如SQL，MySQL，Oracle，Postgres等） - Jay

我不确定我会建议这样做，因为你很容易陷入麻烦。你不能只编写脚本并运行脚本吗？ - GrayWizardx

谢谢回复，但我之所以不使用脚本是因为时间紧迫，所以想在MySQL中运行查询。 - devang

你可以使用游标与Information_Schema。不确定MySql是否支持Information_Schema.Tables，但应该是支持的。 - GrayWizardx

参见：如何在命令行中删除所有MySQL表？。 - kenorb

Answer 1

删除（即移除表）

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done

截断（即清空表）

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done

- battousaix

---

好的，清除干净，谢谢。但你可能需要使用-uUSER -pPASSWORD参数运行此命令。如果你有没有级联删除的外键约束，可能需要多次运行此命令。 - mihaicc

如果您遇到诸如“_无法删除或更新父行：外键约束失败_”之类的FK问题，请确保通过修改命令来禁用外键检查，具体操作如下：mysql -e "SET FOREIGN_KEY_CHECKS = 0; drop table $table" DATABASE_NAME。 - chemick

无需循环 MySQL 客户端。按如下方式将其从循环中取出：mysql -Nse 'show tables' DATABASE_NAME | while read table; do echo "drop table $table;"; done | mysql DATABASE_NAME - Alexander Shcheblikin

@TylerCollier 你需要一个 .my.cnf 文件。你可以参考这个链接来了解如何配置它（https://rtcamp.com/tutorials/mysql/mycnf-preference/）。 - Felix Eve

根据上面的答案和评论，因为它们对我没有按预期工作，我制作了一个简单的bash脚本，您可以用它来完成此操作。您可以在以下链接找到它：https://gist.github.com/mocanuga/eff26a3dcc40ef657a1c812f68511f6d - mocanuga

Answer 2

以下查询将为Mysql模式中所有数据库表生成单独的截断命令列表。(将dbSchemaName1替换为您的Db模式名称。)

SELECT CONCAT('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
    FROM INFORMATION_SCHEMA.TABLES WHERE table_schema IN ('dbSchemaName1','dbSchemaName2');

复制查询结果（可能看起来像以下内容），并将截断命令列表粘贴到MySQL Workbench或您选择的查询命令工具的SQL查询选项卡中：

TRUNCATE TABLE dbSchemaName1.table1;
TRUNCATE TABLE dbSchemaName1.table2;
TRUNCATE TABLE dbSchemaName1.table3;

注意：您可能会收到以下错误：

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

如果有表与您尝试删除/清空的表存在外键引用关系，则会出现此问题。

要解决此问题，请在运行截断命令之前关闭外键检查：

SET FOREIGN_KEY_CHECKS=0;  -- turn off foreign key checks
TRUNCATE TABLE dbSchemaName1.table1;  -- truncate tables
TRUNCATE TABLE dbSchemaName1.table2;
TRUNCATE TABLE dbSchemaName1.table3;
SET FOREIGN_KEY_CHECKS=1;  -- turn on foreign key checks

- lalit choudhary

---

如果您有数据库的用户名和密码，而且不想在 shell 中明文输入它们，那么这可能是最好的解决方案。 - Bach

因为这个解决方案只需要数据库访问权限而不需要SSH访问权限，所以我会点赞。此外，它还与操作系统无关。 - mastazi

@Ahmed，唯一需要更改的是您必须将db1_name和db2_name替换为您的数据库名称。然后，您需要复制此查询的结果，并将其粘贴为新的mysql查询并执行它们。INFORMATION_SCHEMA是每个MySQL安装中预安装的内置模式，保存有关您的数据库的信息，请勿触摸它，否则您的MySQL将开始出现问题:-) table_schema和table_name是information_schema中名为“TABLES”的表的列。 - mastazi

如何“使用查询结果”截断表格？ - The Onin

第一个查询将包含视图，并且对它们的TRUNCATE TABLE命令将失败。应该在WHERE子句中添加table_type = 'BASE TABLE'。 - reformed

Answer 3

按以下方式使用phpMyAdmin：

数据库视图 => 全选（所有表）=> 清空

如果您想忽略外键检查, 您可以取消勾选以下方框：

[ ] 启用外键检查

您需要运行版本4.5.0或更高版本才能获取此复选框。

这不是MySQL CLI技巧，但它有效！

- Nemo

---

谁说我们没有？这个回答显然对人们非常有用；它有帮助。 - bzeaman

如果您的数据库模式是父子关系，则此方法将无效。 - Cary Bondoc

外键约束失败。 - Brian Hannay

@BrianHannay编辑了答案，添加了有关禁用外键检查框的注释。 - Nemo

@Nemo 我使用了另一个答案，但为了完整起见，我想提一下，在PMA版本4.0.8中我找不到外键复选框。 - Brian Hannay

它是在2015年6月的PMA 4.5.0版本中添加的。我找到了[changelog]（https://github.com/phpmyadmin/history/blob/master/ChangeLogs/ChangeLog-2015#L165）和[original issue]（https://github.com/phpmyadmin/phpmyadmin/issues/6239）。 - Nemo

Answer 4

MS SQL Server 2005+ (执行时请删除 PRINT...)

EXEC sp_MSforeachtable 'PRINT ''TRUNCATE TABLE ?'''

如果你的数据库平台支持 INFORMATION_SCHEMA 视图，请执行以下查询的结果。

SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

尝试这个MySQL代码：

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES

在 Concat 中添加分号可以使其更容易从 mysql workbench 中使用。

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES

- beach

---

Ahh. INFORMATION_SCHEMA 是一个存在于当前数据库中的视图。如果你切换了数据库，该视图将执行新的数据库。切换数据库时无需修改查询语句，只需按照以下步骤操作：1）切换当前数据库。2）运行脚本。3）复制结果。4）将结果粘贴回编辑器中。5）执行结果。此时当前数据库中的所有表格均已被清空。 - beach

也许有些老旧，但仍然很有用。我今天仍在使用这些技术 :)。我很高兴你也发现它们很有用。 - beach

如果表格具有外键约束，则此解决方案将无法正常工作。 - neni

没有一个解决方案可以在不先禁用外键的情况下工作（除了刚刚添加的那个解决方案，它是在3年后才添加的）。禁用外键很容易 - 搜索您的DB引擎的特定SQL语法并使用上面的相同代码来1）禁用关键检查2）运行截断命令3）重新启用关键字。对于MySQL，该命令似乎是SET FOREIGN_KEY_CHECKS = 0;以禁用和SET FOREIGN_KEY_CHECKS = 1;以启用。对于SQL Server，请参见此处链接。 - beach

注意！这会选择所有数据库中的所有表（即使不在当前数据库中的表）。MySQL示例无效，但在我的情况下，它返回了293行（表），而不是66行。想象一下数据丢失... - f4der

Answer 5

我发现了以下代码可以删除数据库中的所有表：

mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | mysql -uUSERNAME -pPASSWORD DATABASENAME

如果您的托管方案有限，无法删除整个数据库，则该方法非常有用。

我修改了它以截断表格。 mysqldump没有"--add-truncate-table"，所以我做了这个:

mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | sed -e 's/DROP TABLE IF EXISTS/TRUNCATE TABLE/g' | mysql -uUSERNAME -pPASSWORD DATABASENAME

对我有用。 -- 编辑，修复最后一个命令中的一个错误

- mbjungle

Answer 6

SET FOREIGN_KEY_CHECKS = 0;

SELECT @str := CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, ';')
FROM   information_schema.tables
WHERE  table_type   = 'BASE TABLE'
  AND  table_schema IN ('db1_name','db2_name');

PREPARE stmt FROM @str;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

- George Moik

---

如果您想要使用当前数据库（从而使此代码可移植），请将SELECT语句中的最后一个条件更改为：AND table_schema = DATABASE();。 - alx

它对我来说没有截断。MySQL 5.7.25 - Lucas Bustamante

在MySQL 5.7.12（Aurora MySQL 2.7.2）上进行测试，这只会从“SELECT”查询中截断最后一个表。 “@str”似乎无法将所有表连接在一起。 - Mike Hill

我能够使用SELECT @str := GROUP_CONCAT(CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, '') SEPARATOR ';\n')将所有行连接起来。 - Jairo

Answer 7

我觉得最简单的做法就是像下面这样编写代码，只需将表格名称替换为您自己的即可。 重要提示 确保最后一行始终为 SET FOREIGN_KEY_CHECKS=1;

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `table1`;
TRUNCATE `table2`;
TRUNCATE `table3`;
TRUNCATE `table4`;
TRUNCATE `table5`;
TRUNCATE `table6`;
TRUNCATE `table7`;
SET FOREIGN_KEY_CHECKS=1;

- user3357946

---

在每个TRUNCATE命令之后重复SET FOREIGN_KEY_CHECKS=0是不必要的，只需在开头行将模式标记为0即可。 - HMagdy

如果您的表数量超过500个，就无法编辑和添加表名。 - Mithlaj

Answer 8

这将打印出截断所有表的命令：

SELECT GROUP_CONCAT(Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME) SEPARATOR ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('my_db');

- Gaurav Gupta

Answer 9

1. To truncate a table, one must drop the foreign key constraints mapped to the columns in this table from other tables (in fact on all tables in the specific DB/Schema).
2. So, all foreign key constraints must be dropped initially followed by table truncation.
3. Optionally, use the optimize table (in mysql, innodb engine esp) to reclaim the used data space/size to OS after data truncation.

4. Once data truncation is carried out, create the same foreign key constraints again on the same table. See below a script that would generate the script to carry out the above operations.

   SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';') FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='<TABLE SCHEMA>'
   UNION
   SELECT CONCAT('TRUNCATE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE'
   UNION
   SELECT CONCAT('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE'
   UNION
   SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ADD CONSTRAINT ',CONSTRAINT_NAME,' FOREIGN KEY(',COLUMN_NAME,')',' REFERENCES ',REFERENCED_TABLE_NAME,'(',REFERENCED_COLUMN_NAME,');') FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE CONSTRAINT_NAME LIKE 'FK%' AND TABLE_SCHEMA='<TABLE SCHEMA>'
   INTO OUTFILE "C:/DB Truncate.sql" LINES TERMINATED BY '\n';
   

现在，运行生成的 Db Truncate.sql 脚本。

好处： 1）回收磁盘空间 2）不需要删除并重新创建具有相同结构的 DB/Schema

缺点： 1）表中的 FK 约束应该在约束名称中包含 'FK' 的表中命名。

- Anand

Answer 10

以下是我提供的“一句话清空所有内容”的变体。

首先，我使用了一个名为“util”的单独数据库来存储我的帮助程序存储过程。清空所有表的存储过程代码如下：

DROP PROCEDURE IF EXISTS trunctables;
DELIMITER ;;
CREATE  PROCEDURE trunctables(theDb varchar(64))
BEGIN
    declare tname varchar(64);
    declare tcursor CURSOR FOR 
    SELECT table_name FROM information_schema.tables WHERE table_type <> 'VIEW' AND table_schema = theDb;
    SET FOREIGN_KEY_CHECKS = 0; 
    OPEN tcursor;
    l1: LOOP
        FETCH tcursor INTO tname;
        if tname = NULL then leave l1; end if;
        set @sql = CONCAT('truncate `', theDB, '`.`', tname, '`');
        PREPARE stmt from @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP l1;
    CLOSE tcursor;
    SET FOREIGN_KEY_CHECKS = 1; 
END ;;
DELIMITER ;

一旦您将该存储过程存储在实用数据库中，您可以像这样调用它。

call util.trunctables('nameofdatabase');

现在只有一个语句了，哈哈 :-)

- bebbo

---

这是唯一一个对我可靠地起作用的可编写脚本解决方案。 - Mike Hill