MySQL 5.7使用SELECT插入时忽略NOT NULL约束仅警告问题咨询

使用MySQL 14.14 Distrib 5.7.40（Linux x86_64平台），创建包含3个带NOT NULL约束列的表时，出现两种不一致的插入行为：

• 直接用INSERT ... VALUES插入NULL到NOT NULL列，触发错误提示列col_2不能为NULL；
• 用INSERT ... SELECT插入NULL到NOT NULL列，仅弹出警告，且记录被插入表中，NULL被替换为空字符串。

具体操作代码及执行结果：

DROP TABLE IF EXISTS table1;

DROP TABLE IF EXISTS table2;

CREATE  TABLE IF NOT EXISTS table1 (
     id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
     col_1 VARCHAR(45) NOT NULL ,
     col_2 VARCHAR(45) NOT NULL ,
     PRIMARY KEY (id));
     
CREATE TABLE IF NOT EXISTS table2 LIKE table1;

-- 按预期插入成功
INSERT INTO table1(id, col_1, col_2) VALUES (1,'aaa', 'bbb');
INSERT INTO table2(id, col_1, col_2) VALUES (2,'ccc', 'ddd');

-- 按预期插入失败
INSERT INTO table1(col_1, col_2) VALUES ('xxx', NULL); -- ERROR 1048 (23000): Column 'col_2' cannot be null

-- 意外插入成功，仅弹出警告
INSERT INTO table1(id, col_1, col_2) SELECT id, col_1, NULL FROM table2 where id=2; -- Query OK, 1 row affected, 1 warning (0.07 sec)

执行后查看警告及表数据：

mysql> show warnings;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'col_2' cannot be null |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

mysql> select * from table1;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  1 | aaa   | bbb   |
|  2 | ccc   |       |
+----+-------+-------+
2 rows in set (0.01 sec)

这是MySQL**严格模式（sql_mode）**的行为差异导致的：

• MySQL 5.7中，默认sql_mode可能未完全开启严格校验规则，对不同插入语句的约束校验逻辑不同；
• 当使用INSERT ... VALUES直接插入NULL时，若开启了STRICT_TRANS_TABLES（事务表严格模式），会直接触发错误终止操作；
• 当使用INSERT ... SELECT时，若未开启STRICT_ALL_TABLES（所有表严格模式），MySQL会执行隐式转换：将NULL替换为对应列的默认替代值（字符串类型转空字符串、数值类型转0），仅抛出警告而不中断插入流程。

要统一两种插入方式的行为，确保NOT NULL约束被严格执行，需开启完整的严格模式：

1. 临时生效（重启MySQL后失效） #

执行以下SQL命令，分别设置全局和会话级别的sql_mode：

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

2. 永久生效（修改配置文件） #

编辑MySQL配置文件（通常路径为/etc/my.cnf或/etc/mysql/my.cnf），在[mysqld]段落中添加：

sql_mode = STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

保存配置后重启MySQL服务：

systemctl restart mysqld

开启严格模式后，无论使用哪种插入方式，只要向NOT NULL列插入NULL，都会直接触发ERROR 1048 (23000)错误，终止操作，保证数据的完整性。

内容的提问来源于stack exchange，提问作者Kannan Ramaswamy