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

亦凉 2023-08-17 15:38 231阅读 0赞

文章目录

    • 一、前言
    • 二、关于on duplicate key update
      • 1、官方手册地址
      • 2、应用实例
      • 3、操作列名，主键相同则某个字段相加
      • 4、values()函数的含义
    • 三、on duplicate key update 加条件
      • 1、通过IF函数加条件
      • 2、通过IF()函数加上字段条件
      • 3、通过IF()函数加连环条件
    • 四、批量插入更新带来的id跳跃式增长问题
      • 1、模拟插入数据时主键重复造成的自增字段跳跃增长：
      • 2、解决方案
        • （1）表数据量很小，且主要做查询
        • （2）表数据量小，但是对增删改查要求高
        • （3）表中数据是集中添加的，添加的时候不会有增删改查操作
        • （4）表数据量已经很大了，并且id未插满
        • （5）表数据量大并且id已满
        • （6）实验，设置user_id为主键，id为自增长唯一字段试试
    • 五、总结

一、前言

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

二、关于on duplicate key update

  1.   关于`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、应用实例

  1.   这个方法就是当主键重复的时候，我们可以在`update`后面进行一些操作，实现主键重复就更新的效果。比起之前的insert和update语句要省不少事，大致用法如下：
  3. INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  4.   ON DUPLICATE KEY UPDATE c=c+1;

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

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

解释：

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

4、values()函数的含义

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

另外：

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

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

三、on duplicate key update 加条件

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

1、通过IF函数加条件

  1.   `mysql`的`IF()`函数类似于`php`中的三元操作，即：
  3. IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false

例如：

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

解释：

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

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

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

解释：

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

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

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

  1.   当需要添加的条件比较多的时候呢，我们可以通过逗号来连接各个条件，如下：
  3. insert ignore into xxx (user_id,pay_id,city_level,log_time,pay_date) values {$sqls}
  4.             on duplicate key update 
  5.     city_level=IF(log_time > values(log_time),values(city_level),city_level), 
  6.     log_time=IF(values(log_time) < log_time,values(log_time),log_time)

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

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

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

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

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

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

在这里插入图片描述

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

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

如图：

在这里插入图片描述

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

2、解决方案

（1）表数据量很小，且主要做查询

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

（2）表数据量小，但是对增删改查要求高

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

（3）表中数据是集中添加的，添加的时候不会有增删改查操作

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

  1.   设置为`0`的时候，会加上表锁，等语句执行完成的时候在释放，如果真的添加了记录，将`auto_increment`加`1`。具体的参数解释，可以百度一下，解释的都很清晰。
  3.   这个方案插入性能是慢了，不过最起码`id`字段不会乱跳。不过执行这个方案的前提是不会影响到数据库中的其他的表操作。如果数据库中存在多个表的批量插入更新，或者说其他的表存在并发插入业务，那么还是不要用这个方案了，毕竟`innodb_autoinc_lock_mode` 针对的是所有的`auto_increment` 列的表，对这些表的插入操作都会有影响的。

（4）表数据量已经很大了，并且id未插满

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

（5）表数据量大并且id已满

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

（6）实验，设置user_id为主键，id为自增长唯一字段试试

  1. ALTER TABLE `pay_first_charge_info` ADD COLUMN `id` int(11) UNSIGNED NOT NULL 
  3. AUTO_INCREMENT AFTER `user_id`,ADD UNIQUE INDEX `unq_id`(`id`);

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

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

五、总结

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

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

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

end

发表评论

表情：
评论列表 （有 0 条评论，231人围观）

还没有评论，来说两句吧...

相关阅读