upsert 踩坑清单：时区 UTC 转换、类型强校验、事务夹具冲突等 5 个新手常见的 Upsert 陷阱

upsert 踩坑清单：时区 UTC 转换、类型强校验、事务夹具冲突等 5 个新手常见的 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

upsert 是一款面向 Ruby 的数据库 Upsert（插入或更新）库，支持 MySQL、PostgreSQL 和 SQLite3，通过在数据库里透明地创建存储函数（UDF）来实现高性能的 "insert or update"。它比用 ActiveRecord 模拟 Upsert 快 70%–90%，且不强依赖 ActiveRecord。但在实际使用 lib/upsert.rb 时，新手很容易踩到一些"文档里没大声说"的坑。这篇清单帮你提前排雷。⚠️

坑 1：时区 UTC 转换 —— 写库的时间被悄悄转成 UTC

upsert 会把所有日期时间立即转换成 UTC，并以 ISO8601 字符串发送到数据库。如果你只传了本地时间（比如北京时间），存进去之后读出来可能会差 8 个小时。❗

如何自查：

• 用 MySQL 时，确认服务端与连接时区都是 UTC；
• 用 Rails / ActiveRecord 时，检查ActiveRecord::Base.default_timezone是否为:utc（项目的测试配置 spec/spec_helper.rb 就显式设置为:utc）；
• 官方建议：先跑几次 upsert，确认时间落库符合预期（参考 spec/timezones_spec.rb 中的用例）。

💡 经验法则：全链路统一 UTC，只在展示层做本地化。

坑 2：类型强校验 —— upsert 不做任何自动类型转换

upsert没有任何类型转换逻辑（no automatic typecasting）。往 PostgreSQL 的整型列写一个空字符串''，会直接报invalid input syntax for integer之类的错误（详见 spec/type_safety_spec.rb 中的断言）。

新手常见误区：以为数据库/驱动会帮你把'123'变成整数、''变成NULL。并不会。表单传来的值都是字符串，务必在调用upsert.row之前自己做好类型清洗（整数列的空值用nil而不是''）。

坑 3：事务夹具冲突 —— 测试里莫名"函数不存在"

upsert 依赖它动态创建的数据库函数来实现合并逻辑（例如 lib/upsert/merge_function/postgresql.rb 中的实现）。如果你使用 Rails 的 transactional fixtures（测试数据放在事务里、结束后回滚），函数创建也会被一并回滚，后续用例可能突然报 "function does not exist"，或者出现数据污染等诡异问题。

解决方案：测试环境改用非事务的数据清理策略（如 truncate），或关闭use_transactional_fixtures，单独处理测试数据。

坑 4：PostgreSQL 9.5+ 原生 upsert 只认"唯一约束"

在 Postgres 9.5+ 且检测到 selector 列上有**唯一约束（unique constraint）**时，upsert 会走原生的INSERT ... ON CONFLICT DO UPDATE，性能最好。

⚠️ 注意：只有唯一索引（unique index）不算数！如果你的列上只建了CREATE UNIQUE INDEX而没有约束，upsert 会静默回退到经典的 UDF 方案——功能正常，但性能不是最优。写迁移时建议用add_index ... unique: true+add_unique_constraint（或等价写法）来保证约束存在。

坑 5：SQLite3 的 selector 必须包含主键或唯一索引

SQLite3 上没有存储函数，upsert 采用INSERT OR IGNORE + UPDATE的两步模拟（见 lib/upsert/merge_function/sqlite3.rb）。这个方案依赖唯一性检查来拦截重复插入：如果 selector 列既不是主键也没有唯一索引，"忽略重复插入"就会失效，可能插入出重复行。

自查方法：确认你的 selector 至少有一个列是主键或带唯一索引。

附赠：3 个容易忽略的小细节 📌

小结：上手前的 5 项检查清单 ✅

1. 时区：数据库、连接、Ruby 应用三方统一 UTC；
2. 类型：字符串表单值先转换，别让''流向整型列；
3. 测试：避开 transactional fixtures 与 UDF 的冲突；
4. Postgres：selector 列上建唯一约束，别只建唯一索引；
5. SQLite3：selector 必须含主键或唯一索引。

把这 5 个坑提前排掉，upsert 的 70%–90% 性能收益就能稳稳拿到手。更多原理与用法见 README.md。

【免费下载链接】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