SQLite单条SQL实现不存在则插入、存在则查询的最高效方法

现有SQLite表alarms，仅包含2个字段：

• id：int类型
• name：text类型
  核心要求：用单条SQL语句实现逻辑：查询指定name的记录，如果该name不存在就插入对应记录，方案要保证执行效率最优。
  之前尝试的写法如下，执行时报new insert syntax error错误：

select name, (insert into alarms (name) values ('test_name')) from alarms where name = 'test_name'

SQLite不允许在SELECT的列表达式、子查询位置执行INSERT/UPDATE/DELETE这类写操作，这些位置只支持返回只读结果的查询逻辑，所以这个写法本身不符合语法规则。

前置准备（必须做，兼顾正确性和效率） #

先给name字段建唯一索引，一方面从表结构层面杜绝重复name数据，另一方面把存在性判断的查询效率拉到常数级：

CREATE UNIQUE INDEX IF NOT EXISTS idx_alarms_name ON alarms(name);

单SQL实现（适配SQLite 3.35.0及以上版本，覆盖绝大多数当前在用的生产环境） #

用SQLite原生UPSERT语法加RETURNING子句，一条语句搞定全部逻辑：

INSERT INTO alarms(name) VALUES ('test_name')
ON CONFLICT(name) DO UPDATE SET name = excluded.name
RETURNING id, name;

逻辑说明 #

• 当test_name不存在时：直接插入新记录，返回新生成的id和对应的name。
• 当test_name已存在时：触发唯一键冲突，执行的UPDATE操作只是把name设成和原值完全一样的内容，不会产生实际的磁盘写入开销，最后直接返回库里已存在的那条对应记录。
• 整个语句是原子执行的，不会出现并发场景下多个请求同时判断不存在、重复插入的问题；所有逻辑都在SQLite内核层完成，没有多语句来回交互的额外开销，搭配唯一索引效率拉满，是当前场景下的最优解。

如果是2021年之前发布的、低于3.35.0的老旧SQLite版本，因为不支持RETURNING语法，没法在单条语句里同时完成数据写入和结果返回，建议升级版本后使用上述方案。

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