MySQL中replace into详解、批量更新、不存在插入存在则更新、replace into的坑-三、replace into 使用案例

2024-04-18 19:03:36

3.1、replace into values

3.1.1、只有主键且主键冲突

当只有主键且主键冲突时，会执行delete+insert操作

如下sql:

drop table if exists user_test;
create table user_test(
	id int primary key auto_increment comment '主键',
	name varchar(30)  not null comment '姓名',
	update_time timestamp on update CURRENT_TIMESTAMP comment '更新时间'
) comment '测试表';

insert into user_test(name,update_time) value ('zhangsan','2000-01-01 00:00:00');

此时执行replace into

replace into user_test(id,name) value (1,'zhangsan');

上图可以看出执行replace into之后，返回的受影响条数是2，且update_time的字段被更新为空了。由此证明replace into时当只有主键且主键冲突时执行了delete+insert操作。

注意：当时间字段设置自动更新后，若没有设置默认值，replace into更新时需要手动赋予时间字段，否则会插入为空。

3.1.2、有主键有唯一索引且主键冲突

当有主键有唯一索引且主键冲突时，会执行delete+insert操作

drop table if exists user_test;
create table user_test(
	id int primary key auto_increment comment '主键',
	name varchar(30) unique not null comment '姓名',
	update_time timestamp on update current_timestamp comment '更新时间'
) comment '测试表';

insert into user_test(name,update_time) value ('zhangsan','2000-01-01 00:00:00');

replace into user_test(id,name) value (1,'lisi');

上图可以看出执行replace into之后，返回的受影响条数是2，且update_time的字段被更新为空了。由此证明replace into时当有主键有唯一索引且主键冲突时执行了delete+insert操作。

3.1.3、有主键有唯一索引且唯一索引冲突(有坑)

当有主键有唯一索引且唯一索引冲突时，会执行delete+insert操作，且AUTO_INCREMENT自增1

drop table if exists user_test;
create table user_test(
	id int primary key auto_increment comment '主键',
	name varchar(30) unique not null comment '姓名',
	update_time timestamp on update current_timestamp comment '更新时间'
) comment '测试表';

insert into user_test(name,update_time) value ('zhangsan','2000-01-01 00:00:00');

此时执行replace into语句

replace into user_test(name) value ('zhangsan');

上图可以看出执行replace into之后，返回的受影响条数是2，且update_time的字段被更新为空了。由此证明replace into时当有主键有唯一索引且唯一索引冲突时执行了delete+insert操作。

注意：唯一索引冲突时会带来副作用，主键id被改变了。由于主键设置了自增，所以当唯一索引冲突时导致AUTO_INCREMENT的值自增1。

3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯一键冲突(有坑)

当有主键与唯一索引时，若replace into与其中一条主键冲突、与另一条记录唯一索引冲突，此时执行delete+insert操作。注意：此时受影响的条数是3，且会删除两条数据，可能会有坑

drop table if exists user_test;
create table user_test(
	id int primary key auto_increment comment '主键',
	name varchar(30) unique not null comment '姓名',
	update_time timestamp on update current_timestamp comment '更新时间'
) comment '测试表';

insert into user_test(id,name,update_time) values
(1,'zhangsan','2000-01-01 00:00:00'),
(2,'lisi','2000-01-01 00:00:00');

replace into user_test(id,name) value (1,'lisi');

上图可以看出执行replace into之后，返回的受影响条数是3。

注意：在该场景下会删除2条数据，然后插入1条数据，所以可能会造成坑，需要特别注意。

3.2、replace into select

语法类似insert into select

示例：
replace into t(id, update_time) select 1, now();
replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2;

3.3、replace into set

表结构如下：

drop table if exists user_test;
create table user_test(
	id int primary key auto_increment comment '主键',
	name varchar(30) unique not null comment '姓名',
	age int default 2 comment '年龄',
	sex varchar(10) comment '性别：男、女'
) comment '测试表';

其中： name为唯一索引，age默认值是2

3.3.1、唯一索引不重复

执行以下sql插入第一条数据

replace into user_test set name = 'zhangsan',age = age+1, sex = '男';

可以看到age=3是因为age=default(age)+1，默认age=2然后又+1所以是3；若age字段建表时没有设置默认值则上述sql执行完后age为null。

3.3.2、唯一索引重复

接着上面的示例，运行以下sql

replace into user_test set name = 'zhangsan',age = age+1;

我们看到id=1的数据被删除了，新增了一条id=2的数据；它的sex字段的值没有继承第一条数据的值，age属性的值也只是由默认值决定，与上一条的值无关。

由此证明结论：

在没有唯一键/主键重复时，replace into所做的事情就是新添加一个条目，条目各个属性的值与运行的语句内容有关。
在有唯一键/主键重复时，replace into所做的事情就是

直接删除掉那条重复的旧条目
然后新添加一个条目。

新条目各个属性的值只取决于运行的语句内容，与被删除的旧条目的值无关。

码农公寓