如何用单条INSERT语句实现用户Code校验与SQ自增插入

可以通过**INSERT ... SELECT**语法结合子查询和条件判断，把你原来的先查后插逻辑合并成单条SQL语句，主流关系型数据库（MySQL、SQL Server、PostgreSQL等）都支持这种写法。

核心逻辑说明 #

插入操作仅在指定用户ID下不存在目标Code时执行；同时自动计算SQ值：如果用户已有记录，SQ取当前最大SQ+1；如果用户无任何记录，SQ默认从1开始。

具体SQL语句（以MySQL为例） #

INSERT INTO TABLENAME (ID, SQ, Code)
SELECT 
    ?, 
    COALESCE((SELECT MAX(SQ) FROM TABLENAME WHERE ID = ?), 0) + 1,
    ?
WHERE NOT EXISTS (
    SELECT 1 FROM TABLENAME WHERE ID = ? AND Code = ?
);

参数对应顺序

执行时需要按以下顺序传入参数：

1. 目标用户ID（User_PID）
2. 目标用户ID（User_PID，用于子查询获取最大SQ）
3. 待插入的Code值
4. 目标用户ID（User_PID，用于判断Code是否存在）
5. 待插入的Code值

示例场景验证 #

针对你给出的三个测试场景：

• 用户123456插入Code=123：NOT EXISTS子查询会找到已有记录，条件不成立，语句不执行插入，符合预期。
• 用户654321插入Code=123：NOT EXISTS条件成立，子查询获取到该用户最大SQ为1，计算得SQ=2，执行插入，符合预期。
• 用户999999插入Code=123：NOT EXISTS条件成立，子查询返回NULL，COALESCE将其转为0，计算得SQ=1，执行插入，符合预期。

其他数据库适配 #

• SQL Server：把COALESCE替换为ISNULL即可，语法逻辑完全一致。
• PostgreSQL：直接使用上述语句即可，COALESCE是标准SQL函数，PostgreSQL原生支持。

注意事项 #

1. 并发安全：如果存在高并发插入场景，可能出现多个请求同时通过NOT EXISTS判断，导致插入重复SQ的情况（因为主键是ID+SQ，数据库会抛出主键冲突错误）。这种情况下建议：
   • 在事务中执行该语句，并对用户的记录行加锁（比如MySQL中用SELECT ... FOR UPDATE先锁定用户的最大SQ记录）；
   • 或者利用数据库的原子性特性，结合业务调整逻辑避免冲突。
2. 索引优化：为ID和(ID, Code)创建复合索引，能大幅提升子查询的执行效率，避免大表下的性能问题。

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