执行全表UPDATE触发Column 'quality' cannot be null错误原因排查

执行带WHERE条件的UPDATE语句时，因匹配0行无报错：

mysql> UPDATE DiscussionPost SET timestamp = timestamp WHERE id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

但执行全表UPDATE语句时，触发Column 'quality' cannot be null错误：

mysql> UPDATE DiscussionPost SET timestamp = timestamp;
ERROR 1048 (23000): Column 'quality' cannot be null

数据表结构 #

创建语句：

CREATE TABLE DiscussionPost
(
    id BIGINT                 NOT NULL AUTO_INCREMENT PRIMARY KEY,
    timestamp     DATETIME(6) NOT NULL,
    quality       FLOAT       NOT NULL DEFAULT 0,
    ...
);

表结构详情：

+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | bigint      | NO   | PRI | NULL    | auto_increment |
| timestamp     | datetime(6) | NO   |     | NULL    |                |
| quality       | float       | NO   |     | 0       |                |
...
+---------------+-------------+------+-----+---------+----------------+

数据验证 #

查询结果显示所有行的quality列均为0：

mysql> select quality, timestamp from DiscussionPost;
+---------+----------------------------+
| quality | timestamp                  |
+---------+----------------------------+
|       0 | 2023-12-02 10:37:38.503880 |
|       0 | 2023-12-02 10:37:38.511738 |
|       0 | 2023-12-02 10:37:38.515614 |
|       0 | 2023-12-02 10:37:38.520277 |
|       0 | 2023-12-02 10:37:38.537947 |
|       0 | 2023-12-02 10:37:38.545949 |
|       0 | 2023-12-02 10:37:38.576927 |
|       0 | 2023-12-02 10:37:38.594577 |
|       0 | 2023-12-02 10:37:38.599863 |
|       0 | 2023-12-02 10:37:38.616189 |
|       0 | 2023-12-02 10:37:38.628171 |
|       0 | 2023-12-02 10:37:38.633610 |
+---------+----------------------------+

带WHERE条件的UPDATE不报错的原因 #

这条语句的WHERE id=1未匹配到任何行（Rows matched:0），MySQL没有对任何行执行更新操作，因此不会触发任何行级的约束检查（包括quality列的NOT NULL约束），所以无报错。

全表UPDATE报错的可能原因 #

1. 触发器影响 #

表上存在BEFORE UPDATE触发器，当更新行时，触发器逻辑错误地将quality列设置为NULL。全表更新会触发所有行的触发器，从而触发NOT NULL约束报错；而带WHERE的UPDATE因无匹配行，触发器未被触发。

2. 数据隐式异常 #

虽然查询显示quality均为0，但实际存储中存在quality为NULL的行：

• 比如表结构修改前（当时quality无默认值），通过非法手段插入了NULL值；
• 或者表数据因损坏导致存储值与查询显示值不一致。
  MySQL在执行UPDATE时会读取实际存储值，触发NOT NULL约束。

3. 生成列依赖问题 #

如果quality是生成列（表结构中的...可能包含该定义），其生成逻辑依赖的其他列在更新时发生变化，导致生成结果为NULL，触发约束。

4. 执行计划差异 #

全表更新使用的执行计划与带WHERE的更新不同，MySQL会重新评估所有行的约束；而带WHERE的更新因匹配0行，直接跳过了约束检查步骤。

验证建议 #

• 检查表上的触发器：

  SHOW TRIGGERS LIKE 'DiscussionPost';
  

• 查看完整表结构，确认是否存在生成列：

  SHOW CREATE TABLE DiscussionPost;
  

• 验证实际存储的quality值：

  SELECT id, quality FROM DiscussionPost WHERE quality IS NULL;
  

• 尝试更新单条存在的行，观察是否报错：

  UPDATE DiscussionPost SET timestamp = timestamp WHERE id = 2;
  

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