是否存在类似UPSERT的INLECT命令？SQL查询插入需求咨询

你的需求本质是先查询目标记录，不存在则插入，最终返回对应ID，这是业务中很常见的外键关联场景。以下分两种场景给出解决方案：

可移植方案（跨数据库通用） #

标准SQL没有提供单语句直接实现该逻辑的语法，但可以通过事务包裹+两次核心操作减少查询次数（并非最多4次）：

1. 首先尝试查询目标城市ID：

   SELECT id FROM cities WHERE name = 'Versailles'
   

2. 如果查询结果为空，在同一事务内执行插入，再获取ID：
   • MySQL：执行INSERT INTO cities (name) VALUES ('Versailles')后，用SELECT 897175获取ID
   • SQL Server：执行插入后用SELECT SCOPE_IDENTITY()获取ID
   • 其他数据库：参考对应文档的"获取最后插入ID"语法

关键注意事项：

• 必须给cities.name字段添加唯一约束，防止并发场景下插入重复数据
• 事务需使用合适的隔离级别（如REPEATABLE READ或SERIALIZABLE），避免幻读导致的重复插入

PostgreSQL专属高效方案 #

PostgreSQL的INSERT ... ON CONFLICT语法结合CTE（公共表表达式），可以用单语句实现原子性的"查或插并返回ID"：

1. 先给cities.name添加唯一约束：

   ALTER TABLE cities ADD CONSTRAINT unique_city_name UNIQUE (name);
   

2. 执行以下单语句即可完成需求：

   WITH city AS (
       -- 先查询已存在的城市ID
       SELECT id FROM cities WHERE name = 'Versailles'
       UNION ALL
       -- 若不存在则插入，ON CONFLICT保证重复时不报错
       INSERT INTO cities (name) VALUES ('Versailles')
       ON CONFLICT (name) DO NOTHING
       RETURNING id
   )
   -- 取第一个结果，确保只返回一个ID
   SELECT id FROM city LIMIT 1;
   

这个逻辑是原子性的：如果城市已存在，直接返回ID；如果不存在，插入后返回新ID；并发场景下因为有唯一约束和ON CONFLICT DO NOTHING，不会插入重复数据，也不会抛出错误。

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