MySQL使用INSERT/UPDATE ON DUPLICATE KEY更新行时如何单查询取旧值

有两种成熟方案可以实现接近单次查询的效率完成upsert+旧值捕获，无需提前全表扫描或逐行执行两次查询：

最优方案是利用MySQL会话级用户变量暂存旧值，仅需1次upsert查询+1次轻量变量查询即可拿到结果，性能远高于先查后改的方案。

示例代码（目标表为user，主键user_id，包含name、age普通字段）：

-- 执行upsert同时将旧值赋值给会话变量
INSERT INTO user (user_id, name, age) 
VALUES (1, '张三', 20)
ON DUPLICATE KEY UPDATE
    -- IF语句仅用于实现赋值逻辑，最终返回新值不影响更新结果
    name = IF(@old_name := name, VALUES(name), VALUES(name)),
    age = IF(@old_age := age, VALUES(age), VALUES(age)),
    -- 标记本次操作是更新还是插入
    @is_duplicate := 1;

-- 查询变量获取变更信息
SELECT @is_duplicate AS is_update, @old_name AS old_name, @old_age AS old_age;

• 结果说明：
  • 若is_update为1，代表触发更新操作，old_*字段为更新前的旧值
  • 若is_update为NULL，代表触发插入操作，无旧值
• 注意：用户变量属于当前会话独有，并发场景下不会和其他请求冲突，安全可用。

如果需要一次性操作大量数据，推荐使用临时表方案，总查询次数固定和数据量无关，性能损耗极低：

-- 1. 创建临时表存储待upsert的所有新数据
CREATE TEMPORARY TABLE temp_user LIKE user;
-- 2. 批量写入所有待操作数据到临时表
INSERT INTO temp_user (user_id, name, age) 
VALUES (1,'张三',20), (2,'李四',25), (3,'王五',30);
-- 3. 创建临时日志表存储旧值
CREATE TEMPORARY TABLE change_log (user_id INT PRIMARY KEY, old_name VARCHAR(255), old_age INT);
-- 4. 联表查询所有待更新记录的旧值写入日志表，仅查询匹配主键的行无需扫描全表
INSERT INTO change_log (user_id, old_name, old_age)
SELECT u.user_id, u.name, u.age 
FROM user u 
INNER JOIN temp_user t ON u.user_id = t.user_id;
-- 5. 执行批量upsert
INSERT INTO user (user_id, name, age) 
SELECT * FROM temp_user 
ON DUPLICATE KEY UPDATE name=VALUES(name), age=VALUES(age);
-- 6. 直接查询日志表即可拿到所有变更记录的旧值
SELECT * FROM change_log;

如果有表修改权限，也可以提前给目标表加BEFORE UPDATE触发器，自动把更新前的旧值写入固定日志表，执行完批量upsert后直接查日志表即可，步骤更简洁。

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