如何截断外键约束表？

Question

为什么 mygroup 上的 TRUNCATE 不起作用？ 即使我有 ON DELETE CASCADE SET 我得到：

错误 1701 (42000)：无法截断外键约束中引用的表 (mytest.instance、CONSTRAINT instance_ibfk_1 FOREIGN KEY ( GroupID) 参考 mytest.mygroup (ID))

drop database mytest;
create database mytest;
use mytest;

CREATE TABLE mygroup (
   ID    INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE instance (
   ID           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   GroupID      INT NOT NULL,
   DateTime     DATETIME DEFAULT NULL,

   FOREIGN KEY  (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,
   UNIQUE(GroupID)
) ENGINE=InnoDB;

Answer 1

是的，您可以：

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

使用这些语句，您可能会冒着将不遵守 FOREIGN KEY 约束的行放入表中的风险。

Answer 2

您无法TRUNCATE应用了FK约束的表（TRUNCATE与DELETE不同）。

要解决此问题，请使用以下任一解决方案。两者都存在破坏数据完整性的风险。

选项 1：

1. 删除约束
2. 执行 TRUNCATE
3. 手动删除现在无处引用的行
4. 创建约束

选项 2： 由user447951在他们的答案中建议

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

Answer 3

我会简单地这样做：

DELETE FROM mytest.instance;
ALTER TABLE mytest.instance AUTO_INCREMENT = 1;

Answer 4

如果您使用 phpMyAdmin，则很容易。

只需取消选中 SQL 选项卡下的启用外键检查 选项并运行 TRUNCATE

Answer 5

在 MYSQL 数据库上进行测试

解决方案 1：

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;

解决方案 2：

DELETE FROM table1;
ALTER TABLE table1 AUTO_INCREMENT = 1;
TRUNCATE table1;

这对我有用。我希望，这也会对您有所帮助。感谢您提出这个问题。

Answer 6

你可以做

DELETE FROM `mytable` WHERE `id` > 0

Answer 7

根据 MySQL 文档，TRUNCATE 不能用于具有外键关系的表。据我所知，没有完全的替代方案。

删除约束仍然不会调用 ON DELETE 和 ON UPDATE。
我ATM能想到的唯一解决方案是：

• 删除所有行，删除外键，截断，重新创建键
• 删除所有行，重置auto_increment（如果使用）

似乎MySQL中的TRUNCATE还不是一个完整的功能（它也不调用触发器）。
查看评论

Answer 8

虽然有人问这个问题，但我不知道，但现在如果您使用 phpMyAdmin，您可以简单地打开数据库并选择要截断的表。

• 底部有一个包含许多选项的下拉菜单。打开它并选择删除数据或表标题下的清空选项。
• 它会自动带您进入下一页，其中复选框中有一个名为启用外键检查的选项。只需取消选择它并按Yes按钮，所选的表就会被截断。

也许它在内部运行 user447951 的答案中建议的查询，但从 phpMyAdmin 界面使用非常方便。

Answer 9

答案确实是由zerkms提供的，如选项 1 中所述：

选项 1：不会损害数据完整性：

1. 删除限制
2. 执行截断
3. 手动删除现在无处引用的行
4. 创建约束

棘手的部分是删除约束，所以我想告诉你如何做，以防有人需要知道如何做到这一点：

1. 运行 SHOW CREATE TABLE

   查询来查看你的国外KEY的名字（下图中红框）：

   

2. 运行 ALTER TABLE <表名称>;删除外键<外键名称>。这将删除外键约束。

3. 删除关联的索引（通过表结构页面），即可完成。

重新创建外键：

ALTER TABLE <Table Name>
ADD FOREIGN KEY (<Field Name>) REFERENCES <Foreign Table Name>(<Field Name>);

Answer 10

另一个解决方法是删除表中的所有行，然后重置自动增量列：

delete from table_name where 1

然后运行：

ALTER TABLE table_name AUTO_INCREMENT = 1

Answer 11

如何截断外键约束表？
此图将演示如何解决使用外键约束截断表时的 mysql 错误。
如果您使用 PHPMYADMIN，则很容易截断具有外键约束的表。

1. 登录 PHPMYADMIN 并单击要截断的表。
2. 然后转到 SQL 选项卡将代码放置在 SQL 中截断表
   编辑器示例截断表学生；将学生替换为
   表的名称。
3. 在编辑器底部取消选中“启用外键检查”复选框，如下所示：

它会像魔术一样工作。

Answer 12

如果您使用 Laravel 迁移，则可以使用 Facades 来完成此操作。

更喜欢使用 Eloquent 对象，请回答“Eloquent”方式

 Schema::disableForeignKeyConstraints();
 Teacher::truncate();
 Schema::enableForeignKeyConstraints();

。在 Laravel 7 和 8 中，为了跨 4 个数据库（MySql、Postgres、SQLite 和 SqlServer）兼容，并且没有 Eloquent，你可以使用：

Schema::disableForeignKeyConstraints();
    DB::table('teachers')->truncate();
Schema::enableForeignKeyConstraints();

Answer 13

获取旧的外键检查状态和 sql 模式是截断/删除表的最佳方法，就像 Mysql Workbench 在将模型同步到数据库时所做的那样。

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;`
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

DROP TABLE TABLE_NAME;
TRUNCATE TABLE_NAME;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Answer 14

如果表的数据库引擎不同，您将收到此错误，因此将它们更改为 InnoDB

ALTER TABLE my_table ENGINE = InnoDB;

Answer 15

只需使用 CASCADE

TRUNCATE "products" RESTART IDENTITY CASCADE;

但要准备好级联删除）

Answer 16

在进行增删改查操作查询和截断表之前，我们此时使用安全查询。

对于像删除查询这样的粗略操作，您可以编写此命令。

SET SQL_SAFE_UPDATES = 0;

之后该查询可以将零重写为一以进行安全查询。

 SET SQL_SAFE_UPDATES = 1;

对于截断表，我们可以在截断查询之前编写以下命令。

SET FOREIGN_KEY_CHECKS = 0;

之后该查询可以将零重写为一以进行安全查询。

 SET FOREIGN_KEY_CHECKS = 1;