下面的MySQL查询本身将生成一个查询，该查询将截断给定数据库中的所有表。它绕过外键:

SELECT CONCAT(
         'SET FOREIGN_KEY_CHECKS=0; ',
         GROUP_CONCAT(dropTableSql SEPARATOR '; '), '; ',
         'SET FOREIGN_KEY_CHECKS=1;'
       ) as dropAllTablesSql
FROM   ( SELECT  Concat('TRUNCATE TABLE ', table_schema, '.', TABLE_NAME) AS dropTableSql
         FROM    INFORMATION_SCHEMA.TABLES
         WHERE   table_schema = 'DATABASE_NAME' ) as queries

删除(即删除表格)

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

如果使用SQL server 2005，有一个隐藏的存储过程，允许您对数据库中的所有表执行一个或一组命令。下面是如何用这个存储过程调用TRUNCATE TABLE:

EXEC [sp_MSforeachtable] @command1="TRUNCATE TABLE ?"

这里有一篇很好的文章进一步阐述。

然而，对于MySql，你可以使用mysqldump并指定——add-drop-tables和——no-data选项来删除和创建所有忽略数据的表。是这样的:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE]

Mysqldump使用指南

我们可以编写如下所示的bash脚本

truncate_tables_in_mysql() {
    type mysql >/dev/null 2>&1 && echo "MySQL present." || sudo apt-get install -y mysql-client
    
    tables=$(mysql -h 127.0.0.1 -P $MYSQL_PORT -u $MYSQL_USER  -p$MYSQL_PASSWORD -e "USE $BACKEND_DATABASE;    
SHOW TABLES;")
    tables_list=($tables)
    
    query_string="USE $BACKEND_DATABASE; SET FOREIGN_KEY_CHECKS = 0;"
    for table in "${tables_list[@]:1}"
    do
        query_string="$query_string TRUNCATE TABLE \`$table\`; "
    done
    query_string="$query_string SET FOREIGN_KEY_CHECKS = 1;"
    
    mysql -h 127.0.0.1 -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "$query_string"
}

你可以用你的MySQL细节替换env变量。使用一个命令就可以截断一个DB中的所有表。

我发现TRUNCATE表..外键约束有问题，即使在NOCHECK约束ALL之后，所以我使用DELETE FROM语句代替。这意味着身份种子不会被重置，你总是可以添加一个DBCC CHECKIDENT来实现这一点。

我使用下面的代码打印到消息窗口的sql截断数据库中的所有表，然后运行它。这只会让犯错变得更难。

EXEC sp_MSforeachtable 'PRINT ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'''
EXEC sp_MSforeachtable 'print ''DELETE FROM ?'''
EXEC sp_MSforeachtable 'print ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'''

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”。