MySQL定义为NOT NULL的字段为何UPDATE时可被赋值为NULL？

这种情况不属于标准的预期行为，NOT NULL约束对INSERT和UPDATE操作均生效，你遇到的现象是MySQL非严格SQL模式的特殊处理逻辑导致的，具体原因如下： #

核心原因：非严格模式下的隐式默认值转换 #

• 当MySQL会话未启用严格SQL模式时，给NOT NULL字段赋值NULL不会抛出错误，MySQL会自动将值替换为对应字段类型的隐式默认值：
  • 字符串类型字段会被替换为空字符串''，这就是你观测到「字段值被清空」的本质，实际存储的不是NULL
  • 数值类型字段会被替换为0，日期时间类型字段会被替换为0000-00-00 00:00:00类零值
• 你可以执行以下SQL验证实际存储的值并非NULL，执行后不会返回被更新的记录：

SELECT * FROM `wp_mgf_visits` WHERE `photo` IS NULL;

操作表现不一致的原因 #

你观测到INSERT赋值NULL会报错、UPDATE赋值NULL却执行成功，是因为不同客户端的会话SQL模式可能存在差异：你执行INSERT操作的环境默认启用了严格模式，而phpMyAdmin的会话使用了非严格模式，才会出现两种操作表现不一致的情况。
你可以在phpMyAdmin中执行以下语句查看当前会话的SQL模式：

SELECT @@SESSION.sql_mode;

如果返回结果中没有STRICT_TRANS_TABLES或STRICT_ALL_TABLES参数，就说明当前处于非严格模式。 #

修复方案：启用严格SQL模式 #

要让NOT NULL约束完全按照预期生效，开启严格模式即可：

• 临时会话生效（仅对当前连接有效）：

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

执行后再运行你问题中的UPDATE语句，就会直接抛出Column 'photo' cannot be null的错误。

• 永久生效：修改MySQL配置文件my.cnf（Windows环境为my.ini），在[mysqld]段下添加如下配置后重启MySQL服务：

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

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