用一个命令截断 MySQL 数据库中的所有表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1912813/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Truncate all tables in a MySQL database in one command?
提问by devang
Is there a query (command) to truncate all the tables in a database in one operation? I want to know if I can do this with one single query.
是否有查询（命令）在一次操作中截断数据库中的所有表？我想知道我是否可以通过一个查询来做到这一点。
回答by battousaix
Drop (i.e. remove tables)
删除（即删除表）
mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done
Truncate (i.e. empty tables)
截断（即空表）
mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done
回答by lalit choudhary
truncate multiple database tables on Mysql instance
截断Mysql实例上的多个数据库表
SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES where table_schema in ('db1_name','db2_name');
Use Query Result to truncate tables
使用查询结果截断表
Note: may be you will get this error:
注意：您可能会收到此错误：
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
That happens if there are tables with foreign keys references to the table you are trying to drop/truncate.
如果有外键引用到您尝试删除/截断的表的表，就会发生这种情况。
Before truncating tables All you need to do is:
在截断表之前您需要做的就是：
SET FOREIGN_KEY_CHECKS=0;
Truncate your tables and change it back to
截断您的表格并将其改回
SET FOREIGN_KEY_CHECKS=1;
回答by Nemo
Use phpMyAdmin in this way:
以这种方式使用 phpMyAdmin：
Database View => Check All (tables) => Empty
数据库视图 => 检查所有（表）=> 空
If you want to ignore foreign key checks, you can uncheck the box that says:
如果您想忽略外键检查，您可以取消选中以下框：
[ ] Enable foreign key checks
[ ] 启用外键检查
You'll need to be running atleast version 4.5.0 or higher to get this checkbox.
您至少需要运行 4.5.0 或更高版本才能获得此复选框。
Its not MySQL CLI-fu, but hey, it works!
它不是 MySQL CLI-fu，但嘿，它有效！
回答by beach
MS SQL Server 2005+ (Remove PRINT for actual execution...)
MS SQL Server 2005+（删除 PRINT 以便实际执行...）
EXEC sp_MSforeachtable 'PRINT ''TRUNCATE TABLE ?'''
If your database platform supports INFORMATION_SCHEMA views, take the results of the following query and execute them.
如果您的数据库平台支持 INFORMATION_SCHEMA 视图，请获取以下查询的结果并执行它们。
SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
Try this for MySQL:
为 MySQL 试试这个：
SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
Adding a semicolon to the Concat makes it easier to use e.g. from within mysql workbench.
向 Concat 添加分号使其更易于使用，例如在 mysql 工作台中。
SELECT Concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES
回答by mbjungle
I found this to drop all tables in a database:
我发现这可以删除数据库中的所有表：
mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | mysql -uUSERNAME -pPASSWORD DATABASENAME
Usefull if you are limited by hosting solution (not able to drop a whole database).
如果您受到托管解决方案的限制（无法删除整个数据库），则很有用。
I modified it to truncate the tables. There is no "--add-truncate-table" for mysqldump, so i did:
我修改它以截断表格。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
works for me --edit, fixing a typo in the last command
对我有用 --edit，修复最后一个命令中的错字
回答by George Moik
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;
回答by user3357946
I found it most simple to just do something like the code below, just replace the table names with your own. importantmake sure the last line is always SET FOREIGN_KEY_CHECKS=1;
我发现只做类似下面的代码的事情最简单，只需用你自己的表名替换。 重要的是确保最后一行始终是 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;
回答by Gaurav Gupta
This will print the command to truncate all tables:
这将打印命令以截断所有表：
SELECT GROUP_CONCAT(Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME) SEPARATOR ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('my_db');
回答by Anand
- 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).
- So, all foreign key constraints must be dropped initially followed by table truncation.
- Optionally, use the optimize table (in mysql, innodb engine esp) to reclaim the used data space/size to OS after data truncation.
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/Schema 中的所有表上）。
- 因此，首先必须删除所有外键约束，然后进行表截断。
- 或者，使用优化表（在 mysql 中，innodb 引擎 esp）在数据截断后将使用的数据空间/大小回收到操作系统。
执行数据截断后，在同一张表上再次创建相同的外键约束。请参阅下面的脚本，该脚本将生成执行上述操作的脚本。
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';
Now, run the Db Truncate.sql script generated
现在，运行生成的 Db Truncate.sql 脚本
Benefits. 1) Reclaim disk space 2) Not needed to drop and recreate the DB/Schema with the same structure
好处。1) 回收磁盘空间 2) 不需要删除和重新创建具有相同结构的 DB/Schema
Drawbacks. 1) FK constraints should be names in the table with the name containing 'FK' in the constraint name.
缺点。1) FK 约束应该是表中的名称，其名称在约束名称中包含“FK”。
回答by Vinod Kumar
Use this and form the query
使用它并形成查询
SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES where table_schema in (db1,db2)
INTO OUTFILE '/path/to/file.sql';
Now use this to use this query
现在使用它来使用这个查询
mysql -u username -p </path/to/file.sql
if you get an error like this
如果你得到这样的错误
ERROR 1701 (42000) at line 3: Cannot truncate a table referenced in a foreign key constraint
the easiest way to go through is at the top of your file add this line
最简单的方法是在文件顶部添加这一行
SET FOREIGN_KEY_CHECKS=0;
which says that we don't want to check the foreign key constraints while going through this file.
这表示我们不想在浏览此文件时检查外键约束。
It will truncate all tables in databases db1 and bd2.
它将截断数据库 db1 和 bd2 中的所有表。
