HSQLDB的ON DUPLICATE KEY UPDATE与MySQL行为不一致问题问询

嘿，我之前也碰过这个坑，先给你吃个定心丸——这不是你对特性的理解有误，而是HSQLDB对MySQL兼容语法的实现和MySQL本身存在关键差异，算不上bug，就是俩数据库的逻辑不一样。

为啥会出现第三次还是2的情况？ #

在MySQL里，每次执行INSERT ... ON DUPLICATE KEY UPDATE时，counter=counter+1里的counter永远取的是当前数据库行里的最新值：第一次插入后counter是1，第二次触发更新变成2，第三次更新时就基于2再加1得到3，完全符合预期。

但HSQLDB的实现逻辑有个特殊点：它会缓存你第一次执行INSERT时VALUES子句里的原始值（也就是这里的counter=1）。后续重复执行同一条语句时，UPDATE子句里的counter+1并不是取当前行的实际值，而是用第一次缓存的1来计算。所以第二次执行得到1+1=2，第三次还是1+1=2，自然就和MySQL的结果不一样了。

怎么解决这个问题？ #

如果想在HSQLDB里实现和MySQL一致的自增逻辑，有两个靠谱的办法：

• 改用SQL标准的MERGE语句：这是跨数据库兼容性最好的方案，不管是HSQLDB还是MySQL都支持，而且行为完全一致：

  MERGE INTO foo t
  USING (SELECT 1 AS id, 1 AS counter FROM dual) s
  ON t.id = s.id
  WHEN MATCHED THEN UPDATE SET t.counter = t.counter + 1
  WHEN NOT MATCHED THEN INSERT (id, counter) VALUES (s.id, s.counter);
  

  每次执行这个语句时，都会先匹配id，匹配上就用当前行的counter加1，没匹配上就插入，完全不会有缓存值的问题。

• 避免复用预编译语句的固定值：如果你非要用ON DUPLICATE KEY UPDATE，可以每次执行时动态生成VALUES里的counter值，比如用子查询获取当前值再加1（不过这种写法不如MERGE简洁）：

  INSERT INTO foo(id, counter) 
  VALUES (1, (SELECT COALESCE(counter, 0) + 1 FROM foo WHERE id=1)) 
  ON DUPLICATE KEY UPDATE counter=counter+1;
  

总结一下 #

这属于HSQLDB对MySQL兼容语法的实现局限，不是bug，也不是你理解错了。如果是做单元测试需要和MySQL行为对齐，优先用MERGE语句，能省不少兼容性的麻烦。

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