Java PreparedStatement 和 ON DUPLICATE KEY UPDATE：我怎么知道行是插入还是更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22743497/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java PreparedStatement and ON DUPLICATE KEY UPDATE: how do I know whether row was inserted or updated?
提问by ed22
Having following code, how do I know if the execute() method resulted in insert or in update?:
有了以下代码，我怎么知道 execute() 方法导致插入还是更新？：
Connection c = DriverManager.getConnection(connectionString);
PreparedStatement st = c.prepareStatement("INSERT INTO `table`(`field1`) VALUES (?) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);");
st.setString(1,"some value");
st.execute();
Thanks in advance.
提前致谢。
采纳答案by Gord Thompson
Consider the following MySQL test table:
考虑以下 MySQL 测试表：
CREATE TABLE `customers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
with existing sample data as follows:
现有样本数据如下：
id name email
-- -------------- ----------------
1 Loblaw, Bob [email protected]
2 Thompson, Gord [email protected]
With the default connection setting compensateOnDuplicateKeyUpdateCounts=false(described here) the following Java code
使用默认连接设置compensateOnDuplicateKeyUpdateCounts=false（此处描述）以下 Java 代码
PreparedStatement ps = dbConnection.prepareStatement(
"INSERT INTO customers (name, email) " +
"VALUES (?, ?) " +
"ON DUPLICATE KEY UPDATE " +
"name = VALUES(name), " +
"id = LAST_INSERT_ID(id)");
ps.setString(1, "McMack, Mike");
ps.setString(2, "[email protected]");
int euReturnValue = ps.executeUpdate();
System.out.printf("executeUpdate returned %d%n", euReturnValue);
Statement s = dbConnection.createStatement();
ResultSet rs = s.executeQuery("SELECT LAST_INSERT_ID() AS n");
rs.next();
int affectedId = rs.getInt(1);
if (euReturnValue == 1) {
System.out.printf(" => A new row was inserted: id=%d%n", affectedId);
}
else {
System.out.printf(" => An existing row was updated: id=%d%n", affectedId);
}
produces the following console output
产生以下控制台输出
executeUpdate returned 1
=> A new row was inserted: id=3
Now run the same code again with the parameter values
现在使用参数值再次运行相同的代码
ps.setString(1, "Loblaw, Robert");
ps.setString(2, "[email protected]");
and the console output is
控制台输出是
executeUpdate returned 2
=> An existing row was updated: id=1
This demonstrates that .executeUpdatereally can return 2 if the unique index causes an existing row to be updated. If you require further assistance with your actualtest code then you should edit your question to include it.
这表明.executeUpdate如果唯一索引导致更新现有行，则确实可以返回 2。如果您需要有关实际测试代码的进一步帮助，那么您应该编辑您的问题以包含它。
Edit
编辑
Further testing reveals that .executeUpdatewill return 1 if
进一步的测试表明，.executeUpdate如果
- the attempted INSERT is aborted because it would result in a duplicate UNIQUE key value, and
- the specified ON DUPLICATE KEY UPDATE changes do not actually modify any values in the existing row.
- 尝试的 INSERT 被中止，因为它会导致重复的 UNIQUE 键值，并且
- 指定的 ON DUPLICATE KEY UPDATE 更改实际上不会修改现有行中的任何值。
This can be confirmed by running the above test code twice in a row with the exact same parameter values. Note that the UPDATE ... id = LAST_INSERT_ID(id)"trick" does ensure that the correct idvalue is returned.
这可以通过使用完全相同的参数值连续两次运行上述测试代码来确认。请注意，UPDATE ... id = LAST_INSERT_ID(id)“技巧”确实确保id返回正确的值。
That probably explains OP's test results if the only value being inserted was the UNIQUE key value.
如果插入的唯一值是 UNIQUE 键值，这可能解释了 OP 的测试结果。
回答by Drew MacInnis
Use executeUpdateinstead as it returns an introw count.
请改用executeUpdate，因为它返回int行数。
UPDATE 1: According to the MySQL INSERT ... ON DUPLICATE KEY UPDATE documentation:
更新 1：根据MySQL INSERT ... ON DUPLICATE KEY UPDATE 文档：
With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, and 2 if an existing row is updated.
使用 ON DUPLICATE KEY UPDATE，如果将行作为新行插入，每行的受影响行值为 1，如果更新现有行，则为 2。
UPDATE 2: INSERT IGNOREmay also be an option:
更新 2：INSERT IGNORE也可能是一个选项：
INSERT IGNORE INTO `table`(`field1`) VALUES (?)
executeUpdateshould return 1 when a new row is inserted and 0 when there is a duplicate.
executeUpdate插入新行时应返回 1，重复时应返回 0。
