mybatis批量插入问题记录
需求:自增主键批量插入,存在则插入,不存在则更新。插入或更新后返回对应的主键id
CREATE TABLE `eth_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(127) NOT NULL DEFAULT '' COMMENT 'name',
`age` int(11) NOT NULL,
`version` int(11) NOT NULL DEFAULT '0' COMMENT 'version',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '新增的时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uq_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='test table';
<insert id="batchInsert" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
insert into eth_test(name, age, `version`) VALUES
<foreach collection="list" item="item" separator=",">
(
</foreach>
ON DUPLICATE KEY UPDATE `version`=values(`version`), age = values(age)
</insert>
int batchInsert(@Param("list") List<EthTest> list);
结论:此方法sql如果加ON DUPLICATE KEY UPDATE,批量插入时只会返回第一个插入或更新的id,其余插入不返回id值,如果不加ON DUPLICATE KEY UPDATE则插入重复name会报错,但如果不重复,批量插入是可以返回所有的id的
暂时解决方案,单条插入,此方法无论更新或插入都会返回id
<insert id="insert" keyColumn="id" keyProperty="id" useGeneratedKeys="true" parameterType="EthTest">
insert into eth_test(name, age, version)
values (
on duplicate key update age =
</insert>
int insert(EthTest ethTest);