如何优化INSERT INTO/SELECT语句，避免特定重复且保留多value4b记录？

问题背景 #

现有表结构：

• Table A（结构不可修改）：

autokey | value1a | value2a | value3a | value4a

• Table B（无主键）：

value1b | value2b | value3b | value4b

当前使用的插入语句：

INSERT INTO TableA (value1a, value2a, value3a, value4a)
    SELECT value1b, NULL AS value2a, 111 AS value3a, value4b AS value4a
    FROM TableB 
    WHERE value1b IS NOT NULL

核心需求：

• 若value1a = value1b、value3a = 111（插入的固定值）且value4a = value4b，跳过插入；
• 若value1a = value1b、value3a = 111且value4a != value4b，执行插入。

解决方案 #

方法1：用NOT EXISTS过滤已存在的组合 #

无需修改表结构，直接在查询中排除TableA已有的(value1a, value3a, value4a)组合：

INSERT INTO TableA (value1a, value2a, value3a, value4a)
SELECT 
    value1b, 
    NULL AS value2a, 
    111 AS value3a, 
    value4b AS value4a
FROM TableB 
WHERE 
    value1b IS NOT NULL
    AND NOT EXISTS (
        SELECT 1 
        FROM TableA 
        WHERE 
            TableA.value1a = TableB.value1b
            AND TableA.value3a = 111
            AND TableA.value4a = TableB.value4b
    )

方法2：添加唯一约束（推荐，长期可靠） #

如果允许给TableA添加唯一约束（不属于修改表字段结构范畴），可以强制保证(value1a, value3a, value4a)的唯一性，后续插入自动跳过重复：

MySQL/MariaDB 实现

先创建唯一约束：

ALTER TABLE TableA ADD UNIQUE KEY idx_unique_v1v3v4 (value1a, value3a, value4a);

再执行插入（用INSERT IGNORE跳过重复）：

INSERT IGNORE INTO TableA (value1a, value2a, value3a, value4a)
SELECT 
    value1b, 
    NULL AS value2a, 
    111 AS value3a, 
    value4b AS value4a
FROM TableB 
WHERE value1b IS NOT NULL;

PostgreSQL 实现

先创建唯一约束：

ALTER TABLE TableA ADD CONSTRAINT idx_unique_v1v3v4 UNIQUE (value1a, value3a, value4a);

再执行插入（用ON CONFLICT跳过重复）：

INSERT INTO TableA (value1a, value2a, value3a, value4a)
SELECT 
    value1b, 
    NULL AS value2a, 
    111 AS value3a, 
    value4b AS value4a
FROM TableB 
WHERE value1b IS NOT NULL
ON CONFLICT (value1a, value3a, value4a) DO NOTHING;

说明 #

• 方法1兼容性强，适合不能修改表约束的场景；
• 方法2从数据库层面保证数据唯一性，避免后续重复插入问题，是更可靠的长期方案。

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