SQL如何条件插入数据并返回rowid或0且不重复WHERE子句？

可行方案 #

你当前调用last_insert_rowid()说明使用的是SQLite数据库，原写法不生效是因为SQLite不支持在INSERT ... VALUES后直接加WHERE条件，要实现「不重复书写WHERE子句、插入成功返回rowid、未插入返回0」的单语句需求，直接用CTE+INSERT...SELECT+RETURNING配合聚合函数即可，不需要事务。

兼容SQLite 3.35.0及以上版本（支持RETURNING语法） #

直接执行以下SQL即可：

SELECT COALESCE(
  (
    -- 存在性校验只写一次，不需要重复
    WITH check_dup AS (
      SELECT 1
      FROM comment
      WHERE body = @text AND userid = @userid
      HAVING COUNT(*) = 0
    )
    INSERT INTO comment(body, userid, create_date)
    SELECT @text, @userid, @date
    FROM check_dup
    RETURNING rowid
  ),
  0
) AS res;

逻辑说明 #

• 公共表表达式check_dup只做一次重复校验：仅当不存在body=@text且userid=@userid的记录时，才会返回1行数据，否则返回空集
• 后续INSERT操作仅从check_dup取数，空集场景下不会执行插入，完全避免重复写WHERE判断逻辑
• RETURNING子句会在插入成功时直接返回新行的rowid，没有插入动作时子查询返回NULL，最外层用COALESCE把NULL转为要求的0
• 整条语句是单SQL，不需要拆分多条执行，也不需要手动控制事务

注意事项 #

• 如果使用的SQLite版本低于3.35.0（不支持RETURNING语法），没有纯单语句的实现方案，必须配合事务或多语句执行
• 务必给(body, userid)字段创建唯一索引，高并发场景下仅靠SQL层面的存在性校验无法完全避免重复插入，唯一索引是数据一致性的最终保障
• 如果你实际业务的WHERE判断逻辑更复杂，只需要修改check_dup里的查询条件即可，外层逻辑不需要调整

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