mysql特殊语法insert into .. on duplicate key update ..使用详解
文章目录
- 一、前言
- 二、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;
2> 存在记录，可以更新字段的情况
insert into user2(userName, age, gender) VALUES("saint33", 99, 1) on DUPLICATE KEY UPDATE age = 88;
3> 存在记录，不可以更新字段的情况
insert into user2(userName, age, gender) VALUES("saint33", 99, 1) on DUPLICATE KEY UPDATE age = 88;
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;
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;
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;
注意：VALUES()函数只在INSERT…UPDATE语句中有意义，其它时候会返回NULL；
4、last_insert_id()
如果表含有auto_increment字段，使用insert … on duplicate key update插入或更新后，last_insert_id()返回auto_increment字段的值。