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

文章目录

一、前言

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

比如下列伪代码：

$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、示例：

表结构

CREATETABLE`user2`(`id`int(11)NOTNULLAUTO_INCREMENT,`userName`varchar(94)NOTNULL,`age`int(11)DEFAULTNULL,`gender`int(1)DEFAULTNULL,`type`int(1)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`idex_name`(`userName`)USINGBTREE,KEY`idx_type`(`type`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=13DEFAULTCHARSET=utf8;

user2表中有一个主键id、一个唯一索引idx_userName；

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

insertinto user2(userName, age, gender)VALUES("saint33",99,1)onDUPLICATEKEYUPDATE age =88;

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

insertinto user2(userName, age, gender)VALUES("saint33",99,1)onDUPLICATEKEYUPDATE age =88;

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

insertinto user2(userName, age, gender)VALUES("saint33",99,1)onDUPLICATEKEYUPDATE age =88;

4> 存在多个唯一索引时

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

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

insertinto user2(id, userName, age, gender)VALUES(12,"saint22",99,1)onDUPLICATEKEYUPDATE age =VALUES(gender)+80;

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

insertinto user2(id, userName, age, gender)VALUES(10,"saint22",99,1)onDUPLICATEKEYUPDATE age =VALUES(gender)+90;

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

Update子句可以使用values(col_name)获取insert部分的值：

insertinto user2(userName, age, gender)VALUES("saint22",99,1)onDUPLICATEKEYUPDATE age =VALUES(age)+100;

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

4、last_insert_id()

如果表含有auto_increment字段，使用insert … on duplicate key update插入或更新后，last_insert_id()返回auto_increment字段的值。