考虑下面的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 ;
现有的样本数据如下:
id name email
-- -------------- ----------------
1 Loblaw, Bob bob@example.com
2 Thompson, Gord gord@example.com
使用默认的连接设置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, "mike@example.com");
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);
}
产生以下控制台输出
executeUpdate returned 1
=> A new row was inserted: id=3
现在,再次使用参数值运行相同的代码
ps.setString(1, "Loblaw, Robert");
ps.setString(2, "bob@example.com");
控制台输出是
executeUpdate returned 2
=> An existing row was updated: id=1
这表明,.executeUpdate
如果唯一索引导致现有行被更新,则实际上可以返回2。如果您需要有关 实际 测试代码的进一步帮助,则应编辑问题以包括该问题。
进一步的测试表明,.executeUpdate
如果
可以通过使用完全相同的参数值连续两次运行上述测试代码来确认这一点。请注意,UPDATE ... id = LAST_INSERT_ID(id)
“技巧”确实可以确保id
返回正确的值。
如果唯一要插入的值是UNIQUE键值,这可能可以解释OP的测试结果。