如何实现仅在复合主键不存在时批量插入行，并对指定列重复的行去重插入？

首先得明确两个核心需求：

• 对待插入的批量数据，按第二列（假设为type_col）去重，每个type_col仅保留任意一行（示例中前两行type_col=1，只插其中一行）
• 仅当目标表中不存在对应复合主键时，才执行插入操作

下面分主流数据库给出具体实现方案：

MySQL 实现方案 #

MySQL 可以借助 INSERT ... ON DUPLICATE KEY UPDATE 语法跳过已存在的复合主键行，同时用窗口函数先完成数据去重：

INSERT INTO test_table (id_col, type_col, create_time)
SELECT id_col, type_col, create_time
FROM (
    -- 第一步：对原始待插入数据按type_col去重，取每个分组的最新时间行（可换成其他规则取任意行）
    SELECT 
        id_col, 
        type_col, 
        create_time,
        ROW_NUMBER() OVER (PARTITION BY type_col ORDER BY create_time DESC) AS rn
    FROM (
        -- 这里是你的原始待插入数据，注意字符串类型要加引号
        SELECT '19156a48-5097-412b-8564-ce3e1e430ea3' AS id_col, 1 AS type_col, '2020-12-11 09:21:48.380494' AS create_time
        UNION ALL
        SELECT 'c6b91bf3-af67-4557-8c58-cb3664948723' AS id_col, 1 AS type_col, '2020-12-11 09:31:48.380494' AS create_time
        UNION ALL
        SELECT '103f3010-81a7-419c-8b35-6005dd880f07' AS id_col, 3 AS type_col, '2020-12-11 09:21:48.380494' AS create_time
    ) AS temp_data
) AS deduplicated_data
WHERE rn = 1
ON DUPLICATE KEY UPDATE id_col = id_col; -- 复合主键存在时，执行无意义的更新，等价于跳过插入

关键说明： #

• ROW_NUMBER() OVER (PARTITION BY type_col ...) 用来按type_col分组，给每组内的行编号，取rn=1的行就实现了去重
• ON DUPLICATE KEY UPDATE 会在复合主键冲突时触发，这里设置id_col=id_col不做实际修改，直接跳过插入

PostgreSQL 实现方案 #

PostgreSQL 支持更直观的 ON CONFLICT DO NOTHING 语法，同样先完成数据去重：

INSERT INTO test_table (id_col, type_col, create_time)
SELECT id_col, type_col, create_time
FROM (
    SELECT 
        id_col, 
        type_col, 
        create_time,
        ROW_NUMBER() OVER (PARTITION BY type_col ORDER BY create_time DESC) AS rn
    FROM (
        -- 原始待插入数据，注意类型转换（UUID、TIMESTAMP）
        SELECT '19156a48-5097-412b-8564-ce3e1e430ea3'::UUID AS id_col, 1 AS type_col, '2020-12-11 09:21:48.380494'::TIMESTAMP AS create_time
        UNION ALL
        SELECT 'c6b91bf3-af67-4557-8c58-cb3664948723'::UUID AS id_col, 1 AS type_col, '2020-12-11 09:31:48.380494'::TIMESTAMP AS create_time
        UNION ALL
        SELECT '103f3010-81a7-419c-8b35-6005dd880f07'::UUID AS id_col, 3 AS type_col, '2020-12-11 09:21:48.380494'::TIMESTAMP AS create_time
    ) AS temp_data
) AS deduplicated_data
WHERE rn = 1
ON CONFLICT (id_col, type_col) DO NOTHING; -- 复合主键冲突时直接跳过插入

关键说明： #

• ON CONFLICT (id_col, type_col) 指定复合主键作为冲突判断依据
• DO NOTHING 明确告诉数据库冲突时不执行任何操作，直接跳过该行

SQL Server 实现方案 #

SQL Server 可以用 MERGE 语句来实现匹配插入，先通过CTE完成数据去重：

WITH temp_data AS (
    -- 原始待插入数据
    SELECT '19156a48-5097-412b-8564-ce3e1e430ea3' AS id_col, 1 AS type_col, '2020-12-11 09:21:48.380494' AS create_time
    UNION ALL
    SELECT 'c6b91bf3-af67-4557-8c58-cb3664948723' AS id_col, 1 AS type_col, '2020-12-11 09:31:48.380494' AS create_time
    UNION ALL
    SELECT '103f3010-81a7-419c-8b35-6005dd880f07' AS id_col, 3 AS type_col, '2020-12-11 09:21:48.380494' AS create_time
), deduplicated_data AS (
    -- 按type_col去重，取每个分组的最新时间行
    SELECT 
        id_col, 
        type_col, 
        create_time,
        ROW_NUMBER() OVER (PARTITION BY type_col ORDER BY create_time DESC) AS rn
    FROM temp_data
)
MERGE INTO test_table AS target
USING (SELECT id_col, type_col, create_time FROM deduplicated_data WHERE rn = 1) AS source
ON target.id_col = source.id_col AND target.type_col = source.type_col
WHEN NOT MATCHED THEN
    INSERT (id_col, type_col, create_time)
    VALUES (source.id_col, source.type_col, source.create_time);

关键说明： #

• 用CTE（公共表表达式）先定义原始数据和去重后的数据，逻辑更清晰
• MERGE 语句匹配目标表和源数据的复合主键，仅当不匹配（不存在）时执行插入

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