为什么 upsert 拒绝 ON DUPLICATE KEY UPDATE三大数据库 Upsert 实现方案终极对比【免费下载链接】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️upsertGitHub 加速计划 / ups / upsert是一个 Ruby 库让你在MySQL、PostgreSQL、SQLite3三大数据库上用统一的 API 执行 Upsert插入或更新操作。它比用 ActiveRecord 模拟 upsert 快 70%–90%而且不依赖任何 ORM。很多新手都会问MySQL 不是有现成的ON DUPLICATE KEY UPDATE吗为什么这个项目偏偏不用今天我们就把这个反直觉的设计决策讲透并对比三大数据库的 Upsert 实现方案。Upsert 到底解决什么问题想象一个场景你有一张宠物登记表pets想记录每只宠物的品种——如果这只宠物已存在→ 更新它的breed字段如果这只宠物不存在→ 新建一行传统做法要先查询、再判断、然后插入或更新至少两次数据库往返还容易并发出错。upsert 库把这件事压缩成一次调用核心就是两个概念selector选择器唯一定位一行的字段比如{ name: Jerry }setter设置器要写入的字段比如{ breed: beagle }upsert Upsert.new connection, :pets upsert.row({ name: Jerry }, { breed: beagle })就这么简单。但怎么做到的三大数据库给出的答案完全不同。为什么拒绝 ON DUPLICATE KEY UPDATE这是本文的重点 ⚠️MySQL 的INSERT ... ON DUPLICATE KEY UPDATE看起来是 Upsert 的标准答案但 upsert 库的 README 明确写着不使用INSERT ON DUPLICATE KEY UPDATE因为它只有在你极其小心地创建唯一索引时才可靠。问题出在哪依赖唯一索引ODKU 的冲突检测完全靠唯一索引/唯一约束触发。如果你的 selector 字段比如name上没有唯一索引冲突根本检测不到——静默插入重复行没有唯一约束时ODKU 会默默插入一条看起来像重复的新记录数据悄悄变脏没有任何报错。对业务来说这种静默失败比报错更可怕。可移植性差PostgreSQL 的ON CONFLICT同样要求唯一约束SQLite3 则根本没有这个语法。一条写死 ODKU 的代码无法跨库运行。所以 upsert 库选择了一条不依赖任何唯一约束的路用数据库侧的函数/存储过程显式地 SELECT → UPDATE 或 INSERT把控制权拿回自己手里。三大数据库 Upsert 实现方案终极对比下面这张表就是全文的精华 维度MySQLPostgreSQLSQLite3实现方式存储过程Stored Procedureplpgsql 函数9.5 自动切换原生语法两条 SQL无需建函数核心语法CREATE PROCEDURE 异常处理器CREATE OR REPLACE FUNCTIONEXCEPTIONINSERT OR IGNOREUPDATE需要唯一约束❌ 不需要❌ 函数方案不需要原生ON CONFLICT需要⚠️ 建议 selector 是主键/唯一索引并发安全机制异常处理器捕获 23000/1062 错误自动重试捕获unique_violation只重试一次OR IGNORE天然忽略冲突函数创建时机首次 upsert 时透明创建并缓存同左且函数丢失时自动重建无需创建源码位置lib/upsert/merge_function/mysql.rblib/upsert/merge_function/postgresql.rblib/upsert/merge_function/sqlite3.rbMySQL 方案存储过程 异常重试MySQL 的实现见lib/upsert/merge_function/mysql.rb会在你第一次 upsert 时透明创建一个存储过程逻辑分三步SELECT COUNT(*)检查 selector 是否命中已有行命中 → 执行UPDATE未命中 → 执行INSERT用REPEAT ... UNTIL循环包裹整个流程并通过异常处理器ER_DUP_UNIQUE23000 /ER_INTEG1062捕获并发写入导致的唯一键冲突自动回退重试一次这正是对前面 SELECT 与 INSERT 之间竞态窗口的兜底就算两个进程同时发现行不存在后到的一方也不会报错而是被异常处理器接住转入更新流程。✨ 好处全程不依赖唯一索引即使你的表只有普通索引行为也完全正确。PostgreSQL 方案能原生则原生否则降级函数PostgreSQL 是最聪明的实现见lib/upsert/merge_function/postgresql.rb它有两条路径PG 9.5 且 selector 上有唯一约束→ 直接用原生的INSERT ... ON CONFLICT (...) DO UPDATE SET ...性能最优其他情况→ 自动降级到经典的 plpgsql 函数方案函数方案改编自 PostgreSQL 官方文档的规范示例逻辑是先更新再插入先UPDATE若found为真则直接返回否则INSERT若撞上unique_violation异常说明有并发插入只重试一次——作者特意加了仅重试一次的保险避免极端场景下的死循环⚠️ 一个容易踩的坑想走原生ON CONFLICT路径selector 上必须是唯一约束unique constraint普通的唯一索引不行。不满足时库会打印一条 WARNING 并自动降级代码无需修改。SQLite3 方案最简单的 INSERT OR IGNORESQLite3 没有存储过程upsert 库也没必要造——直接用两条 SQL 就搞定见lib/upsert/merge_function/sqlite3.rbINSERT OR IGNORE INTO visits (...) VALUES (...); UPDATE visits SET visits ... WHERE ip ...;第一条OR IGNORE保证主键/唯一键冲突时静默跳过插入第二条无条件更新把值写到对应行上一插一更两不冲突。注意SQLite3 方案要求 selector 中至少有一列是主键或唯一索引否则OR IGNORE拦不住重复行。 有意思的是created_at/created_on列在三种方案里都有特殊待遇只在插入时写入更新时忽略——保证创建时间永远是最初的那一次。性能实测快了多少upsert 库的测试套件spec/speed_spec.rb不只是测正确性不够快测试直接会失败。官方基准数据对比方式MySQLPostgreSQLSQLite3find 新建/保存快 82%快 72%快 77%find_or_create_by 更新快 85%快 79%快 80%create 捕获异常回退更新快 90%快 83%快 85%批量场景还有专门的Batch 模式Upsert.batch官方实测比其他方式模拟 upsert 快约 80%——适合一次性灌入大量记录。上手指南三步接入 upsert以 MySQL 为例其他数据库只是换一下连接对象 第 1 步安装。在 Gemfile 中加入gem upsert然后bundle install。若从源码安装git clone https://gitcode.com/gh_mirrors/ups/upsert cd upsert bundle install第 2 步建连接。你只需要一个裸的数据库连接Mysql2::Client/PG::Connection/SQLite3::Database不需要ActiveRecord 参与。第 3 步调用。单行用upsert.row(selector, setter)批量用Upsert.batch块。如果用的是 Rails一行require upsert/active_record_upsert后就能直接Pet.upsert(...)详见lib/upsert/active_record_upsert.rb。新手避坑清单 ️没有自动类型转换往整型字段 upsert 空字符串PostgreSQL 会直接报错库不替你猜测类型。时间全部转 UTC日期时间以 ISO8601 字符串入库请确保 MySQL 服务端/连接时区也是 UTC。清理遗留函数MySQL/PostgreSQL 上自动创建的函数前缀为upsert版本号可用Upsert.clear_database_functions(connection)一键清掉。与事务型测试夹具不兼容Rails 的 transactional fixtures 下可能有问题测试时留意。总结为什么拒绝反而是高级upsert 库的选择其实是一套清晰的设计哲学不赌数据库的隐式行为——唯一索引缺失时 ODKU 静默出错不如显式控制流程用存储过程/函数把重试逻辑下沉到数据库侧——一次往返、天然原子、无 ORM 开销能原生则原生——PostgreSQL 9.5 检测到唯一约束就切到ON CONFLICT代码零改动统一 API——selector setter 的心智模型来自 MongoDB 的 update 语义三种数据库写同一套代码一句话ON DUPLICATE KEY UPDATE是把安全性托付给你的索引建对了而 upsert 库是把安全性托付给可验证的逻辑。对新手来说记住这张三大方案对比表再根据自己的数据库版本和索引情况选型就能少走大部分弯路。 【免费下载链接】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），仅供参考 为什么 upsert 拒绝 ON DUPLICATE KEY UPDATE？三大数据库 Upsert 实现方案终极对比
为什么 upsert 拒绝 ON DUPLICATE KEY UPDATE三大数据库 Upsert 实现方案终极对比【免费下载链接】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️upsertGitHub 加速计划 / ups / upsert是一个 Ruby 库让你在MySQL、PostgreSQL、SQLite3三大数据库上用统一的 API 执行 Upsert插入或更新操作。它比用 ActiveRecord 模拟 upsert 快 70%–90%而且不依赖任何 ORM。很多新手都会问MySQL 不是有现成的ON DUPLICATE KEY UPDATE吗为什么这个项目偏偏不用今天我们就把这个反直觉的设计决策讲透并对比三大数据库的 Upsert 实现方案。Upsert 到底解决什么问题想象一个场景你有一张宠物登记表pets想记录每只宠物的品种——如果这只宠物已存在→ 更新它的breed字段如果这只宠物不存在→ 新建一行传统做法要先查询、再判断、然后插入或更新至少两次数据库往返还容易并发出错。upsert 库把这件事压缩成一次调用核心就是两个概念selector选择器唯一定位一行的字段比如{ name: Jerry }setter设置器要写入的字段比如{ breed: beagle }upsert Upsert.new connection, :pets upsert.row({ name: Jerry }, { breed: beagle })就这么简单。但怎么做到的三大数据库给出的答案完全不同。为什么拒绝 ON DUPLICATE KEY UPDATE这是本文的重点 ⚠️MySQL 的INSERT ... ON DUPLICATE KEY UPDATE看起来是 Upsert 的标准答案但 upsert 库的 README 明确写着不使用INSERT ON DUPLICATE KEY UPDATE因为它只有在你极其小心地创建唯一索引时才可靠。问题出在哪依赖唯一索引ODKU 的冲突检测完全靠唯一索引/唯一约束触发。如果你的 selector 字段比如name上没有唯一索引冲突根本检测不到——静默插入重复行没有唯一约束时ODKU 会默默插入一条看起来像重复的新记录数据悄悄变脏没有任何报错。对业务来说这种静默失败比报错更可怕。可移植性差PostgreSQL 的ON CONFLICT同样要求唯一约束SQLite3 则根本没有这个语法。一条写死 ODKU 的代码无法跨库运行。所以 upsert 库选择了一条不依赖任何唯一约束的路用数据库侧的函数/存储过程显式地 SELECT → UPDATE 或 INSERT把控制权拿回自己手里。三大数据库 Upsert 实现方案终极对比下面这张表就是全文的精华 维度MySQLPostgreSQLSQLite3实现方式存储过程Stored Procedureplpgsql 函数9.5 自动切换原生语法两条 SQL无需建函数核心语法CREATE PROCEDURE 异常处理器CREATE OR REPLACE FUNCTIONEXCEPTIONINSERT OR IGNOREUPDATE需要唯一约束❌ 不需要❌ 函数方案不需要原生ON CONFLICT需要⚠️ 建议 selector 是主键/唯一索引并发安全机制异常处理器捕获 23000/1062 错误自动重试捕获unique_violation只重试一次OR IGNORE天然忽略冲突函数创建时机首次 upsert 时透明创建并缓存同左且函数丢失时自动重建无需创建源码位置lib/upsert/merge_function/mysql.rblib/upsert/merge_function/postgresql.rblib/upsert/merge_function/sqlite3.rbMySQL 方案存储过程 异常重试MySQL 的实现见lib/upsert/merge_function/mysql.rb会在你第一次 upsert 时透明创建一个存储过程逻辑分三步SELECT COUNT(*)检查 selector 是否命中已有行命中 → 执行UPDATE未命中 → 执行INSERT用REPEAT ... UNTIL循环包裹整个流程并通过异常处理器ER_DUP_UNIQUE23000 /ER_INTEG1062捕获并发写入导致的唯一键冲突自动回退重试一次这正是对前面 SELECT 与 INSERT 之间竞态窗口的兜底就算两个进程同时发现行不存在后到的一方也不会报错而是被异常处理器接住转入更新流程。✨ 好处全程不依赖唯一索引即使你的表只有普通索引行为也完全正确。PostgreSQL 方案能原生则原生否则降级函数PostgreSQL 是最聪明的实现见lib/upsert/merge_function/postgresql.rb它有两条路径PG 9.5 且 selector 上有唯一约束→ 直接用原生的INSERT ... ON CONFLICT (...) DO UPDATE SET ...性能最优其他情况→ 自动降级到经典的 plpgsql 函数方案函数方案改编自 PostgreSQL 官方文档的规范示例逻辑是先更新再插入先UPDATE若found为真则直接返回否则INSERT若撞上unique_violation异常说明有并发插入只重试一次——作者特意加了仅重试一次的保险避免极端场景下的死循环⚠️ 一个容易踩的坑想走原生ON CONFLICT路径selector 上必须是唯一约束unique constraint普通的唯一索引不行。不满足时库会打印一条 WARNING 并自动降级代码无需修改。SQLite3 方案最简单的 INSERT OR IGNORESQLite3 没有存储过程upsert 库也没必要造——直接用两条 SQL 就搞定见lib/upsert/merge_function/sqlite3.rbINSERT OR IGNORE INTO visits (...) VALUES (...); UPDATE visits SET visits ... WHERE ip ...;第一条OR IGNORE保证主键/唯一键冲突时静默跳过插入第二条无条件更新把值写到对应行上一插一更两不冲突。注意SQLite3 方案要求 selector 中至少有一列是主键或唯一索引否则OR IGNORE拦不住重复行。 有意思的是created_at/created_on列在三种方案里都有特殊待遇只在插入时写入更新时忽略——保证创建时间永远是最初的那一次。性能实测快了多少upsert 库的测试套件spec/speed_spec.rb不只是测正确性不够快测试直接会失败。官方基准数据对比方式MySQLPostgreSQLSQLite3find 新建/保存快 82%快 72%快 77%find_or_create_by 更新快 85%快 79%快 80%create 捕获异常回退更新快 90%快 83%快 85%批量场景还有专门的Batch 模式Upsert.batch官方实测比其他方式模拟 upsert 快约 80%——适合一次性灌入大量记录。上手指南三步接入 upsert以 MySQL 为例其他数据库只是换一下连接对象 第 1 步安装。在 Gemfile 中加入gem upsert然后bundle install。若从源码安装git clone https://gitcode.com/gh_mirrors/ups/upsert cd upsert bundle install第 2 步建连接。你只需要一个裸的数据库连接Mysql2::Client/PG::Connection/SQLite3::Database不需要ActiveRecord 参与。第 3 步调用。单行用upsert.row(selector, setter)批量用Upsert.batch块。如果用的是 Rails一行require upsert/active_record_upsert后就能直接Pet.upsert(...)详见lib/upsert/active_record_upsert.rb。新手避坑清单 ️没有自动类型转换往整型字段 upsert 空字符串PostgreSQL 会直接报错库不替你猜测类型。时间全部转 UTC日期时间以 ISO8601 字符串入库请确保 MySQL 服务端/连接时区也是 UTC。清理遗留函数MySQL/PostgreSQL 上自动创建的函数前缀为upsert版本号可用Upsert.clear_database_functions(connection)一键清掉。与事务型测试夹具不兼容Rails 的 transactional fixtures 下可能有问题测试时留意。总结为什么拒绝反而是高级upsert 库的选择其实是一套清晰的设计哲学不赌数据库的隐式行为——唯一索引缺失时 ODKU 静默出错不如显式控制流程用存储过程/函数把重试逻辑下沉到数据库侧——一次往返、天然原子、无 ORM 开销能原生则原生——PostgreSQL 9.5 检测到唯一约束就切到ON CONFLICT代码零改动统一 API——selector setter 的心智模型来自 MongoDB 的 update 语义三种数据库写同一套代码一句话ON DUPLICATE KEY UPDATE是把安全性托付给你的索引建对了而 upsert 库是把安全性托付给可验证的逻辑。对新手来说记住这张三大方案对比表再根据自己的数据库版本和索引情况选型就能少走大部分弯路。 【免费下载链接】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），仅供参考