SQLite 也能 Upsert？upsert 的 INSERT OR IGNORE 方案与唯一索引硬性要求详解

SQLite 也能 Upsertupsert 的 INSERT OR IGNORE 方案与唯一索引硬性要求详解【免费下载链接】upsertUpsert on MySQL, PostgreSQL, and SQLite3. Transparently creates functions (UDF) for MySQL and PostgreSQL; on SQLite3, uses INSERT OR IGNORE.项目地址: https://gitcode.com/gh_mirrors/ups/upsert在 SQLite 上做upsert存在则更新、不存在则插入一直是痛点而 Ruby 的upsert库给出了零依赖 ActiveRecord 的方案对 MySQL 和 PostgreSQL 透明创建数据库函数UDF对 SQLite 3 则直接使用INSERT OR IGNORE UPDATE 两步组合。本文带你搞清楚这套 SQLite 方案的原理以及为什么唯一索引是硬性要求——漏掉它数据会悄悄变重复。先认识 upsert一个 selector 一个 setterupsert的 API 借鉴了 Mongo Ruby Driver 的 update 写法核心就两个参数参数作用selector唯一标识一行的条件如{:name Jerry}setter需要写入或更新的字段如:breed beagleconnection SQLite3::Database.open(app.db) upsert Upsert.new connection, :pets # 行存在 → 更新行不存在 → 插入 upsert.row({:name Jerry}, :breed beagle, :created_at Time.now)注意一个细节created_at和created_on列只在插入时写入更新时会被自动忽略由CREATED_COL_REGEX规则控制见lib/upsert.rb。upsert 支持 MySQL、PostgreSQL、SQLite3 三大数据库SQLite 为什么走 INSERT OR IGNORE 路线MySQL 有INSERT ... ON DUPLICATE KEY UPDATEPostgreSQL 9.5 有INSERT ... ON CONFLICT DO UPDATE但SQLite 两者都没有也不支持存储过程。upsert 的对策很务实用两条原生 SQL 模拟 MERGE官方在 README 的 SQL MERGE trick 一节有完整说明-- 第 1 步新行就插入旧行静默忽略不报错、不中断 INSERT OR IGNORE INTO pets (name, breed) VALUES (Jerry, beagle); -- 第 2 步按 selector 精确更新自动跳过 created_at UPDATE pets SET breed beagle WHERE name Jerry;这两条 SQL 就是 SQLite 版 upsert 的全部秘密对应源码lib/upsert/merge_function/sqlite3.rb中的execute方法。与 MySQL/PostgreSQL 不同SQLite 路径不需要创建任何数据库函数create!和clear!方法都是空实现零清理成本这也是它在轻量部署场景嵌入式、本地工具、CI 测试库中特别顺手的原因。唯一索引硬性要求为什么 OR IGNORE 会静默失效这是全文最重要的部分。INSERT OR IGNORE的 IGNORE 只在触发唯一约束冲突时才生效✅ selector 列有主键或唯一索引→ 重复插入被忽略第二步 UPDATE 生效 → 行为正确❌ selector 列没有任何唯一约束→ INSERT 永远成功重复行不断堆积UPDATE 可能误伤多行 →数据悄悄变脏且不报任何错误upsert 的 README 对此有明确警告Sqlite 章节只有当 selector 中至少有一列是主键或唯一索引时该方案才能正确工作。正确的建表姿势CREATE TABLE pets ( id INTEGER PRIMARY KEY, name VARCHAR(191) UNIQUE -- 关键selector 列必须唯一 ); -- 已有表补唯一索引 CREATE UNIQUE INDEX index_pets_on_name ON pets(name);作为对比各数据库对唯一性的依赖程度数据库upsert 机制唯一性要求MySQL透明创建存储过程不强制但建议有PostgreSQL 9.5原生ON CONFLICT必须有唯一约束注意普通唯一索引不算否则回退到 UDF 方案SQLite 3INSERT OR IGNORE UPDATE必须有主键或唯一索引否则重复数据 一个排查技巧如果你发现 SQLite 里同一 selector 出现多行记录先去PRAGMA index_list(表名)检查唯一索引是否存在。三步快速上手SQLite 连接与批量写入 本地跑起来只需三步需先gem install sqlite3# 1. 建立裸连接不需要 ActiveRecord connection SQLite3::Database.open(app.db) # 2. 单条 upsert upsert Upsert.new connection, :pets upsert.row({:name Jerry}, :breed beagle)数据量大时用批量模式官方测试显示Upsert.batch比常规模拟方式快约 80%Upsert.batch(connection, :pets) do |u| u.row({:name Jerry}, :breed beagle) u.row({:name Pierre}, :breed tabby) end如果项目已在用 Rails也可以直接传模型的连接Upsert.new Pet.connection, Pet.table_name甚至通过upsert/active_record_upsert获得Pet.upsert(...)的类方法语法。想要完整源码本地体验执行git clone https://gitcode.com/gh_mirrors/ups/upsertSQLite upsert 批量写入性能对比性能表现比 ActiveRecord 快 70%~90%upsert 在测试中强制要求必须比常规写法更快否则测试失败。SQLite 3 下的实测数据来自 README比find new/set/save快77%比find_or_create update_attributes快80%比create rescue/find/update快85%MySQL 场景下快 82%~90%PostgreSQL 场景下快 72%~83%。提速主要来自减少应用层往返、避免 N1 查询、批量模式下的语句复用。常见坑清单新手必读 踩坑自查表忘了建唯一索引→ 最常见的事故重复数据且不报错见上文硬性要求类型不转换→ upsert 不做任何类型转换往整型列塞空字符串在 PostgreSQL 上会直接报错SQLite 较宽容但生产仍建议统一类型时区→ 所有日期时间会立即转为 UTC 的 ISO8601 字符串用 MySQL 时确保服务端/连接时区是 UTC请求不存在的列→ 会抛出 invalid col 错误selector 和 setter 里的列名必须真实存在JRuby 环境→ SQLite 对应驱动是jdbc-sqlite3MRI 用sqlite3gem连接类是Java::OrgSqlite::Conn总结upsert让存在则更新、不存在则插入这套逻辑在三种数据库上有了统一且快速的答案MySQL/PostgreSQL 走透明 UDFSQLite 走INSERT OR IGNOREUPDATE两步组合。记住 SQLite 方案的一条铁律——selector 列必须有主键或唯一索引否则 OR IGNORE 形同虚设重复行会在无声无息中污染你的数据。核心源码位置供延伸阅读SQLite 合并逻辑lib/upsert/merge_function/sqlite3.rbSQLite 值绑定BigDecimal/布尔转换lib/upsert/connection/sqlite3.rb入口与批处理 APIlib/upsert.rb性能测试速度不达标即失败spec/speed_spec.rb正确性测试spec/correctness_spec.rb【免费下载链接】upsertUpsert on MySQL, PostgreSQL, and SQLite3. Transparently creates functions (UDF) for MySQL and PostgreSQL; on SQLite3, uses INSERT OR IGNORE.项目地址: https://gitcode.com/gh_mirrors/ups/upsert创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考