MariaDB使用子查询插入时校验目标表重复及INSERT IGNORE失效问题

问题根因 #

MariaDB的INSERT IGNORE语法仅会在插入操作触发唯一性约束冲突/主键冲突时才会跳过对应行，你的Test表目前没有为EmployeeId+UserId字段创建联合唯一约束，所以IGNORE关键字不会识别到这两个字段的重复值，自然不会跳过插入。

解决步骤 #

第一步：给Test表添加联合唯一约束 #

先执行以下SQL创建唯一索引：

ALTER TABLE authdb.test 
ADD UNIQUE INDEX idx_unique_employee_user (EmployeeId, UserId);

注意：如果当前Test表已经存在EmployeeId+UserId重复的行，需要先清理重复数据后再执行上述建索引语句，否则索引会创建失败。

第二步：执行插入语句 #

你原来的INSERT IGNORE语句本身写法没问题，加完索引后就可以正常生效，重复数据会自动跳过：

INSERT IGNORE INTO authdb.test (EmployeeId, UserId)
SELECT e.Id, e.UserId 
FROM basedb.employee e
WHERE e.UserId != 0;

可选替代方案 #

如果不想修改表结构加索引，也可以用NOT EXISTS语法实现重复校验，写法如下：

INSERT INTO authdb.test (EmployeeId, UserId)
SELECT e.Id, e.UserId 
FROM basedb.employee e
WHERE e.UserId != 0
AND NOT EXISTS (
    SELECT 1 FROM authdb.test t 
    WHERE t.EmployeeId = e.Id AND t.UserId = e.UserId
);

两种方案的区别：加唯一索引是数据库层面的强校验，能避免所有场景下的重复数据写入，更稳妥；NOT EXISTS方案不需要修改表结构，但仅能保证当前插入语句不写重复，高并发场景下还是可能出现重复数据。

内容的提问来源于stack exchange，提问作者Abhijit Mondal Abhi