MariaDB中仅当非唯一外键无对应记录时批量插入数据

我有表A，结构如下：

还有表B，包含指向表A的外键，结构如下：

我希望仅当表B中不存在任何引用表A某条记录（通过fk_table_a）的数据集时，才向表B插入数据。但fk_table_a并非唯一键且无法修改，理论上可插入相同外键的多条记录，但我不希望出现这种情况。

我使用INSERT INTO ... SELECT语句批量插入，SELECT语句结构大致如下：

INSERT INTO table_b (fk_table_a, field_a, field_b, ...)
SELECT ot1.id_table_a, ot2.field_a, ot3.field_b
FROM other_table_1 ot1
JOIN other_table_2 ot2 ...
JOIN other_table_3 ot3 ...
....#仅当ot1.id_table_a未在table_b中存在时才插入当前数据集！

需求总结：SELECT返回的每条数据，若table_b中已存在相同fk_table_a的记录，则不插入该数据。

方法1：用NOT EXISTS子查询过滤 #

直接在SELECT的WHERE子句中添加判断，过滤掉table_b已存在对应外键的记录：

INSERT INTO table_b (fk_table_a, field_a, field_b, ...)
SELECT ot1.id_table_a, ot2.field_a, ot3.field_b
FROM other_table_1 ot1
JOIN other_table_2 ot2 ...
JOIN other_table_3 ot3 ...
WHERE NOT EXISTS (
    SELECT 1 
    FROM table_b 
    WHERE table_b.fk_table_a = ot1.id_table_a
);

逻辑直白：只要目标表中没找到当前外键，就保留这条数据用于插入。

方法2：用LEFT JOIN + IS NULL过滤 #

通过左连接目标表，筛选出未匹配到的记录：

INSERT INTO table_b (fk_table_a, field_a, field_b, ...)
SELECT ot1.id_table_a, ot2.field_a, ot3.field_b
FROM other_table_1 ot1
JOIN other_table_2 ot2 ...
JOIN other_table_3 ot3 ...
LEFT JOIN table_b tb ON tb.fk_table_a = ot1.id_table_a
WHERE tb.id IS NULL;

左连接后，若table_b中无对应外键记录，关联后的tb.id会是NULL，以此作为过滤条件。

方法3：（MySQL专属）创建唯一索引配合ON DUPLICATE KEY UPDATE #

如果你有权限给fk_table_a加唯一索引（不修改原有外键约束），可以用这个方法避免并发场景下的重复插入：

1. 先创建唯一索引：

CREATE UNIQUE INDEX idx_unique_fk_table_a ON table_b(fk_table_a);

2. 执行插入语句：

INSERT INTO table_b (fk_table_a, field_a, field_b, ...)
SELECT ot1.id_table_a, ot2.field_a, ot3.field_b
FROM other_table_1 ot1
JOIN other_table_2 ot2 ...
JOIN other_table_3 ot3 ...
ON DUPLICATE KEY UPDATE id = id; -- 无实际更新，仅跳过重复插入

这个方法能处理并发插入的冲突，但前提是你能创建索引，否则优先用前两种方法。

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