这对我很管用。修改数据库、用户名和密码。

mysql -Nse 'show tables' -D DATABASE -uUSER -pPWD | while read table; do echo "SET FOREIGN_KEY_CHECKS = 0;drop table \`$table\`;SET FOREIGN_KEY_CHECKS = 1;"; done | mysql DATABASE -uUSER -pPWD

我发现这可以删除数据库中的所有表:

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

如果您受到托管解决方案的限制(不能删除整个数据库)，则非常有用。

我修改了它来截断表格。mysqldump没有“——add- trunct -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

对我有用 ——edit，修复最后一个命令的错别字

我发现最简单的方法是像下面这样做，只需用自己的表名替换表名。重要的是确保最后一行永远是 设置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;

mysqldump -u root -p --no-data dbname > schema.sql
mysqldump -u root -p drop dbname
mysqldump -u root -p < schema.sql

MS SQL Server 2005+(删除打印以实际执行…)

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工作台中使用。

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

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 Truncate。生成SQL脚本

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

缺点。 1) FK约束应该是表中的名称，约束名称中包含“FK”。