mysql根据条件批量插入更新(on duplicate key update)

mysql 专栏收录该内容
61 篇文章 11 订阅

一、前言

      我们在做批量插入的时候,经常需要根据唯一字段判断,唯一字段相同时则进行更新,唯一字段不同时则进行插入。一般来说我们都是采用on duplicate key update 写法,只是假如我们需要、在唯一字段相同,并且满足其他条件时才进行更新,那么该怎么写这个"where"条件呢?on duplicate key update会带来的自增长字段跳跃增长的问题该如何解决呢?

二、关于on duplicate key update

      关于mysql的批量插入更新,很多人的第一反应就是在插入之前进行查询,若主键相同则进行更新,主键不同则插入。只是这样的工作量要大上很多,不过mysql给我们提供了一个方案:on duplicate key update ,通过这个方法可以实现自动的插入更新,一条sql就能搞定。

1、官方手册地址

关于 on duplicate key update 的mysql官方手册地址:
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

2、应用实例

      这个方法就是当主键重复的时候,我们可以在update后面进行一些操作,实现主键重复就更新的效果。比起之前的insert和update语句要省不少事,大致用法如下:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

3、操作列名,主键相同则某个字段相加

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

解释:

当主键相同的时候,c字段的值更新为a +b的值。
假如a字段是主键,表中数据是:1,2,2 ,
那么当执行这条语句的时候,c字段会被更新为:2 +3 =5 ,即数据库中数据为:1,2,5

4、values()函数的含义

      values的含义是指新插入字段的值,比如我们新插入的数据为 (1,2,3),那么values(a) 就是1values(b)就是2values(c)就是3。借用这个values,我们可以实现更新的时候,字段相加减的效果。

另外:

VALUES()函数仅在INSERT ... ON DUPLICATE KEY UPDATE语句中有意义,NULL否则返回。

在MariaDB 10.3.3中,此函数被重命名为VALUE(),因为它与MariaDB 10.3.3中实现的
标准表值构造器语法不兼容。
VALUES()即使在MariaDB 10.3.3中,该函数仍可以使用,但只能在
INSERT ... ON DUPLICATE KEY UPDATE语句中使用。否则是语法错误。

也就是说mysql5.7之后,直接用value()函数就可以,不过我们用values()函数也不会报错。

三、on duplicate key update 加条件

      在实际开发中,单纯的通过update c= c是肯定不够用的,我们实际上要根据实际数据的大小来更新对应的值,但是在update后面直接加where条件是会报错,无法直接使用sql的那种写法。

1、通过IF函数加条件

      mysqlIF()函数类似于php中的三元操作,即:

IF(expr1,expr2,expr3),如果expr1的值为true,则返回expr2的值,如果expr1的值为false

例如:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=IF(a>0,1,2);

解释:

当a的值大于0的时候,c = 1,否则c=2

2、通过IF()函数加上字段条件

insert ignore into xxx (user_id,pay_id,city_level,log_time,pay_date) values {$sqls}
on duplicate key update log_time=IF(values(log_time) <log_time,values(log_time),log_time)

解释:

这里使用values(log_time)来代表插入的新数据中log_time的值,语意为:

当插入的values(log_time) 比原来的log_time字段的值小的时候,就更新log_time = values(log_time),
否则就log_time = log_time 即维持不变。

3、通过IF()函数加连环条件

      当需要添加的条件比较多的时候呢,我们可以通过逗号来连接各个条件,如下:

insert ignore into xxx (user_id,pay_id,city_level,log_time,pay_date) values {$sqls}
            on duplicate key update 
	city_level=IF(log_time > values(log_time),values(city_level),city_level), 
	log_time=IF(values(log_time) < log_time,values(log_time),log_time)

四、批量插入更新带来的id跳跃式增长问题

      我们在查看各种资料的时候,经常能看到大家说的这个自增长字段,也就是id会跳跃性增加的问题,下面咱们就先测试一下,然后给出合适的解决方案。

1、模拟插入数据时主键重复造成的自增字段跳跃增长:

insert ignore into pay_first_charg
e_info (user_id,pay_id,city_level,log_time,pay_date) values 
(150,36, 99,'2019-01-09 22:21:09','2019-01-09 22:19:09'),
 (150,36, 4,'2019-01-09 22:23:09','2019-01-09 22:19:09'), 
xxxx
 (155,45, 4,'2019-01-10 03:00:09','2018-07-02 22:39:48')

存储的数据如图:
在这里插入图片描述

      主键是150的,我们更新了两条记录,其中一条会替换原来表中的数据。然后新增了一条主键是155的数据,如图:

在这里插入图片描述

在原来的基础上,再次新增一些数据看看主键变化:

insert ignore into pay_first_charg
e_info (user_id,pay_id,city_level,log_time,pay_date) values 
 (156,46, 3,'2019-01-10 03:00:11','2018-07-02 22:46:50');

如图:

在这里插入图片描述

      果然还是会自增的,这样的话,插入千万级的数据,id可能会达到亿级,Int类型的主键早晚会扛不住的。这块也算是业界难题了,通过谷歌百度并没有完美的解决方案。下面给出几个还可以的解决方案,大家自己根据业务来改吧。

2、解决方案

(1)表数据量很小,且主要做查询

      如果表的数据量还小,那么可以考虑去掉id字段,改用唯一索引字段user_id作为主键,这样的话,插入的时候就不用担心自增字段的问题。不过没有自增字段,可能会影响updatedelete的性能,对查询性能影响有限。

(2)表数据量小,但是对增删改查要求高

      这样的话,id字段是不能少的,显式的自增字段对于updatedelete是比较友好的,而且后续还有利于统计分析相关。建议是设置id 字段为bigint,不过显然是治标不治本的,可以考虑用3条sql来实现业务。

(3)表中数据是集中添加的,添加的时候不会有增删改查操作

就比如博主这个表,是分析日志然后通过定时循环写入库中。在批量插入的时候,没有进行修改或者删除操作,这样的话,建议开启:innodb_autoinc_lock_mode = 0;这个值默认为1,代表当插入(可以确定插入行数)的时候,直接将auto_increment1,而不会去锁表,这也就提高了性能。

      设置为0的时候,会加上表锁,等语句执行完成的时候在释放,如果真的添加了记录,将auto_increment1。具体的参数解释,可以百度一下,解释的都很清晰。

      这个方案插入性能是慢了,不过最起码id字段不会乱跳。不过执行这个方案的前提是不会影响到数据库中的其他的表操作。如果数据库中存在多个表的批量插入更新,或者说其他的表存在并发插入业务,那么还是不要用这个方案了,毕竟innodb_autoinc_lock_mode 针对的是所有的auto_increment 列的表,对这些表的插入操作都会有影响的。

(4)表数据量已经很大了,并且id未插满

      MySQL 不支持 non blocking ddl, 因此线上大表的调整, 需要特别谨慎,所以想要临时修改idbigint的话会锁全表, 发生全拷贝, 相当于重建一张表,所以最佳操作就是拆分了,先查询,重复的部分更新,不重复的部分就新增,插入性能稍低一些,逻辑复杂一些,如果可以的话,最好是设置Idbigint类型。

(5)表数据量大并且id已满

      这种情况,必须要壮士断腕,最好的方式就是删除掉id,然后设置唯一索引为主键了。对于大表来说,主要瓶颈在于查询,其他的性能还是可以缓一缓的,而且一般来说唯一索引经常会作为查询条件使用,所以把唯一索引设置成主键,还能避免查询时候的回表操作,加快查询速度。缺点是需要更改大量的逻辑部分,原来用到id的部分都要改掉。

(6)实验,设置user_id为主键,id为自增长唯一字段试试

ALTER TABLE `pay_first_charge_info` ADD COLUMN `id` int(11) UNSIGNED NOT NULL 

AUTO_INCREMENT AFTER `user_id`,ADD UNIQUE INDEX `unq_id`(`id`);

如图:
在这里插入图片描述

      结果很残酷,自增长字段还是影响最大的因素,因此博主这里决定舍弃掉id字段,就用user_id作为主键了,这样虽然不太美观,统计上也不太好统计,但至少不会出现主键溢出的问题。所以大家还是根据自己的业务来选择比较好。

五、总结

      根据上面的分析,大家也许发现了,一劳永逸的方式就是在建表之初就设置唯一索引字段为主键,去掉id字段等。如果是等业务量起来了,那更多的就是补救了,大家按照自己的业务情况慎重选择方案即可。

去掉id字段,选用user_id作为主键则完全解决了这个问题,如图:
在这里插入图片描述

有舍有得,想要尽量完美那就必须要接收不完美的地方,古人诚不我欺。

end

  • 3
    点赞
  • 10
    评论
  • 12
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值