英文:
HSQLDBs 'ON DUPLICATE KEY UPDATE'-feature does not behave as in MYSQL?
问题
我正在使用MYSQL的ON DUPLICATE KEY UPDATE特性，在MYSQL数据库上运行时效果符合预期。但是当我尝试针对内存中的HSQLDB编写运行测试时，我遇到了不同的行为。
给定以下表格：
CREATE TABLE foo（id INT主键NOT NULL，counter INT）;
使用以下插入语句：
INSERT INTO foo（id，counter）VALUES（1，1）ON DUPLICATE KEY UPDATE counter = counter + 1
- 第一次运行后，计数器为1。
- 第二次运行后，计数器为2。
- 第三次运行后，计数器仍为2。在这里，我预期计数器应该是3（如果我针对MYSQL运行相同的查询，计数器就是3）。
这是一个错误吗，还是我对ON DUPLICATE KEY UPDATE的工作原理有误解？
有关运行示例，请参阅以下GitHub存储库：https://github.com/mortenberg80/hsqldbtest
英文:
I am using MYSQLs ON DUPLICATE KEY UPDATE-feature, and it works as I expect when running on a MYSQL database. But when I try to write tests running against a in-memory HSQLDB, I experience different behaviour.
Given the following table:
CREATE TABLE foo (id INT PRIMARY KEY NOT NULL, counter INT);
With the following insert statement:
INSERT INTO foo(id, counter) VALUES (1, 1) ON DUPLICATE KEY UPDATE counter=counter+1
- After the first run, counter is 1.
- After the second run, counter is 2.
- After the third run, counter is still 2. Here I expected the counter to be 3. (It is 3 if I run the same queries against MYSQL).
Is this a bug, or have I misunderstood how ON DUPLICATE KEY UPDATE should work?
For a running example, see the following github-repository: https://github.com/mortenberg80/hsqldbtest
答案1
得分: 2
初始的HSQLDB ON DUPLICATE UPDATE 实现在所有col_name被遇到的情况下，将VALUES列表作为更新的源。从版本2.5.1开始，它已经进行了增强，将现有的表行值作为col_name的源，并将VALUES列表作为VALUES(col_name)的源。这与MySQL的用法相符。
英文:
The initial HSQLDB implementation of ON DUPLICATE UPDATE used the VALUES list as the source of the update in all cases where col_name was encounterd. It has been enhanced for version 2.5.1 to use the existing table row values as the source for col_name, and use the VALUES list as the source for VALUES(col_name). This corresponds with MySQL usage.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论