MySQL 如何截断外键约束表？

How to truncate a foreign key constrained table?

提问by user391986

Why doesn't a TRUNCATEon mygroupwork? Even though I have ON DELETE CASCADE SETI get:

为什么没有一个TRUNCATE上mygroup工作？即使我有ON DELETE CASCADE SET我得到：

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1FOREIGN KEY (GroupID) REFERENCES mytest.mygroup(ID))

错误 1701 (42000)：无法截断外键约束 ( mytest. instance, CONSTRAINT instance_ibfk_1FOREIGN KEY ( GroupID) REFERENCES 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;

回答by user447951

Yes you can:

是的你可以：

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

With these statements, you risk letting in rows into your tables that do not adhere to the FOREIGN KEYconstraints.

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

回答by zerkms

You cannot TRUNCATEa table that has FK constraints applied on it (TRUNCATEis not the same as DELETE).

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

To work around this, use either of these solutions. Both present risks of damaging the data integrity.

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

Option 1:

选项1：

1. Remove constraints
2. Perform TRUNCATE
3. Delete manually the rows that now have references to nowhere
4. Create constraints

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

Option 2:suggested by user447951in their answer

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

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

回答by George Garchagudashvili

I would simply do it with :

我会简单地做：

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

回答by Ali Sadran

you can do

你可以做

DELETE FROM `mytable` WHERE `id` > 0

回答by Omer Sabic

As per mysql documentation, TRUNCATE cannot be used on tables with foreign key relationships. There is no complete alternative AFAIK.

根据mysql 文档，TRUNCATE 不能用于具有外键关系的表。没有完整的替代 AFAIK。

Dropping the contraint still does not invoke the ON DELETE and ON UPDATE. The only solution I can ATM think of is to either:

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

• delete all rows, drop the foreign keys, truncate, recreate keys
• delete all rows, reset auto_increment (if used)

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

It would seem TRUNCATE in MySQL is not a complete feature yet (it also does not invoke triggers).See comment

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

回答by Rolen Koh

While this question was asked I didn't know about it, but now if you use phpMyAdmin you can simply open the database and select the table(s) you want to truncate.

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

• At the bottom there is a drop down with many options. Open it and select Emptyoption under the heading Delete data or table.
• It takes you to the next page automatically where there is an option in checkbox called Enable foreign key checks. Just unselect it and press the Yesbutton and the selected table(s) will be truncated.

• 在底部有一个下拉菜单，有很多选项。打开它并选择Empty标题下的选项Delete data or table。
• 它会自动将您带到下一页，其中复选框中有一个名为 的选项Enable foreign key checks。只需取消选择它并按下Yes按钮，选定的表将被截断。

Maybe it internally runs the query suggested in user447951's answer, but it is very convenient to use from phpMyAdmin interface.

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

回答by Ajmal Salim

Easy if you are using phpMyAdmin.

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

Just uncheck Enable foreign key checksoption under SQLtab and run TRUNCATE <TABLE_NAME>

只需取消选中Enable foreign key checks选项SQL卡下的选项并运行TRUNCATE <TABLE_NAME>

回答by Kamlesh

Tested on MYSQL Database

在 MYSQL 数据库上测试

Solution 1:

解决方案1：

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;

Solution 2:

解决方案2：

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

This works for me. I hope, this will help you also. Thanks for asking this question.

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

回答by Pmpr

Answer is indeed the one provided by zerkms, as stated on Option 1:

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

Option 1: which does not risk damage to data integrity:

1. Remove constraints
2. Perform TRUNCATE
3. Delete manually the rows that now have references to nowhere
4. Create constraints

选项 1：不会对数据完整性造成损害的风险：

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

The tricky part is Removing constraints, so I want to tell you how, in case someone needs to know how to do that:

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

1. Run SHOW CREATE TABLE <Table Name>query to see what is your FOREIGN KEY's name (Red frame in below image):

   

2. Run ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign Key Name>. This will remove the foreign key constraint.

3. Drop the associated Index(through table structure page), and you are done.

1. 运行SHOW CREATE TABLE <Table Name>查询以查看您的FOREIGN KEY的名称（下图中的红框）：

   

2. 运行ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign Key Name>。这将删除外键约束。

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

to re-create foreign keys:

重新创建外键：

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

回答by Alexus1024

Just use CASCADE

只需使用级联

TRUNCATE "products" RESTART IDENTITY CASCADE;

But be ready for cascade deletes )

但准备好级联删除）