如何用单条INSERT OR REPLACE语句实现新增与更新操作？

我想用INSERT OR REPLACE语句实现更新逻辑：传入整数类型主键r_id时执行替换操作，r_id为空时执行插入。但目前代码运行时出现类型不匹配错误，原代码如下：

INSERT OR REPLACE INTO ${tableNames[1].name} (r_id,r_from_ac_type_id,r_to_ac_type_id,r_date,r_amount,r_note) values` +
      recordItems
        .map(
          i =>
            `('${i.r_id}','${i.r_from_ac_type_id}','${i.r_to_ac_type_id}', '${i.r_date}','${i.r_amount}','${i.r_note}')`,
        )
        .join(',')

问题原因 #

1. 类型不匹配：原代码把所有值都用单引号包裹，r_id是整数类型，空值时会被转成''（空字符串），但数据库期望整数类型的主键值，触发类型错误。
2. INSERT OR REPLACE逻辑偏差：该语句的核心是主键存在则替换、不存在则插入。但传入空字符串作为主键时，数据库无法识别为“主键不存在”，反而会尝试将空字符串插入整数字段，引发错误。

修正方案 #

针对r_id做区分处理：空值时传入NULL（数据库会自动按插入逻辑处理，若主键为自增则生成新值），非空时直接输出数字（不加单引号）；其他整数类型字段同理调整。

修改后的代码：

INSERT OR REPLACE INTO ${tableNames[1].name} (r_id,r_from_ac_type_id,r_to_ac_type_id,r_date,r_amount,r_note) values` +
  recordItems
    .map(i => {
      // 处理整数类型主键r_id：空值用NULL，非空直接输出数字
      const rId = i.r_id ? i.r_id : 'NULL';
      // 处理其他整数类型字段（如果r_from_ac_type_id是整数的话）
      const fromAcType = i.r_from_ac_type_id ? i.r_from_ac_type_id : 'NULL';
      const toAcType = i.r_to_ac_type_id ? i.r_to_ac_type_id : 'NULL';
      // 字符串类型字段保留单引号包裹
      return `(${rId},${fromAcType},${toAcType}, '${i.r_date}','${i.r_amount}','${i.r_note}')`;
    })
    .join(',')

额外提示 #

• 如果r_from_ac_type_id、r_to_ac_type_id是字符串类型，保持单引号包裹即可；如果是整数类型，必须去掉单引号，避免重复触发类型不匹配。
• 字符串拼接方式存在SQL注入风险，生产环境建议使用数据库驱动提供的参数化查询（如prepare语句），更安全可靠。

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