环境
Intellij IDEA : 2021.3
Mysql:8+
java:1.8+
前言
以前使用mongodb
、JOOQ组件
的时候都是有insertOrUpdate
的功能,现在使用mybatis似乎没有提供这种功能。
最近研究了,这个功能其实是mysql提供的,利用的是duplicate key update
;
假设,我们有这么一张表:
CREATE TABLE `relation` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(64) NOT NULL DEFAULT '' COMMENT '名称',
`relation_id` varchar(64) NOT NULL DEFAULT '' COMMENT '关联id',
`type` int(11) NOT NULL DEFAULT '0' COMMENT '0:默认',
`is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT ' 状态值',
`create_at` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人',
`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_at` varchar(64) NOT NULL DEFAULT '' COMMENT '更新人',
`updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新人',
PRIMARY KEY (`id`),
UNIQUE KEY `ix_relation_id_type` (`relation_id`,`type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
注意: ix_relation_id_type:唯一索引
Dao
@Mapper
public interface FlowModelMapper {
void insertOrUpdate(List<FlowModel> flowModel);
}
Mapper XML文件
<insert id="insertOrUpdate">
insert into flow_model(name, relation_id, type, is_delete,create_at,update_at)
values
<foreach collection="list" item="p" index="index" separator=",">
(
#{p.name},
#{p.relationId},
#{p.type},
#{p.isDelete},
#{p.createAt},
#{p.updateAt}
)
</foreach>
on duplicate key update
name=values(name),
update_at=values(update_at)
</insert>
说明:
on duplicate key update
这个是非常关键的地方,需要有唯一键
和主键
。on duplicate key update
后面跟着的name=values(name)
算是一个固定写法,作用:动态的传入要修改的值。
在MySQL 8.0.20
之后,VALUES()
在mysql未来的版本会被删除。
官方建议,使用列别名的方式来写:
<insert id="insertOrUpdate">
insert into flow_model(name, relation_id, type, is_delete,create_at,update_at)
values
<foreach collection="list" item="p" index="index" separator=",">
(
#{p.name},
#{p.relationId},
#{p.type},
#{p.isDelete},
#{p.createAt},
#{p.updateAt}
)
</foreach>
AS fm
on duplicate key update
name=fm.name,
update_at=fm.update_at
</insert>
行别名
insert into …values
语法:insert into ...values(...) AS 行别名 ON DUPLICATE KEY UPDATE 使用行别名
。
例如:下面的 new
就是行别名。
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
ON DUPLICATE KEY UPDATE c = new.a+new.b;
列别名
或者是:insert into ...values(...) AS 行别名(列别名,列别名,列别名) ON DUPLICATE KEY UPDATE 使用别名
下面的m,n,p是随便取的列别名
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;
注意:
- 当使用列别名时,必须在
VALUES
子句后面使用行别名,即使在后面的子句中不使用行别名。
除了insert into … values 场景,insert into …set场景也适用。
语法和上面是一样的:
INSERT INTO t1 SET a=1,b=2,c=3 AS new
ON DUPLICATE KEY UPDATE c = new.a+new.b;
INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;
主键和唯一索引
现在假设我们有这些索引:
- 唯一索引:biz_id、name、code
- 主键:id
insert into template_url(id,name, code, url, scope, description,
biz_id, create_by, create_user_id, update_by, update_user_id)
values
(
1,'yutao','yutao','www.baidu.com','yutao','yutao',0,'yutao',0,'yutao',0
)
ON DUPLICATE KEY UPDATE
name=values(name),
description=values(description),
url=values(url),
scope=values(scope),
update_by=values(update_by),
update_user_id=values(update_user_id)
主键冲突
假设这时,主键冲突,那么MySQL就会接着判断是否 唯一索引冲突:
① 唯一索引不冲突,那么久执行更新
② 唯一索引冲突,就会报错:
1062 - Duplicate entry '0-yutao-yutao111' for key 'template_url.uk_biz_id_code_name', Time: 0.004000s
编辑时,唯一索引的字段不要修改
总结
insertOrUpdate
的实现是基于mysql的on duplicate key update 来实现的。- 使用
ON DUPLICATE KEY UPDATE
,如果行作为新行插入,则每行受影响的行值为1。如果更新现有行,则每行受影响的行值为2;如果将现有行设置为其当前值,则每行受影响的行值为0(可以通过配置,使其受影响的行值为1)。