1. 问题描述
使用 insert … on duplicate key update
语法实现 insertOrUpdate
之后出现了几个新问题,首先给出测试代码:
DROP database if EXISTS test;
CREATE database test;
DROP TABLE if EXISTS test.person;
CREATE table test.person (
id int not NULL PRIMARY KEY auto_increment,
name VARCHAR(100) not NULL DEFAULT '' UNIQUE COMMENT '名字',
age int not NULL DEFAULT 0 COMMENT '年龄',
gender VARCHAR(20) NOT NULL DEFAULT '' COMMENT '性别',
addresses text NOT NULL COMMENT '地址'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='person';
MyBatis 语句:
<insert id="insertOrUpdate" useGeneratedKeys="true" keyProperty="id">
insert into test.person
(id, name, age, gender, addresses)
VALUES
<foreach collection="list" item="person" separator=",">
(id, #{person.name}, #{person.age}, #{person.gender},
#{person.addresses, typeHandler=com.note4code.test.persistence.typeHandler.GenericMapHandler})
</foreach>
on duplicate key update
age = VALUES(age),
gender = VALUES(gender),
addresses = VALUES(addresses)
</insert>
Java 代码:
package com.note4code.test.service;
import com.note4code.test.domain.Address;
import com.note4code.test.domain.Gender;
import com.note4code.test.domain.Person;
import com.note4code.test.domain.Province;
import junit.framework.TestCase;
import org.assertj.core.util.Lists;
import org.assertj.core.util.Maps;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.IOException;
import java.util.List;
import java.util.Map;
@RunWith(SpringRunner.class)
@SpringBootTest
public class PersonServiceTest extends TestCase{
@Autowired
private PersonService personService;
private Person me;
private Person you;
private Person him;
@Before
public void initData() {
Address address = new Address(Province.BEIJING, "北京", "学院路");
Map<Province, Address> map = Maps.newHashMap(Province.BEIJING, address);
this.me = new Person();
this.me.setName("me");
this.me.setAge(27);
this.me.setGender(Gender.MALE);
this.me.setAddresses(map);
this.you = new Person();
this.you.setName("you");
this.you.setAge(25);
this.you.setGender(Gender.FEMALE);
this.you.setAddresses(map);
this.him = new Person();
this.him.setName("him");
this.him.setAge(25);
this.him.setGender(Gender.MALE);
this.him.setAddresses(map);
}
@Test
public void testForOnDuplicateKey() {
personService.addPerson(me);
int id = me.getId();
me.setAge(28);
List<Person> people = Lists.newArrayList(me, you, him);
personService.addOrUpdate(people);
assertTrue(id != me.getId());
}
}
运行测试用例,得到的输出结果是:
people = [Person{id=2, name=’me’, age=28, gender=MALE, addresses={BEIJING=Address{province=BEIJING, city=’北京’, street=’学院路’}}}
, Person{id=0, name=’you’, age=25, gender=FEMALE, addresses={BEIJING=Address{province=BEIJING, city=’北京’, street=’学院路’}}}
, Person{id=0, name=’him’, age=25, gender=MALE, addresses={BEIJING=Address{province=BEIJING, city=’北京’, street=’学院路’}}}
]
另外,查询数据库可以得到:
mysql root@localhost:test> SELECT * from person;
+------+--------+-------+----------+--------------------------------------------------------------------+
| id | name | age | gender | addresses |
|------+--------+-------+----------+--------------------------------------------------------------------|
| 1 | me | 28 | MALE | {"BEIJING":{"province":"BEIJING","city":"北京","street":"学院路"}} |
| 2 | you | 25 | FEMALE | {"BEIJING":{"province":"BEIJING","city":"北京","street":"学院路"}} |
| 3 | him | 25 | MALE | {"BEIJING":{"province":"BEIJING","city":"北京","street":"学院路"}} |
+------+--------+-------+----------+--------------------------------------------------------------------+
3 rows in set
Time: 0.002s
mysql root@localhost:test> SELECT LAST_INSERT_ID();
+--------------------+
| LAST_INSERT_ID() |
|--------------------|
| 17 |
+--------------------+
1 row in set
Time: 0.001s
从上面的示例可以看出 3 个问题:
- 即使使用了
userGeneratedKeys = true
并指定了keyProperty
,只回写了第一行的主键。 - 回写的主键与数据库不一致。
LAST_INSERT_ID()
的值发生了跳跃,按理来说应该是 3,但是变成了 17。
2. 疑问
看到这里其实很让人费解:
- 为什么只返回了一个主键?
useGeneratedKeys
返回的主键不对那么到底是什么?- 为什么
LAST_INSERT_ID()
发生了跳变?
首先从 userGeneratedKeys
说起:
useGeneratedKeys(仅对 insert 和 update 有用)这会令 MyBatis 使用 JDBC 的 getGeneratedKeys 方法来取出由数据库内部生成的主键(比如:像 MySQL 和 SQL Server 这样的关系数据库管理系统的自动递增字段),默认值:false。
With older JDBC drivers for MySQL, you could always use a MySQL-specific method on the
Statement
interface, or issue the querySELECT LAST_INSERT_ID()
after issuing anINSERT
to a table that had anAUTO_INCREMENT
key.First, we demonstrate the use of the new JDBC 3.0 method
getGeneratedKeys()
which is now the preferred method to use if you need to retrieveAUTO_INCREMENT
keys and have access to JDBC 3.0. The second example shows how you can retrieve the same value using a standardSELECT LAST_INSERT_ID()
query.
也就是说 Mybatis 通过 useGeneratedKeys
返回的是 LAST_INSERT_ID()
。
接着说,那么为什么只回写了一个主键,并且还是错的呢?
If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only.
按照上文的说法,批量插入只会返回插入的第一条数据的主键。第一次插入 me
这个对象之后 LAST_INSERT_ID()
返回 1。接着在插入 people
时首先是更新了 me
这行记录,而 LAST_INSERT_ID()
没有变。直到插入第二行 you
这个对象,此时 LAST_INSERT_ID()
返回 2,也就是批量插入后回写的主键值。这同时解释了为什么只回写了一个主键并且回写的主键与数据库没有对应上。
最后,关于 LAST_INSERT_ID()
的跳变,我也找到了一些参考资料:
- 官方文档:InnoDB AUTO_INCREMENT Lock Modes。
- 其实这个说的更加简洁清楚:AUTO_INCREMENT 字段的GAP