SQL 如何用 INSERT … SELECT + ON DUPLICATE KEY 实现批量 upsert

能，但必须存在唯一约束且冲突字段需在select中明确提供；错误多因列数不匹配、别名未定义或自增主键缺失；VALUES(col)引用本次插入值，非原表旧值。

mysql 的 INSERT … SELECT + ON DUPLICATE KEY UPDATE 能否批量 upsert？

能，但必须满足唯一约束（UNIQUE 或 PRIMARY KEY）存在，且冲突字段在 SELECT 结果集中被明确提供。它不是“自动识别主键”，而是依赖索引匹配触发 ON DUPLICATE KEY UPDATE 分支。

为什么 INSERT … SELECT 后接 ON DUPLICATE KEY UPDATE 常报错？

常见错误是 Error 1062: Duplicate entry ... for key ... 没被捕获，或直接报 ERROR 1136: column count doesn't match value count —— 这通常因为 SELECT 字段数与目标表列数不一致，或 ON DUPLICATE KEY UPDATE 中引用了未在 SELECT 中出现的别名。

• SELECT 返回的列顺序和数量必须严格对应 INSERT INTO tbl(col1, col2, ...) 的列声明（或表默认顺序）
• ON DUPLICATE KEY UPDATE 只能更新目标表字段，右侧表达式可引用 VALUES(col_name) 或 SELECT 中的别名（需显式 AS），但不能用未定义别名
• 若目标表有自增主键，SELECT 中**必须包含该主键值**（或对应唯一键字段），否则无法触发冲突判断

正确写法示例：带别名的 INSERT … SELECT upsert

假设表 users(id, name, email, updated_at)，其中 email 为 UNIQUE 索引：

INSERT INTO users (id, name, email, updated_at) SELECT u.id, u.name, u.email, NOW() FROM (   SELECT 101 AS id, 'Alice' AS name, 'alice@example.com' AS email   UNION ALL   SELECT 102, 'Bob', 'bob@example.com'   UNION ALL   SELECT 103, 'Charlie', 'charlie@example.com' ) AS u ON DUPLICATE KEY UPDATE   name = VALUES(name),   updated_at = VALUES(updated_at);

注意：VALUES(name) 表示本次 INSERT 尝试插入的 name 值，不是原表旧值；id 在此例中非冲突键，但因 email 是唯一键，只要 email 重复就会触发更新，id 是否重复不影响逻辑。

容易被忽略的性能与语义陷阱

批量 upsert 并不等于“每行独立事务”。MySQL 会将整个语句作为单个语句执行，但：

• 若 SELECT 来源是大表（如 SELECT ... FROM logs WHERE ...），全量扫描 + 逐行索引查找可能很慢，建议确保 WHERE 条件能走索引
• ON DUPLICATE KEY UPDATE 中不能使用子查询（如 updated_at = (SELECT NOW())），会报错 ERROR 1093；必须用 NOW() 或 VALUES(col)
• 如果 SELECT 结果含重复的冲突键（例如两条记录 email 相同），MySQL 会报 ERROR 1062 —— 它不保证内部去重，需业务层预处理
• 影响行数返回值是「插入行数 × 2 + 更新行数」，不可直接当作成功条数；建议用 ROW_COUNT() 结合应用逻辑判断

真正难的不是语法拼写，而是确认冲突依据是否覆盖所有 upsert 场景，以及 SELECT 数据源是否已排除逻辑重复。