mysql - 如何删除MySQL InnoDB中的所有相关记录？

Question

好吧，我的 friend 告诉我应该开始使用 InnoDB 作为我的数据库，所以我第一次尝试了。我在删除记录/索引/字段/表时遇到问题。每当我尝试删除一条记录时，我都必须转到所有相关记录，删除它们，然后返回删除我的原始记录。而且我根本无法删除索引!有没有更简单的方法来做到这一点(因为当我要在 PHP 中运行时，它不会删除任何东西)或者我应该跳回到 MyISAM？您认为以下哪个更好？

Answer 1

如果要删除主表记录时自动删除依赖记录，需要指定ON DELETE CASCADE在你的外键上。

根据手册，您选择的外键约束是以下之一:

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

Note Currently, cascaded foreign key actions do not activate triggers.

SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. InnoDB rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

如果您未指定任何内容，则默认设置为 NO ACTION -- 这就是您的删除操作现在没有执行任何操作的原因。