使用主键和唯一键重复密钥更新的Mysql

I have a table with an auto incremented primary key and also a unique key:

我有一个表,其中包含一个自动递增的主键和一个唯一键:

CREATE TABLE `product` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`canonical_url` varchar(750) CHARACTER SET latin1 NOT NULL,
...
PRIMARY KEY (`id`),
UNIQUE KEY `canonical_url_idx` (`canonical_url`)

Im using the on duplicate key feature to update records if the canonical_url already exists:

如果canonical_url已经存在,我使用on duplicate key功能更新记录:

"INSERT INTO product(id, canonical_url, name VALUES(?, ? ?) ON DUPLICATE KEY UPDATE name=VALUES(name), id=LAST_INSERT_ID(id)"

KeyHolder productKeyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(conn -> {
  PreparedStatement ps = conn.prepareStatement(productSql, new String[] {"id"});
  ps.setInt(1, id);
  ps.setString(2, canonicalUrl);
  ps.setString(3, name);
}, productKeyHolder);

final int productId = productKeyHolder.getKey().intValue();

The problem is that I'm getting this error:

问题是我收到了这个错误:

The getKey method should only be used when a single key is returned. The current key entry contains multiple keys: [{GENERATED_KEY=594}, {GENERATED_KEY=595}]

只有在返回单个键时才应使用getKey方法。当前键条目包含多个键:[{GENERATED_KEY = 594},{GENERATED_KEY = 595}]

Does anyone know what is causing this?

有谁知道是什么原因造成的?

1 个解决方案

#1

I just ran into this myself. According to the documentation here:

我自己就碰到了这个。根据这里的文件:

https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

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。

So when your query executes, if a new record is inserted it's ID is returned. If the record already exists then the existing record is updated. If no update is needed because the values all match then an ID is returned and the number of rows modified is 0. However, if the record is updated, the ID is returned and the number of rows modified is 2. The keyholder is assuming two rows have been modified (even though only one has been) and is incorrectly returning the ID plus the next sequential ID (i.e. the ID plus 1).

因此,当您的查询执行时,如果插入了新记录,则返回其ID。如果记录已存在,则更新现有记录。如果不需要更新,因为值全部匹配,则返回ID并且修改的行数为0.但是,如果更新记录,则返回ID并修改行数为2.密钥持有者假定为2行已被修改(即使只有一行)并且错误地返回ID加上下一个顺序ID(即ID加1)。

To work around this I just checked the count in the getKeys before attempting to call getKey. If there is more than one value in getKeys I won't call getKey.

为了解决这个问题,我在尝试调用getKey之前只检查了getKeys中的计数。如果getKeys中有多个值,我将不会调用getKey。

#1