mysql - 无法截断 MySQL 表，同时能够删除所有记录

Question

我是关系的初学者，所以这听起来可能很愚蠢。但是，截断表和删除所有记录之间(在 MySQL 中)有什么区别(this 答案仅说明性能)？

我正在(在 phpMyAdmin 中)玩我的一个测试表，检查如何重置表的 auto_increment 值并遇到我能够删除所有记录的情况:

DELETE from managers;

但是当我试图截断这个表时(TRUNCATE managers)，我得到了警告:无法截断在外键约束中引用的表(探针，CONSTRAINT probes_ibfk_4 FOREIGN KEY ( manager_id) REFERENCES managers (id));.

我必须使用 ALTER TABLE managers AUTO_INCREMENT = 1;“重置”auto_increment 值。

这不是很奇怪吗？到目前为止，我认为，TRUNCATE = DELETE from managers(在检查和效果方面，因为性能可能不同，但这不是这里的关键)。

如何在 TRUNCATE 上弹出约束警告，而不是在“全部删除”上弹出？

Answer 1

truncate 在删除所有行后重置 auto_increment。所以它与使用 DELETE 命令删除所有行不同。

来自 mysql reference :

Although TRUNCATE TABLE is similar to DELETE, it is classified as aDDL statement rather than a DML statement. It differs from DELETE inthe following ways:

Truncate operations drop and re-create the table, which is much fasterthan deleting rows one by one, particularly for large tables.

Truncate operations cause an implicit commit, and so cannot be rolledback. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

Truncation operations cannot be performed if the session holds anactive table lock.

TRUNCATE TABLE fails for an InnoDB table or NDB table if there are anyFOREIGN KEY constraints from other tables that reference the table.Foreign key constraints between columns of the same table arepermitted.

Truncation operations do not return a meaningful value for the numberof deleted rows. The usual result is “0 rows affected,” which shouldbe interpreted as “no information.”

As long as the table format file tbl_name.frm is valid, the table canbe re-created as an empty table with TRUNCATE TABLE, even if the dataor index files have become corrupted.

Any AUTO_INCREMENT value is reset to its start value. This is trueeven for MyISAM and InnoDB, which normally do not reuse sequencevalues.

When used with partitioned tables, TRUNCATE TABLE preserves thepartitioning; that is, the data and index files are dropped andre-created, while the partition definitions (.par) file is unaffected.

The TRUNCATE TABLE statement does not invoke ON DELETE triggers.