MySQL“INSERT ... ON DUPLICATE KEY UPDATE” on Java：如何区分插入/更新/ NoChange状态 [英] MySQL "INSERT ... ON DUPLICATE KEY UPDATE" on Java: How to Differ Inserted/Updated/NoChange states

问题描述

我对MySQL的的返回值感到困惑INSERT ... ON DUPLICATE KEY UPDATE 语句。当我在MySQL客户端（mysql终端，phpmyadmin或MySQL Workbench）上尝试时，执行结果如下：

• 1 ：如果插入新记录（即没有重复密钥）。


• 2 ：如果重复密钥，则更新现有记录。


• 0 ：如果执行了更新但没有更改列值。

<这些结果是有道理的。但是，当我在Java中执行相同的查询（使用mysql-connector 5.1.34）时，JDBC的 executeUpdate 方法返回 1 如果插入且未更改，则 2 以便成功更新。当更新无效时，它不会返回0.

这是MySQL JDBC驱动程序的错误吗？如果是这样，是否有一个没有这个bug的版本？如果这不是错误，我如何得到MySQL客户端返回的相同结果？

解决方案

似乎你需要手动设置驱动程序属性中的 useAffectedRows 。

我在google搜索后得到了解决方案。如果JDBC ExecuteUpdate没有变化，您可以参考此MySql Bug来获取此 INSERT ON DUPLICATE KEY UPDATE返回0

同样在讨论中声明他们在5.1.7中发布了

EDIT For您的第二个问题：

useAffectedRows ：

不要设置CLIENT_FOUND_ROWS标志连接到服务器时（不符合JDBC标准，将破坏大多数依赖于找到行和DML语句的受影响行的应用程序），但确实会导致INSERT ... ON DUPLICATE KEY中的正确更新计数UPDATE服务器返回的语句。

默认值：false

版本：5.1.7

Resference useAffectedRows

I'm confused about the return value of MySQL's INSERT ... ON DUPLICATE KEY UPDATE statement. When I try it on a MySQL client (mysql terminal, phpmyadmin or MySQL Workbench), the execution results with one of the followings:

• 1: If new record inserted (i.e. no duplicate key).
• 2: If existing record updated in case of duplicate key.
• 0: If update executed but no column value was changed.

These results make sense. However, when I executed the same query in Java (with mysql-connector 5.1.34), JDBC's executeUpdate method returns 1 in case of both insertion and no change, and 2 in case of an successful update. It doesn't return 0 when update has no effect.

Is this a bug of MySQL JDBC driver? If so, is there a release which is free of this bug? If this is not a bug, how can I get the same result that is returned by a MySQL client?

解决方案

Seems you need to set useAffectedRows in driver properties manually.

I got the solution after some googling. You can refer this MySql Bug for this INSERT ON DUPLICATE KEY UPDATE return 0 if no change in JDBC ExecuteUpdate

Also in the discussion it is stated they released it in 5.1.7

EDIT For Your Second Question:

useAffectedRows:

Don't set the CLIENT_FOUND_ROWS flag when connecting to the server (not JDBC-compliant, will break most applications that rely on "found" rows vs. "affected rows" for DML statements), but does cause "correct" update counts from "INSERT ... ON DUPLICATE KEY UPDATE" statements to be returned by the server.

Default: false

Since version: 5.1.7

Resference useAffectedRows in JDBC Connector-j

这篇关于MySQL“INSERT ... ON DUPLICATE KEY UPDATE” on Java：如何区分插入/更新/ NoChange状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！