如何利用SQL子查询在Insert语句中实现“若不存在则插入”的幂等性？

最稳妥的跨数据库幂等插入方案是INSERT … SELECT + NOT EXISTS，它不依赖特定方言、不触发唯一键异常；需确保子查询中显式关联待插字段（如WHERE email = ‘xxx’），避免漏写关联条件导致逻辑错误。

INSERT … SELECT + NOT EXISTS 实现幂等插入

直接用 INSERT INTO ... SELECT 配合 NOT EXISTS 是最稳妥的跨数据库方案，不依赖特定方言，也不触发唯一键冲突异常。

常见错误是把 NOT EXISTS 写成 WHERE NOT EXISTS (SELECT 1 FROM ... WHERE ...) 却漏掉关联条件，导致子查询变成“只要表里有任意一行就永远不插”。

• 必须在子查询中通过字段显式关联外层待插入值，例如：WHERE t.id = new.id
• 子查询的 SELECT 1 只是占位，不能写成 SELECT * 或空括号
• 如果目标表有自增主键，INSERT 中需明确列出字段名（避开 id），否则可能因列数不匹配报错 Column count doesn't match value count

示例（向 users 插入新用户，按 email 去重）：

INSERT INTO users (email, name, created_at)
SELECT 'alice@example.com', 'Alice', NOW()
FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM users WHERE email = 'alice@example.com'
);

MySQL 的 INSERT IGNORE 和 ON DUPLICATE KEY UPDATE

INSERT IGNORE 看似简单，但只对违反 PRIMARY KEY 或 UNIQUE 约束的行静默跳过，其他错误（如类型不匹配、NOT NULL 字段为空）仍会报错，不是真正的“忽略所有失败”。

ON DUPLICATE KEY UPDATE 更灵活，但容易误用：如果没设 ELSE 分支，默认会更新整行（包括你不想动的字段），甚至可能把 updated_at 改成当前时间而掩盖真实业务逻辑。

• 必须确保目标字段上有 UNIQUE 或 PRIMARY KEY 约束，否则 IGNORE 和 ON DUPLICATE KEY 完全不生效
• ON DUPLICATE KEY UPDATE 中建议只更新必要字段，比如 updated_at = VALUES(updated_at)，避免 name = VALUES(name) 这类覆盖已有值的操作
• MySQL 8.0+ 支持 VALUES(col) 引用 INSERT 中对应列的值，别写成 VALUES(col) AS v —— 这是无效语法

PostgreSQL 的 INSERT … ON CONFLICT DO NOTHING / DO UPDATE

ON CONFLICT 是 PostgreSQL 的标准解法，但陷阱在于：它只响应 *明确指定的约束名或列组合*。如果只写 ON CONFLICT (email)，而实际约束是 UNIQUE (email, tenant_id)，就会失效并抛出 duplicate key violates unique constraint 错误。

另一个常见疏忽是 DO UPDATE SET 中没加 WHERE 条件，导致所有冲突行都被更新，而非仅当前这一条。

• 务必用 d table_name 查清约束名，或直接写 ON CONFLICT ON CONSTRAINT constraint_name
• DO UPDATE 里推荐用 WHERE excluded.email = users.email 显式限定范围，excluded 是 PostgreSQL 为冲突行自动创建的临时别名
• 如果只想插入、绝不更新，用 DO NOTHING 更安全；但注意它不返回影响行数（pg_affected_rows 返回 0），业务代码别靠这个判断是否真插入了

为什么不能用 SELECT + IF 判断再 INSERT？

应用层先 SELECT 再 INSERT 看起来直观，但在并发场景下必然出现竞态：两个请求同时查到“不存在”，然后都执行 INSERT，最终违反唯一约束。

即使加了应用层锁（如 Redis 分布式锁），也会显著拖慢吞吐，且锁粒度难控制——锁整个表太重，锁单个 email 值又得自己维护锁生命周期。

• 数据库原生的原子性语句（INSERT ... SELECT + NOT EXISTS、ON CONFLICT、ON DUPLICATE KEY）才是唯一能保证线程安全的方案
• 如果业务要求“插入成功才发消息”，必须用数据库返回的影响行数（如 MySQL 的 mysql_affected_rows()、PostgreSQL 的 GET DIAGNOSTICS row_count = ROW_COUNT）判断，而不是靠 SELECT 结果推断

真正难的是约束设计本身：没有合适的唯一键，再好的语法也救不了。比如用手机号做幂等依据，却没建 UNIQUE 索引，那所有方案都会失效。