基于条件的MySQL/MariaDB ON DUPLICATE KEY UPDATE实现问询

好问题！在MariaDB里实现带条件的ON DUPLICATE KEY UPDATE其实很灵活，主要是利用条件函数或者表达式来控制更新行为，我结合你的场景给你两种常用的实现方式：

方式一：在更新赋值中添加条件判断（最常用） #

你可以在ON DUPLICATE KEY UPDATE的每个列赋值语句里，用IF()函数或者CASE表达式来设置更新条件——当条件满足时用新值替换原有值，不满足时保持原有值（相当于不更新）。

比如假设你的需求是：当主键name重复时，只有新的age大于原有age，才更新所有列（age到col100），对应的SQL语句可以写成：

INSERT INTO beautiful (name, age, col3, col4, ..., col100)
VALUES 
    ('Helen', 24, ...), 
    ('Katrina', 21, ...), 
    ('Samia', 22, ...), 
    ('Hui Ling', 25, ...), 
    ('Yumie', 29, ...)
ON DUPLICATE KEY UPDATE
    -- 只有新age大于旧age时才更新，否则保持原值
    age = IF(VALUES(age) > age, VALUES(age), age),
    col3 = IF(VALUES(age) > age, VALUES(col3), col3),
    col4 = IF(VALUES(age) > age, VALUES(col4), col4),
    -- 以此类推，直到col100
    col100 = IF(VALUES(age) > age, VALUES(col100), col100);

如果不同列有不同的更新条件，也可以单独设置，比如只在新的col3不为空时更新col3，用CASE处理更复杂的分支：

ON DUPLICATE KEY UPDATE
    age = IF(VALUES(age) > age, VALUES(age), age),
    col3 = IF(VALUES(col3) IS NOT NULL, VALUES(col3), col3),
    col4 = CASE 
        WHEN VALUES(col4) > 100 THEN VALUES(col4)
        ELSE col4
    END;

这里的VALUES(column)是用来引用INSERT语句中对应列的新值的语法，在MariaDB 10.3+也可以用NEW.column替代，但VALUES()的兼容性更好。

方式二：先筛选满足条件的记录再执行插入/更新 #

如果你想完全跳过不满足条件的记录（既不插入也不更新），可以用INSERT ... SELECT ...的写法，先把符合条件的记录筛选出来，再执行操作。

比如你只想处理age大于23的记录：

INSERT INTO beautiful (name, age, col3, col4, ..., col100)
SELECT name, age, col3, col4, ..., col100
FROM (
    VALUES 
        ('Helen', 24, ...), 
        ('Katrina', 21, ...), 
        ('Samia', 22, ...), 
        ('Hui Ling', 25, ...), 
        ('Yumie', 29, ...)
) AS temp
WHERE temp.age > 23 -- 先筛选满足条件的记录
ON DUPLICATE KEY UPDATE
    age = VALUES(age),
    col3 = VALUES(col3),
    -- ... 直到col100
    col100 = VALUES(col100);

这种方式下，不满足age>23的记录不会被插入，也不会触发主键重复时的更新操作。

注意点 #

• 如果条件不满足时，用IF(条件, 新值, 原值)的写法虽然会执行UPDATE，但实际不会修改数据，性能上几乎没有损耗。
• 确保你的表确实有主键（name），否则ON DUPLICATE KEY UPDATE不会生效。

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