是否有一个查询(命令)以一次操作截断数据库中的所有表? 我想知道是否可以通过一个查询来做到这一点。
#1楼
我发现即使在NOCHECK CONSTRAINT ALL之后,TRUNCATE TABLE ..仍存在外键约束问题,因此我改用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'''
#2楼
要截断一个表,必须从其他表中删除映射到该表中列的外键约束(实际上是在特定DB / Schema中的所有表上)。
因此,必须首先删除所有外键约束,然后再删除表截断。
(可选)在数据截断后,使用优化表(在mysql中,innodb引擎esp中)将使用的数据空间/大小回收到OS。
一旦执行了数据截断,请在同一表上再次创建相同的外键约束。 参见下面的脚本,该脚本将生成执行上述操作的脚本。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='
现在,运行生成的Db Truncate.sql脚本
好处。 1)回收磁盘空间2)无需删除并重新创建具有相同结构的DB / Schema
缺点。 1)FK约束应为表中的名称,且约束名称中的名称应包含“ FK”。
#3楼
索恩1)
mysql> select group_concat('truncate',' ',table_name,';') from information_schema.tables where table_schema="db_name" into outfile '/tmp/a.txt';
mysql> /tmp/a.txt;
索恩2)
- Export only structure of a db
- drop the database
- import the .sql of structure
-编辑----
earlier in solution 1, i had mentioned concat() instead of group_concat() which would have not returned the desired result
#4楼
我知道这不完全是一个命令,但是可以按照以下步骤从phpMyAdmin中实现所需的结果:
选择要删除的(所有)表(全部选中)
从“已选择:”列表中选择“拖放” /“截断”
在确认页面(“您真的要:”上)复制查询(所有带有红色背景的内容)
转到顶部,单击SQL并输入:“ SET FOREIGN_KEY_CHECKS = 0;” 然后粘贴先前复制的查询
点击“开始”
这样做的想法是从数据库中快速获取所有表(您在5秒钟和2次单击中完成),但首先禁用外键检查。 没有CLI,也没有删除数据库并再次添加它。
#5楼
截断Mysql实例上的多个数据库表
SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES where table_schema in ('db1_name','db2_name');
使用查询结果截断表
注意:可能是您会收到此错误:
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
如果存在带有要删除/截断的表的外键引用的表,则会发生这种情况。
截断表格之前,您需要做的是:
SET FOREIGN_KEY_CHECKS=0;
截断表格并将其更改回
SET FOREIGN_KEY_CHECKS=1;