MySQL 8.0.3更新语句已传值仍触发非空约束异常排查

原因分析 #

1. 5.7与8.0对NOT NULL字段隐式赋值的行为差异
   MySQL 5.7关闭严格模式时，插入未指定值的NOT NULL无默认值字段，会自动填充字段类型的隐式默认值（比如INT类型填0），不会存储NULL。但8.0版本改变了这个逻辑：关闭严格模式时，这类插入操作只会触发警告，实际存储的是NULL值。这就导致表中存在callback_received为NULL的记录，而该字段本身定义为NOT NULL，后续更新操作可能触发约束校验报错。

2. 更新操作的隐性干扰
   虽然你的UPDATE语句明确设置callback_received=1，但可能存在以下问题：

   • 未察觉的UPDATE触发器：迁移后若存在未声明的BEFORE UPDATE触发器，可能在更新过程中把callback_received改成NULL；
   • 数据匹配失效：插入时你指定的order_code='ORDCODE001'被BEFORE INSERT触发器覆盖为函数生成的编码，导致UPDATE的WHERE条件没匹配到目标记录，若此时有其他批量更新逻辑，可能误触对其他NULL值记录的约束校验；
   • SQL_MODE隐性设置：即使关闭了严格模式，8.0默认启用的部分模式（如ERROR_FOR_DIVISION_BY_ZERO）可能间接影响约束校验，导致原本被允许的NULL值在更新时触发错误。

解决思路 #

1. 修正表结构，添加默认值 #

给callback_received字段添加合理默认值，从根源避免NULL值产生：

ALTER TABLE `order` 
MODIFY COLUMN `callback_received` INT NOT NULL DEFAULT 2 COMMENT '1 - Yes  2 - No';

这样插入时即使不指定该字段，数据库会自动填充默认值2，无需依赖宽松模式。

2. 清理现有NULL值记录 #

批量更新表中已存在的NULL值记录：

UPDATE `order` 
SET `callback_received`=2 
WHERE `callback_received` IS NULL;

确保所有记录的该字段值符合NOT NULL约束。

3. 检查触发器与SQL_MODE设置 #

• 排查是否存在未声明的UPDATE触发器，确认它们不会修改callback_received为NULL；
• 检查当前SQL_MODE配置：

  SELECT @@sql_mode;
  

  若包含STRICT_TRANS_TABLES或STRICT_ALL_TABLES，执行以下命令修改（可根据需求调整其他模式）：

  SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
  SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
  

  同时修改my.cnf（或my.ini）文件，确保重启后生效：

  sql_mode = NO_ENGINE_SUBSTITUTION
  

4. 调整插入逻辑 #

修改插入语句，显式指定callback_received的值，避免依赖数据库的宽松处理：

INSERT INTO `order`(order_code,member_id,order_date,callback_received) 
VALUES ('ORDCODE001',1000,NOW(),2);

触发器生成订单编码的逻辑可保留，但插入时的字段赋值需明确。

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