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

TRUNCATE TABLE empties a table completely. Logically, this is

equivalent to a DELETE statement that deletes all rows, but there are

practical differences under some circumstances.

For an InnoDB table before version 5.0.3, InnoDB processes TRUNCATE

TABLE by deleting rows one by one. As of MySQL 5.0.3, row by row

deletion is used only if there are any FOREIGN KEY constraints that

reference the table. If there are no FOREIGN KEY constraints, InnoDB

performs fast truncation by dropping the original table and creating

an empty one with the same definition, which is much faster than

deleting rows one by one. (When fast truncation is used, it resets any

AUTO_INCREMENT counter to zero. From MySQL 5.0.13 on, the

AUTO_INCREMENT counter is reset to zero by TRUNCATE TABLE, regardless

of whether there is a foreign key constraint.)

In the case that FOREIGN KEY constraints reference the table, InnoDB

deletes rows one by one and processes the constraints on each one. If

the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the

child (referenced) table are deleted, and the truncated table becomes

empty. If the FOREIGN KEY constraint does not specify CASCADE, the

TRUNCATE TABLE statement deletes rows one by one and stops if it

encounters a parent row that is referenced by the child, returning

this error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign

key constraint fails (test.child, CONSTRAINT child_ibfk_1

FOREIGN KEY (parent_id) REFERENCES parent (id))