删除级联上的 MySQL。测试示例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12185811/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL on delete cascade. Test Example
提问by Eae
I am wondering about this test question. I prepared the example myself and tested it but I still feel unsure of the answer.
我想知道这个测试问题。我自己准备了这个例子并进行了测试，但我仍然不确定答案。
With the following:
具有以下内容：
CREATE TABLE foo (
id INT PRIMARY KEY AUTO_INCREMENT,
name INT
)
CREATE TABLE foo2 (
id INT PRIMARY KEY AUTO_INCREMENT,
foo_id INT REFERENCES foo(id) ON DELETE CASCADE
)
As far as I can see the answer is:
据我所知，答案是：
a. Two tables are created
一种。创建了两个表
Although there are also:
虽然也有：
b. If a row in table foo2, with a foo_id of 2 is deleted, then the row with id=2 in the table foo is automatically deleted
湾 如果删除表 foo2 中 foo_id 为 2 的行，则自动删除表 foo 中 id=2 的行
d.If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are deleted
d.如果删除表foo中id=2的行，则删除表foo2中所有foo_id=2的行
In my example I would have used the delete syntax:
在我的示例中，我会使用删除语法：
DELETE FROM foo WHERE id = 2;
DELETE FROM foo2 WHERE foo_id = 2;
For some reason I was unable to find any relationship between the tables although it seems like there should be one. Maybe there is some MySQL setting or perhaps is ON DELETE CASCADEnot used properly in the table creation queries? I am left wondering...
出于某种原因，我无法找到表之间的任何关系，尽管似乎应该有一个关系。也许有一些 MySQL 设置或者ON DELETE CASCADE在表创建查询中没有正确使用？我不知道...
回答by spencer7593
Answer d. is correct, if and only if the storage engine actually supports and enforces foreign key constraints.
答案 D. 是正确的，当且仅当存储引擎实际支持并强制执行外键约束。
If the tables are created with Engine=MyISAM, then neither b. or d. is correct.
如果表是用 来创建的Engine=MyISAM，那么 b. 或 d。是正确的。
If the tables are created with Engine=InnoDB, then d.is correct.
如果表是用 来创建的Engine=InnoDB，那么d。是正确的。
NOTE:
笔记：
This is true for InnoDB if and only if FOREIGN_KEY_CHECKS = 1; if FOREIGN_KEY_CHECKS = 0, then a DELETEfrom the parent table (foo) will notremove rows from the child table (foo2) that reference a row removed from the parent table.
当且仅当 InnoDB 是这样FOREIGN_KEY_CHECKS = 1；if FOREIGN_KEY_CHECKS = 0，则DELETE父表 (foo) 中的 a不会从子表 (foo2) 中删除引用从父表中删除的行的行。
Verify this with the output from SHOW VARIABLES LIKE 'foreign_key_checks'(1=ON, 0=OFF)
(The normal default is for this to be ON.)
使用SHOW VARIABLES LIKE 'foreign_key_checks'(1=ON, 0=OFF)的输出验证这一点（正常默认设置为 ON。）
The output from SHOW CREATE TABLE foowill show which engine the table uses.
的输出SHOW CREATE TABLE foo将显示该表使用的引擎。
The output from SHOW VARIABLES LIKE 'storage_engine'will show the default engine used when a table is created and the engine is not specified.
的输出SHOW VARIABLES LIKE 'storage_engine'将显示在创建表且未指定引擎时使用的默认引擎。
回答by Vic
You do have a relationship between two tables, it's in the foo2 creation command:
... foo_id int references foo(id) on delete cascade.
你有两个表之间的关系，它在创建foo2的命令：
... foo_id int references foo(id) on delete cascade。
According to the MySQL Foreign Key Constraints reference:
根据MySQL 外键约束参考：
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.
CASCADE：从父表中删除或更新行，并自动删除或更新子表中匹配的行。支持 ON DELETE CASCADE 和 ON UPDATE CASCADE。
Also, according to the MySQL Foreign Keys reference:
另外，根据MySQL 外键参考：
For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table.
对于 InnoDB 以外的存储引擎，在定义列时可以使用 REFERENCES tbl_name(col_name) 子句，该子句没有实际效果，仅作为备忘录或注释向您说明您当前定义的列的用途引用另一个表中的列。
So since the foreign key is from the child table to the parent table, it makes fooa parent table and foo2a child table, so deleting a row from foowill cascade deletions to foo2, providing you use InnoDB or some other storage engine that supports it.
因此，由于外键是从子表到父表，它构成foo了一个父表和foo2一个子表，因此删除行将从foo级联删除到foo2，前提是您使用 InnoDB 或其他一些支持它的存储引擎。
