我对MySQL 5.6 InnoDB有一个问题,在运行INSERT INTO xxx (col) SELECT ...
时忽略了非零外键。以其他格式运行插入语句时,可以正确执行约束。启用了外键检查,sql_mode = STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION
这是一个示例:
CREATE TABLE Test_Parent
(
id BIGINT(18) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
dummy VARCHAR(255)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci
COMMENT 'Test parent table';
CREATE TABLE Test_Child
(
id BIGINT(18) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
fid BIGINT UNSIGNED NOT NULL,
FOREIGN KEY Fk_Test_Parent_01(fid) REFERENCES Test_Parent(id)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci
COMMENT 'Test child table';
INSERT INTO Test_Parent(dummy)
VALUES ('test');
## Here's where the FK constraint should be enforced but isn't ##
INSERT INTO Test_Child(fid)
SELECT id
FROM Test_Parent
WHERE dummy = 'missing value';
1 row affected in 5ms
## Running an insert with a different format, the constraint is enforced ##
INSERT INTO Test_Child(fid)
VALUES (null);
Column 'fid' cannot be null
## Running this format, the foreign key is also enforced ##
INSERT INTO Test_Child(id, fid)
VALUES (123, (SELECT id FROM Test_Parent WHERE dummy = 'missing value'));
Column 'fid' cannot be null
我不明白为什么MySQL会在3个插入语句中强制执行外键。有什么想法吗?
来自客户的误导性1 row affected in 5ms
消息可能是这里的混乱来源。在评论线程中,您提到的Intellij报告了该消息,但是我在MySQL 5.6.35和5.7.16-ubuntu和两个版本中均在您定义明确的测试表中运行了您的测试表:
mysql > INSERT INTO Test_Child(fid)
-> SELECT id
-> FROM Test_Parent
-> WHERE dummy = 'missing value';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
因此,查看超过误导性影响的行消息,这里真正发生的是INSERT...SELECT
语句的SELECT
部分与没有行匹配,因此MySQL不会尝试插入任何行。因此,没有外国密钥约束。
INSERT INTO...SELECT
的格式与以后的示例有所不同:
INSERT INTO Test_Child(id, fid)
VALUES (123, (SELECT id FROM Test_Parent WHERE dummy = 'missing value'));
...因为在这种情况下,数字文字123
强制插入一行,并且与从子计算机返回的null
值配对。因此,null
试图插入并造成约束违规。
如果您强迫INSERT...SELECT
返回带有空值的行,则由于违反约束,您可能会导致失败:
INSERT INTO Test_Child (fid)
-- Return a literal NULL row...
SELECT NULL as id FROM Test_Parent
-- Column fid cannot be null