MySQL5.7升级至8.0后单表出现非预期非空约束报错求助

问题核心细节 #

• 升级背景：从MySQL5.7迁移至8.0，CI、本地、预发布环境测试全量通过，仅生产环境某单表出现异常
• 触发语句：UPDATE my_table SET my_col = '1' WHERE table_id='abc123' LIMIT 1;
• 报错信息：SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'my_col' cannot be null
• 特殊表现：报错非必现，连续两次请求第二次通常正常；仅PHP PDO执行触发，mysql-client CLI无任何问题

排查与解决步骤 #

1. 校验PDO预处理与参数绑定逻辑 #

MySQL 8.0对数据类型校验更严格，PDO的模拟预处理可能导致参数解析异常：

• 检查PDO连接是否开启模拟预处理：

  var_dump($pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES));
  

  若返回true，尝试关闭模拟预处理：

  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  

• 显式指定参数绑定类型，避免隐式转换：

  $stmt = $pdo->prepare("UPDATE my_table SET my_col = :val WHERE table_id=:id LIMIT 1");
  $stmt->bindParam(':val', $val, PDO::PARAM_STR);
  $stmt->bindParam(':id', $id, PDO::PARAM_STR);
  $stmt->execute();
  

2. 对齐测试与生产环境的SQL_MODE #

生产环境可能启用了更严格的SQL_MODE规则，导致隐式报错：

• 对比两边的SQL_MODE配置：

  SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
  

• 临时将生产环境SESSION的SQL_MODE调整为与测试环境一致，验证是否解决问题：

  SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
  

  （注：调整前需评估业务影响，避免破坏其他逻辑）

3. 检查表结构与触发器差异 #

• 对比测试与生产环境的表定义，执行SHOW CREATE TABLE my_table;，重点确认：
  • my_col的字符集、排序规则、长度是否完全一致
  • 是否存在未同步的触发器：SHOW TRIGGERS LIKE 'my_table';，验证触发器逻辑是否会意外将my_col设为NULL

4. 排查事务与自动提交配置 #

未提交的事务可能导致数据状态异常：

• 检查PDO自动提交状态：

  var_dump($pdo->getAttribute(PDO::ATTR_AUTOCOMMIT));
  

• 若业务中使用事务，确保执行UPDATE后正确提交或回滚：

  $pdo->beginTransaction();
  try {
      // 执行UPDATE语句
      $pdo->commit();
  } catch (PDOException $e) {
      $pdo->rollBack();
      throw $e;
  }
  

临时应急方案 #

若需快速恢复业务，可给my_col添加默认值规避非空约束：

ALTER TABLE my_table MODIFY COLUMN my_col VARCHAR(10) NOT NULL DEFAULT '';

（注：将10替换为实际列长度，需确认业务逻辑允许该默认值）

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