MariaDB批量插入多行时跳过两列组合已存在记录的实现方法

原写法错误说明 #

你之前编写的SQL无法执行有两个核心问题：

• MariaDB原生不支持在INSERT ... VALUES语句后直接添加WHERE子句做逐行过滤，会直接触发语法错误
• 你写的NOT EXISTS子句没有关联待插入的具体行数据，即使语法调整正确，也无法实现逐行判断组合是否存在的逻辑

方案一：唯一联合索引+INSERT IGNORE（生产环境首选） #

这是性能最好、可靠性最高的实现方式，分两步操作：

1. 首先给classID和userID字段添加唯一联合索引，从数据库层强制约束两个字段的组合不可重复，避免并发场景下的脏数据：

   ALTER TABLE `enrollments` ADD UNIQUE INDEX `uk_class_user` (`classID`, `userID`);
   

2. 使用INSERT IGNORE语法执行批量插入，遇到唯一键冲突的行数据库会自动跳过，不会中断插入流程也不会抛出错误：

   -- 因为enrollmentsID是自增主键，插入时可以省略该字段
   INSERT IGNORE INTO `enrollments` (`classID`, `userID`)
   VALUES (1,2),(1,3),(1,4),(1,5);
   

针对你给出的测试数据，上述SQL执行后只会插入classID=1、userID=5的新记录，其余已存在的组合会被自动忽略，完全符合预期。

方案二：派生表+NOT EXISTS（无索引时临时使用） #

如果暂时无法给表添加唯一索引，可以将待插入的多行数据先构造为临时派生表，再通过关联判断过滤已存在的记录，SQL写法如下：

INSERT INTO `enrollments` (`classID`, `userID`)
SELECT tmp.classID, tmp.userID
FROM (
    -- 把所有待插入的数据通过UNION ALL拼成临时表
    SELECT 1 AS classID, 2 AS userID
    UNION ALL SELECT 1,3
    UNION ALL SELECT 1,4
    UNION ALL SELECT 1,5
) AS tmp
WHERE NOT EXISTS (
    SELECT 1
    FROM `enrollments` e
    WHERE e.classID = tmp.classID AND e.userID = tmp.userID
);

该方案不需要修改表结构即可实现需求，但批量插入数据量较大时性能远低于方案一，且高并发写入场景下依然存在重复插入的风险，仅建议临时使用。

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