MySQL多表存在则更新不存在则插入的实现方案咨询

核心思路 #

MySQL的INSERT ... ON DUPLICATE KEY UPDATE仅支持单表操作，因此我们可以将**更新t_goods和t_prices的Upsert（存在则更新，不存在则插入）**拆分为两个独立操作，并用事务保证两个操作的原子性（要么同时成功，要么同时回滚）。

前提条件 #

必须确保t_prices表的goods_id字段存在唯一约束（主键或唯一索引），否则INSERT ... ON DUPLICATE KEY UPDATE无法触发更新逻辑，会直接插入重复记录。

具体实现代码 #

START TRANSACTION;

-- 第一步：更新t_goods的description字段
UPDATE `t_goods`
INNER JOIN (
    SELECT 'bla-bla-bla' AS description, '123456' AS id, 1000 AS price
) AS DataToUpdateTable
ON t_goods.id = DataToUpdateTable.id
SET t_goods.description = DataToUpdateTable.description;

-- 第二步：对t_prices执行Upsert操作
INSERT INTO `t_prices` (goods_id, price)
SELECT id, price FROM (
    SELECT '123456' AS id, 1000 AS price
) AS DataToUpdateTable
ON DUPLICATE KEY UPDATE price = VALUES(price);

COMMIT;

原方案问题说明 #

你之前用LEFT JOIN关联t_prices的写法，当t_prices中无对应记录时，t_prices的所有字段值为NULL，此时SET t_prices.price = ...的更新逻辑不会生效（因为没有实际的行可以更新）。拆分操作后，INSERT ... ON DUPLICATE KEY UPDATE会自动判断：如果t_prices中已存在对应goods_id的记录，则更新price；如果不存在，则插入一条新记录。

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