mysql 强制插入_MySQL插入选择不强制执行NOT NULL约束

运行INSERT INTO xxx(col)SELECT时,我遇到

MySQL 5.6 InnoDb忽略NOT NULL外键的问题….当以其他格式运行insert语句时,约束被正确执行.启用了外键检查,并且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个插入语句中的2个强制执行外键.有任何想法吗？