MySQL多表UPDATE语句中行的动态更新逻辑疑问

测试环境初始化 #

先创建并初始化两张测试表：

CREATE TABLE t1(
  id INT
);

INSERT INTO t1 VALUES (1), (2), (1), (2);

CREATE TABLE t2(
  id INT,
  new_id INT
);

INSERT INTO t2 VALUES (1, 8);

各场景执行情况 #

场景1 #

执行以下UPDATE语句：

UPDATE t1, t2
SET t1.id = t2.new_id,
t2.new_id = t2.new_id + 2
WHERE t1.id = t2.id;

结果：t1的部分行使用了t2更新后的值。

场景2 #

添加t2.id = t2.id + 1更新项后，执行语句：

UPDATE t1, t2
SET t1.id = t2.new_id,
t2.new_id = t2.new_id + 2,
t2.id = t2.id + 1
WHERE t1.id = t2.id;

结果：t1中id=2的行未按预期更新，且id=1的行使用了t2的旧值。

场景3 #

修改WHERE条件为或逻辑后，执行语句：

UPDATE t1, t2
SET t1.id = t2.new_id,
t2.new_id = t2.new_id + 2
WHERE t1.id = t2.id OR t1.id = t2.id + 1;

结果：t1所有符合条件的行均被更新。

场景4 #

同时更新t2.id并使用或逻辑WHERE条件，执行语句：

UPDATE t1, t2
SET t1.id = t2.new_id,
t2.new_id = t2.new_id + 2,
t2.id = t2.id + 1
WHERE t1.id = t2.id OR t1.id = t2.id + 1;

结果：t1所有行均使用t2的旧值。

核心疑问解答 #

1. WHERE子句是否在更新前执行？ #

是的，WHERE子句的过滤逻辑完全基于更新前的原始表数据执行，它的作用是筛选出需要参与更新的行集合。但多表UPDATE的特殊之处在于，后续更新是否会读取实时修改后的数据，取决于是否修改了关联条件中的字段。

2. 为何不同场景结果差异巨大？ #

差异根源在于MySQL处理多表UPDATE时的两种数据读取策略：

• 未修改关联条件字段时（如场景1、3）：MySQL会先基于原始数据生成临时匹配集，但在逐行更新过程中，后续行可能读取到前面行更新后的表数据。比如场景1中t2的new_id被更新为10后，后续匹配的t1行会直接使用这个新值，导致部分t1行用了更新后的值。场景3的OR条件让临时集包含了所有t1的id=1和id=2行，所以这些行都会被更新，且能读取t2更新后的new_id。
• 修改关联条件字段时（如场景2、4）：为了避免关联逻辑混乱，MySQL会强制使用临时匹配集中的原始数据进行更新，不会读取实时修改后的表数据。场景2中t2.id被更新为2，但临时集是基于原始t2.id=1生成的，所以t1.id=2的行不在匹配集内，不会被更新；同时t1.id=1的行只能使用临时集里t2.new_id的旧值8。场景4同理，所有t1行都只能用t2.new_id的原始值8。

3. MySQL UPDATE命令的内部运行机制 #

MySQL多表UPDATE的核心执行流程可分为两步：

1. 构建临时匹配集：根据FROM子句的表关联关系和WHERE条件，使用更新前的原始数据进行查询，生成一个包含所有待更新行的临时结果集，记录每行的原始数据和更新目标位置。
2. 执行更新操作：
   • 若未修改关联条件中的字段，更新过程中可能读取表的实时数据（已被前面行修改后的值）。
   • 若修改了关联条件中的字段，严格使用临时匹配集的原始数据完成更新，避免关联关系失效。
3. 事务保障：对于InnoDB等支持事务的存储引擎，所有更新操作会被包裹在一个事务中，确保原子性（要么全部成功，要么全部回滚）。

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