MySQL批量插入更新需求：id为空则插入，存在则更新，忽略不存在ID

嘿，这个需求可以用MySQL的INSERT ... SELECT结合ON DUPLICATE KEY UPDATE来实现，完美匹配你要的「NULL id插入新行、存在id更新、不存在id忽略」的逻辑。我给你详细说明：

你的表texts的id是自增主键，刚好可以利用主键冲突触发更新逻辑，同时通过子查询过滤掉那些指定了不存在id的记录，避免误插入已删除的id行。

最终执行语句（兼容多数MySQL版本） #

INSERT INTO texts (id, body)
SELECT id, body FROM (
  -- 这里列出所有要处理的记录
  SELECT NULL AS id, 'create new row' AS body
  UNION ALL
  SELECT NULL AS id, 'create other new row' AS body
  UNION ALL
  SELECT 1 AS id, 'update id 1' AS body
  UNION ALL
  SELECT 2 AS id, 'update id 2' AS body
  UNION ALL
  SELECT 3 AS id, 'update id 3' AS body
) AS temp_records
-- 过滤条件：要么是插入新行（id为NULL），要么是指定的id确实存在于原表中
WHERE (temp_records.id IS NULL) OR (EXISTS(SELECT 1 FROM texts WHERE id = temp_records.id))
-- 当主键冲突（即id已存在）时，更新对应行的body字段
ON DUPLICATE KEY UPDATE
  body = temp_records.body;

逻辑拆解 #

• 插入新行：当id为NULL时，WHERE条件直接通过，MySQL会自动为这条记录生成自增id，完成插入。
• 更新已有行：当指定的id在原表中存在时，INSERT尝试插入会触发主键冲突，此时ON DUPLICATE KEY UPDATE会执行，用新的body值覆盖原有值。
• 忽略已删除的id：当指定的id不存在于原表中（已经被删除），EXISTS判断会返回false，这条记录会被WHERE条件过滤掉，不会被插入或更新，正好满足你「忽略」的要求。

简化写法（MySQL 8.0.19+适用） #

如果你的MySQL版本比较新，也可以用VALUES子句构造临时数据，写法更接近你最初期望的格式：

INSERT INTO texts (id, body)
SELECT id, body FROM (
  VALUES
    (NULL, 'create new row'),
    (NULL, 'create other new row'),
    (1, 'update id 1'),
    (2, 'update id 2'),
    (3, 'update id 3')
) AS temp_records(id, body)
WHERE (temp_records.id IS NULL) OR (EXISTS(SELECT 1 FROM texts WHERE id = temp_records.id))
ON DUPLICATE KEY UPDATE
  body = temp_records.body;

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