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

亦凉 2023-08-17 15:38 150阅读 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为自增长唯一字段试试
     *  五、总结

## 一、前言 ##

我们在做批量插入的时候，经常需要根据唯一字段判断，唯一字段相同时则进行更新，唯一字段不同时则进行插入。一般来说我们都是采用`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)` 就是`1` ，`values(b)`就是`2`，`values(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函数加条件 ###

`mysql`的`IF()`函数类似于`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')

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xKRlBIUA_size_16_color_FFFFFF_t_70 1]

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

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')；

**如图：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xKRlBIUA_size_16_color_FFFFFF_t_70 2]

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

### 2、解决方案 ###

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

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

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

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

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

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

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

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

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

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

#### （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`);

**如图：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xKRlBIUA_size_16_color_FFFFFF_t_70 3]

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

## 五、总结 ##

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

去掉id字段，选用user\_id作为主键则完全解决了这个问题，如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xKRlBIUA_size_16_color_FFFFFF_t_70 4]

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

**end**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xKRlBIUA_size_16_color_FFFFFF_t_70]: /images/20230810/3f3e762cad874bdf9a33dc4dc8aac0e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xKRlBIUA_size_16_color_FFFFFF_t_70 1]: /images/20230810/17359527df794d9b800e54955e09221f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xKRlBIUA_size_16_color_FFFFFF_t_70 2]: /images/20230810/a1ba9a628dd442d2884cfea615ea6b68.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xKRlBIUA_size_16_color_FFFFFF_t_70 3]: /images/20230810/ae8e5ce032d942e69f225bf6572f2cc6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xKRlBIUA_size_16_color_FFFFFF_t_70 4]: /images/20230810/8b29a1d024c54cd7a5a0627c5557dca7.png