MySQL中UPDATE无影响行时如何在单次请求中执行对应INSERT操作

可行解决方案 #

方案1：多语句合并单次提交（通用方案，无需修改表结构） #

绝大多数数据库客户端都支持一次性提交多条SQL语句，你可以直接将UPDATE和带判断的INSERT合并为同一个请求发送，无需依赖存储过程：

-- 先执行更新操作
UPDATE my_table 
SET column_i_want_to_update = 'column value' 
WHERE id_which_is_not_primary_key_nor_unique = 'id value';
-- 仅当无匹配行时执行插入
INSERT INTO my_table (column_i_want_to_update, id_which_is_not_primary_key_nor_unique) 
SELECT 'column value', 'id value'
WHERE NOT EXISTS (
    SELECT 1 FROM my_table 
    WHERE id_which_is_not_primary_key_nor_unique = 'id value'
);

注：MySQL环境下需要在SELECT后面加FROM DUAL，PostgreSQL、SQLite等数据库可直接省略虚表部分。
高并发场景下该方案可能出现重复插入的问题，业务允许的话优先选择方案2。

方案2：添加唯一约束后使用ON DUPLICATE KEY（性能最优） #

如果业务上id_which_is_not_primary_key_nor_unique字段的值具备唯一性，直接给该字段添加唯一约束：

ALTER TABLE my_table ADD UNIQUE KEY uniq_idx_target_id (id_which_is_not_primary_key_nor_unique);

添加完成后你原来的ON DUPLICATE KEY语句即可正常生效，同时还能避免重复脏数据写入，执行性能最高。

方案3：PostgreSQL单语句CTE实现 #

如果你使用的是PostgreSQL，可以通过WITH语法将逻辑合并为单条SQL语句执行：

WITH update_res AS (
    UPDATE my_table 
    SET column_i_want_to_update = 'column value' 
    WHERE id_which_is_not_primary_key_nor_unique = 'id value'
    RETURNING 1
)
INSERT INTO my_table (column_i_want_to_update, id_which_is_not_primary_key_nor_unique)
SELECT 'column value', 'id value'
WHERE NOT EXISTS (SELECT 1 FROM update_res);

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