MariaDB带子查询的UPDATE语句SET子句求值异常问题咨询

表结构与初始数据 #

CREATE OR REPLACE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    discount_price DECIMAL(10,2)
);

INSERT INTO products (name, category, price, discount_price) VALUES
('Laptop', 'electronics', 1000.00, 900.00),
('Smartphone', 'electronics', 800.00, 720.00),
('Tablet', 'electronics', 500.00, 450.00),
('Headphones', 'accessories', 200.00, 180.00);

场景1：预期行为 #

执行以下查询时：

UPDATE products 
SET price = price * 1.10, 
    discount_price = price * 0.90 
WHERE category = 'electronics';

SET子句按预期顺序求值：先更新price（price = price * 1.10），再基于更新后的price计算discount_price，结果正确。

场景2：意外行为 #

但当WHERE子句使用子查询时：

UPDATE products 
SET price = price * 1.10, 
    discount_price = price * 0.90 
WHERE id IN (SELECT id FROM products WHERE category = 'electronics');

行为发生变化：SET子句似乎同时求值，导致discount_price基于price的旧值计算，结果错误。

我的认知 #

根据MariaDB的特性，UPDATE语句的多SET子句应从左到右顺序求值，简单WHERE条件下正常，但带子查询时行为异常。

问题 #

该行为是MariaDB的Bug吗？为何子查询会导致SET子句同时求值？如何确保顺序求值？

环境：MariaDB版本10.6.20（AWS RDS MariaDB）

解答 #

1. 这不是Bug，是查询优化器的执行策略差异
   MariaDB的查询优化器在处理带自查询的UPDATE时，会采用不同的执行路径：

• 当使用简单WHERE category = 'electronics'时，优化器直接对目标表逐行更新，SET子句会按左到右顺序使用当前行的更新后值计算。
• 当使用WHERE id IN (SELECT id FROM products...)这种自查询时，优化器会先将子查询的结果集（符合条件的id）提取到临时集合中，再基于这个集合更新原表。此时优化器会选择基于原始行快照计算所有SET子句的值——这是为了避免子查询结果在更新过程中发生变化（幻读风险），因此会先读取所有目标行的原始值，再批量计算更新值。

2. 确保顺序求值的解决方案
   要保证SET子句的顺序求值逻辑，有几种可靠方法：

• 方法一：用用户变量暂存更新后的值
  通过变量先保存更新后的price，再用该变量计算discount_price，无论执行路径如何，都能保证依赖关系：

  UPDATE products 
  SET price = @new_price := price * 1.10, 
      discount_price = @new_price * 0.90 
  WHERE id IN (SELECT id FROM products WHERE category = 'electronics');
  

• 方法二：将子查询转换为JOIN
  用JOIN替代IN子查询，让优化器回到逐行更新的执行路径：

  UPDATE products p
  JOIN (SELECT id FROM products WHERE category = 'electronics') sub
  ON p.id = sub.id
  SET p.price = p.price * 1.10,
      p.discount_price = p.price * 0.90;
  

• 方法三：使用ORDER BY和LIMIT（适用于特定场景）
  强制优化器逐行处理更新，需配合足够大的LIMIT值覆盖所有目标行：

  UPDATE products 
  SET price = price * 1.10, 
      discount_price = price * 0.90 
  WHERE id IN (SELECT id FROM products WHERE category = 'electronics')
  ORDER BY id
  LIMIT 1000; -- 设置足够大的数值覆盖所有目标行
  

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