mysql复杂的插入语句,MYSQL多种插入或修改的语法特性详解(replace into/insert ignore into/insert into on duplicate key update...

在我们平常的业务场景中,可能有很多时候,需要对库操作进行增(未存在相应数据行)或者修改(已存在相应数据行),这时候,有人就会想到不管什么语言,我先查相应的库有没有这条行数据,再根据结果进行操作,不就完事了吗(已有的数据则修改,没有的数据则新增).

能想到解决方案是好的,但是这种,先查后增或改的方案,存在很大的弊端,原因如下:

1、你先查后操作,这并不是原子性的,在多个线程进行操作的时候,比如线程1和线程2都查询了没有这条结果,然后先后进行插入操作,如果库已经做了业务数据的唯一性约束,则这种情况会报错.会带来各种线程安全的问题.或许你又会想到,线程安全问题,那窝加锁不就完事了吗?加锁固然是可以,但是这个损失的性能,对于一些高并发的业务场景来说,又有一些得不偿失.

2、先查,后操作,这个查的成本,如果是目标表数据量级在百万千万级别,这个查询效果,并不一定是很快能返回结果的(尤其是字符串索引的情况),当这个接口如果调用频率比较高,不仅对数据库的IO负载,还是对接口的响应能力,以及多次库请求之间的网络延迟来讲,都是一个不小的开销.而且这个查询耗时,又会放大1所讲的线程安全问题.

一、replace into

先上建表语句

CREATE TABLE `student` (

`id` bigint(22) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键自增id‘,

`student_id`            bigint(22) NOT NULL DEFAULT ‘0‘ COMMENT ‘学生id‘,

`chinese_socre`       decimal(18,6) NOT NULL DEFAULT ‘0.000000‘ COMMENT ‘语文分数‘,

`math_score`           decimal(18,6) NOT NULL DEFAULT ‘0.000000‘ COMMENT ‘数学分数‘,

PRIMARY KEY (`id`) USING BTREE,

UNIQUE KEY `un_idx_stu_id` (`student_id`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘学生表‘;

场景:

一个年级的语文和数学考试补考(前面已经存在了部分同学的成绩数据,但是可能因为某些原因,有的同学是二次考试(原先已经存在数据),有的上次因为有事没来参加考试(补考是他的第一次考试,原先不存在数据))完毕,这时候replace into语法就派上用场了,

replace into student(student_id,chinese_socre,math_score) values(1,58,59),(2,60,61),(3,62,63).......;

优点

1、语法简单,而又覆盖情况广:

对于第二次考试的同学来说,这一次插入进去的数据,会覆盖掉原先的数据,即更新成他最新的补考分数数据(这里暂且不考虑成生产环境的高要求,可能需要留存其历史成绩的场景)

对于第一次考试的同学来说,这一次即会将其数据全部插入新增

对于不需要参加补考的同学来说,这一次操作,对其原有数据没有影响.

2、因为是采用的数据库唯一索引做业务约束,既保证了数据的最终唯一性,也比之前先查后操作的方式要极大的提高效率(不存在多次网络调用通信成本,唯一性由数据库高效唯一索引去保证和查询等等)

注:

1、插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据

2、如果数据原表已经存在(根据主键、索引判断是否存在),存在则更新(删除原先的,新增后面的),不存在则新增,replace into做的是全量更新(完全以新数据为准,原有的数据不会保存)

业务场景类推:

比如说,现在有两个系统需要对接、同步商品数据,A系统是输出方,B系统是接收方,A给出的数据,都是完整的,全量的,这时候,就可以使用此语法了,B系统不需要去考虑A系统给过来的商品信息,到底是已经存在的,还是原先就有,需要更新的.语法简单而又覆盖的情况多.

二、insert ignore into

insert ignore into student(student_id,chinese_score,math_score) values(1,58,59),(2,60,61),(3,62,63).......;

场景、优点

1、比如原先只录入了一部分同学的分数数据,然后被意外中断,但是不好分辨已经录入的是哪一些,此时就可以采用此种语法,对原有数据没有影响(不会因为已录入数据唯一索引重复的而报错导致中断),但是对新增的数据会进行插入

2、因为是采用的数据库唯一索引做业务约束,既保证了数据的最终唯一性,也比之前先查后操作的方式要极大的提高效率(不存在多次网络调用通信成本,唯一性由数据库高效唯一索引去保证和查询等等)

注:

1、插入数据的表必须有主键或者是唯一索引！否则的话，insert ignore into 会直接插入数据，这将导致表中出现重复的数据

2、如果数据原表已经存在(根据主键、索引判断是否存在),存在则不作任何操作,不存在则新增,insert ignore into做的是增量插入(与原有数据存在重合的情况下,会以原有数据为准)

业务场景类推:

比如说,现在有两个系统需要对接、同步订单数据,接收方系统并根据每次新成功同步的订单数据做一些相关统计

做的方案是A系统实时同步给B系统,为了避免某时间段或者某次实时同步异常,晚上还会定时做一次全量同步,这时候,就可以使用此语法了,B系统不需要去考虑A系统给过来的订单信息到底是已存在的还是没有的,用此语法插入兼容两种情况并且,新增的根据其返回影响行数,可以确定到底是新增几单(结合java的mybatis框架,插入成功可以返回自增主键,来判定到底是哪几单新增成功,从而进行相应的后续业务统计逻辑计算)

三、insert into    on duplicate keyupdate

insert into student(student_id,chinese_score,math_socre) values(1,58,59),(2,60,61),(3,62,63).......  on duplicate key update chinese_socre = values(chinese_score),math_socre = math_socre;

场景、优点

比如此次补考,只是语文补考,数学没有进行考试.则可采用此种语法,进行自定义的更新(对已有数据而言,即补考)

1、语法简单,而又覆盖情况广:

比如此次考试,如果是第二次参加的同学,则只更新其语文成绩,数学成绩还是保持原来的数据

对于第一次考试的同学来说,这一次即会将其数据全部插入新增

对于不需要参加补考的同学来说,这一次操作,对其原有数据没有影响.

2、因为是采用的数据库唯一索引做业务约束,既保证了数据的最终唯一性,也比之前先查后操作的方式要极大的提高效率(不存在多次网络调用通信成本,唯一性由数据库高效唯一索引去保证和查询等等)

注:

1、插入数据的表必须有主键或者是唯一索引！否则的话此语法会直接插入数据，这将导致表中出现重复的数据

2、如果数据原表已经存在(根据主键、索引判断是否存在),存在则更新on duplicate key update后的自定义操作,不存在则新增,他做的是全量插入或者自定义更新

业务场景类推:

比如说,现在有两个系统需要对接、同步商品的数据(假定此处A系统只有商品价格数据会发生变更,其他的都不会,上面的例子是商品的全部属性数据,注意区别)

做的方案是A系统实时同步给B系统,这时候,就可以使用此语法了,B系统不需要去考虑A系统给过来的商品信息到底是已存在的还是没有的,用此语法插入兼容两种情况并且,出现已有此商品数据的情况,则只更新其价格信息,减少数据库开销.也可以将update后的操作进行复杂扩展,提供了自定义操作的语法空间支持

————————————————

版权声明：本文为CSDN博主「没有永恒」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/qq_18406563/article/details/80639029

原文：https://www.cnblogs.com/hsz1124/p/11641515.html