mysql特殊语法insert into .. on duplicate key update ..使用详解

柔光的暖阳◎ 2023-09-24 13:13 82阅读 0赞

#### 文章目录 ####

*  一、前言
 *  二、insert into … on duplicate key update ...
 *   *  1、处理逻辑
     *  2、示例：
     *   *  表结构
         *  1> 不存在记录，插入的情况
         *  2> 存在记录，可以更新字段的情况
         *  3> 存在记录，不可以更新字段的情况
         *  4> 存在多个唯一索引时
         *   *  1）数据库中id = 12的记录不存在，userName="saint22"的记录存在，所以会根据第二个唯一索引userName做duplicate判断；
             *  2）数据库中id = 10的记录存在，userName="saint22"的记录存在，所以会根据第一个唯一索引id做duplicate判断；
         *  3、Update子句获取inset部分的值
         *  4、last\_insert\_id()

## 一、前言 ##

在日常开发中，经常会遇到这样的需求：查看某条记录是否存在，不存在的话创建一条新记录，存在的话更新某些字段。

比如下列伪代码：

$row = mysql_query($result);
    
    if($row){
        
    
    mysql_execute('update ...');
    
    }else{
        
    
    mysql_execute('insert ...');
    
    }

## 二、insert into … on duplicate key update … ##

MySql针对此，提供了insert into … on duplicate key update …的语法：

*  在insert的时候，如果insert的数据会引起唯一索引(包括主键索引)的冲突，即唯一值重复了，则不会执行insert操作，而执行后面的update操作。

**注意：这个是MYSQL特有的，不是SQL标准语法；**

### 1、处理逻辑 ###

insert into … on duplicate key update …语句是根据唯一索引判断记录是否重复的；

*  如果不存在记录，插入，则影响的行数为1；
 *  如果存在记录，可以更新字段，则影响的行数为2；
 *  如果存在记录，并且更新的值和原有的值相同，则影响的行数为0。

如果表同时存在多个唯一索引，只会根据第一个在数据库中存在相应value的唯一索引做duplicate判断：

### 2、示例： ###

#### 表结构 ####

CREATE TABLE `user2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userName` varchar(94) NOT NULL,
      `age` int(11) DEFAULT NULL,
      `gender` int(1) DEFAULT NULL,
      `type` int(1) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `idex_name` (`userName`) USING BTREE,
      KEY `idx_type` (`type`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

user2表中有一个主键id、一个唯一索引idx\_userName；

#### 1> 不存在记录，插入的情况 ####

insert into user2(userName, age, gender) VALUES("saint33", 99, 1) on DUPLICATE KEY UPDATE age = 88;

![在这里插入图片描述][311a0954e100419bb082b6954f17eb40.png]

#### 2> 存在记录，可以更新字段的情况 ####

insert into user2(userName, age, gender) VALUES("saint33", 99, 1) on DUPLICATE KEY UPDATE age = 88;

![在这里插入图片描述][a8d3dd6cc9644c2d9e4fc70f19298674.png]

#### 3> 存在记录，不可以更新字段的情况 ####

insert into user2(userName, age, gender) VALUES("saint33", 99, 1) on DUPLICATE KEY UPDATE age = 88;

![在这里插入图片描述][ebe1ded062884debaa3355d45b36f160.png]

#### 4> 存在多个唯一索引时 ####

如果表同时存在多个唯一索引，只会根据第一个在数据库中存在相应value的唯一索引做duplicate判断：

##### 1）数据库中id = 12的记录不存在，userName="saint22"的记录存在，所以会根据第二个唯一索引userName做duplicate判断； #####

insert into user2(id, userName, age, gender) VALUES(12, "saint22", 99, 1) on DUPLICATE KEY UPDATE age = VALUES(gender) + 80;

![在这里插入图片描述][a5f47fd8ba3948f5b33388b07d1ca986.png]

##### 2）数据库中id = 10的记录存在，userName="saint22"的记录存在，所以会根据第一个唯一索引id做duplicate判断； #####

insert into user2(id, userName, age, gender) VALUES(10, "saint22", 99, 1) on DUPLICATE KEY UPDATE age = VALUES(gender) + 90;

![在这里插入图片描述][8c86efb0fabd43db9291b8b2c8ef772d.png]

#### 3、Update子句获取inset部分的值 ####

**Update子句可以使用values(col\_name)获取insert部分的值：**

insert into user2(userName, age, gender) VALUES("saint22", 99, 1) on DUPLICATE KEY UPDATE age = VALUES(age) + 100;

![在这里插入图片描述][311b90da80754847ba80fb0238659c3d.png]

注意：VALUES()函数只在INSERT…UPDATE语句中有意义，其它时候会返回NULL；

#### 4、last\_insert\_id() ####

如果表含有auto\_increment字段，使用insert … on duplicate key update插入或更新后，last\_insert\_id()返回auto\_increment字段的值。

[311a0954e100419bb082b6954f17eb40.png]: https://img-blog.csdnimg.cn/311a0954e100419bb082b6954f17eb40.png
[a8d3dd6cc9644c2d9e4fc70f19298674.png]: https://img-blog.csdnimg.cn/a8d3dd6cc9644c2d9e4fc70f19298674.png
[ebe1ded062884debaa3355d45b36f160.png]: https://img-blog.csdnimg.cn/ebe1ded062884debaa3355d45b36f160.png
[a5f47fd8ba3948f5b33388b07d1ca986.png]: https://img-blog.csdnimg.cn/a5f47fd8ba3948f5b33388b07d1ca986.png
[8c86efb0fabd43db9291b8b2c8ef772d.png]: https://img-blog.csdnimg.cn/8c86efb0fabd43db9291b8b2c8ef772d.png
[311b90da80754847ba80fb0238659c3d.png]: https://img-blog.csdnimg.cn/311b90da80754847ba80fb0238659c3d.png