将MySQL Upsert迁移至Cloud Spanner遇唯一键问题求建议

问题背景 #

MySQL中支持基于唯一约束的ON DUPLICATE KEY UPDATE，比如针对ssn这个唯一键，即使主键id不同，插入时也会触发更新：

CREATE TABLE persons (
    id int PRIMARY KEY,
    name varchar(45),
    ssn varchar(15),
    CONSTRAINT uc_ssn Unique(ssn)
);

INSERT INTO persons (id,name,ssn)
VALUES (1,'a', '111-111-1111')
ON DUPLICATE KEY UPDATE name=name;

# 此插入会因ssn唯一约束触发更新
INSERT INTO persons (id,name,ssn)
VALUES (2,'b', '111-111-1111')
ON DUPLICATE KEY UPDATE name=name;

但Cloud Spanner的Mutation.newInsertOrUpdateBuilder仅基于主键判断是否重复，上述场景中因为id不同，Spanner会视为新记录插入，触发二级唯一键约束报错，无法实现MySQL的逻辑。

方案分析与建议 #

方案1：用唯一键的SHA256值作为主键实现Upsert #

• 实现方式：将原表的主键id替换为ssn的SHA256哈希值（可存储为字符串或字节类型），之后直接使用Spanner的原生Upsert操作。
• 优点：
  • 实现简单，完全利用Spanner的原生Upsert能力，无需额外逻辑。
  • 性能最优，单Mutation操作即可完成，无额外查询开销。
• 风险：SHA256的冲突概率极低（2^256的取值空间），业务场景下几乎不可能出现，可忽略不计。若id原有业务含义，可将其保留为普通列而非主键。

方案2：先查询再执行更新/插入 #

• 实现方式：在只读一致性事务中查询ssn对应的记录：
  1. 若记录存在，执行更新操作；
  2. 若记录不存在，执行插入操作。
• 注意事项：
  • 存在竞态条件：并发场景下，两个请求可能同时查询到记录不存在，进而同时触发插入，导致唯一键冲突。需在代码中捕获冲突异常并重试更新逻辑。
  • 性能损耗：多了一次查询步骤，若使用读写事务保证原子性，延迟和吞吐量会进一步下降，高并发场景下影响更明显。

最终建议 #

• 如果ssn是业务核心唯一标识，且原id无强业务依赖，优先选择方案1，兼顾实现复杂度和性能。
• 若必须保留原id作为主键，只能选择方案2，但需做好并发冲突的重试处理，同时接受一定的性能损耗。

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