之后执行SET FOREIGN_KEY_CHECKS=1,启动外键约束 在重新truncate table xxx;清空就可以了
mysql清空有外键关联的表 第一种:(不要外键约束) 手动删除外键约束; 删除表数据 第二种:(保留外键约束) SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE 表名; SET FOREIGN_KEY_CHECKS = 1; --- TRUNCATE 清空数据 (还原主键,自增的ID会重新从1开始) DELETE 删除数据 (删除数据,自增的ID会继续递增) ---...
在Mysql中取消外键约束: SET FOREIGN_KEY_CHECKS=0 执行 truncatetablename 然后再设置外键约束: SET FOREIGN_KEY_CHECKS=1
truncate table app04news_userinfo; 报错如下: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`platform`.`app04news_news`, CONSTRAINT `app04news_news_user_id_d2929af9_fk_app04news_userinfo_id`) 问题原因 因为数据库存在主外键关系,不能进行删除或者截断。此时...
[Err] 1701 -Cannot truncate a table referenced in a foreign key constraint … 解决办法 删除之前先执行删除外键约束 代码语言:javascript 代码运行次数:0 SETforeign_key_checks=0; 删除完之后再执行启动外键约束 代码语言:javascript 代码运行次数:0
6、对用TRUNCATE TABLE删除数据的表上增加数据时,要使用UPDATE STATISTICS来维护索引信息。 7、如果有ROLLBACK语句,DELETE操作将被撤销,但TRUNCATE不会撤销。 三、不能对以下表使用 TRUNCATE TABLE 1、由 FOREIGN KEY 约束引用的表。(您可以截断具有引用自身的外键的表。) ...
mysql> ALTER TABLE testalter_tbl CHANGE teacher student BIGINT; 1. 2. 3. 4. 5. 6. 7. 8. 9. 删除表的语法 删除指定表 drop table TML; //该表结构被删除,对象被删除,所有数据被删除,所有相关索引,约束被删除 truncate table TML //truncate的作用是截断某表,具体作用是删除表的数据而不删除表的...
If the table does not have any foreign key constraint, the TRUNCATE TABLE statement drops the table and recreates a new empty one with the same definition, which is faster and more efficient than using the DELETE statement especially for big tables. If you are using other storage engines, th...
To achieve high performance,TRUNCATE TABLEbypasses the DML method of deleting data. Thus, it does not causeON DELETEtriggers to fire, it cannot be performed forInnoDBtables with parent-child foreign key relationships, and it cannot be rolled back like a DML operation. However,TRUNCATE TABLEoperat...
Re: truncate table? Posted by:John Nahlen Date: February 23, 2012 11:57AM I'm not all that familiar with InnoDB, and someone correct me if I'm wrong, but it looks like dropping and recreating the table are just "implementation details" for the InnoDB storage engine, so that should ...