MySQL 8.x 中的主要错误?-- 外键 [英] Major bug in MySQL 8.x? -- foreign keys

问题描述

在从 MySQL(用于代码生成器)检索外键信息时，我注意到了这种奇怪的行为.它看起来像是 MySQL 8.x 中的一个主要错误.当我使用 REFERENCES 创建外键时，引擎不会强制执行它.例如:

While retrieving foreign keys information from MySQL (for a code generator) I noticed this strange behavior. It looks like a major bug in MySQL 8.x. When I create a foreign key using REFERENCES the engine does not enforce it. For example:

create table p (
  id int primary key not null
) engine=innodb;

create table q (
  pid int references p (id)
) engine=innodb;

insert into q (pid) values (123); -- succeeds (!)

请参阅 DB Fiddle 中的示例.

但是，如果我创建外键键入 FOREIGN KEY (col) REFERENCES table (col) 它可以正常工作:

However, if I create the foreign key typing FOREIGN KEY (col) REFERENCES table (col) it works properly:

create table p (
  id int primary key not null
) engine=innodb;

create table r (
  pid int,
  foreign key (pid) references p (id)
) engine=innodb;

insert into r (pid) values (456); -- fails, as expected

请参阅 DB Fiddle 上的运行示例.

See running example at DB Fiddle.

如果这是 MySQL 中真正的主要错误，是否有任何方法可以禁用错误的语法?

If this is a bona fide major bug in MySQL, is there any way of disabling the bad syntax?

注意:我刚刚验证了 MariaDB 在 10.4 之前存在相同的错误，但它似乎已在 10.5 中修复.

Note: I just verified that MariaDB presented the same bug until 10.4, but it seems it's fixed in 10.5.

推荐答案

在 MySQL 中未修复.它允许合法的 SQL 语法，但 MySQL 在使用列级外键语法时不保存约束.

Not fixed in MySQL. It permits legal SQL syntax, but MySQL does not save the constraint when using the column-level foreign key syntax.

这在 2005 年被报告为错误，并以无法修复"关闭.消息.

This was reported as a bug in 2005, and closed with a "won't fix" message.

https://bugs.mysql.com/bug.php?id=13301

https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html 说:

MySQL 解析但忽略内联引用规范"(如 SQL 标准中所定义)，其中引用被定义为列规范的一部分.MySQL 仅在指定为单独的 FOREIGN KEY 规范的一部分时才接受 REFERENCES 子句.对于不支持外键的存储引擎(如 MyISAM)，MySQL Server 会解析并忽略外键规范.

MySQL parses but ignores "inline REFERENCES specifications" (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

无法禁用不受支持的语法，甚至无法使其返回错误或警告.

There is no way to disable the unsupported syntax, or even to make it return an error or a warning.

这篇关于MySQL 8.x 中的主要错误?-- 外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！