小工具知识网

当前位置: 首页 > 行业知识 > mysql特殊语法insert into .. on duplicate key update ..使用详解

行业知识

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

2026-11-19 04:25:31 来源:互联网转载

文章目录

  • 一、前言
  • 二、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字段的值。

上一篇:11年奥迪a5音响主机怎么拆卸

下一篇:升级文档撰写秘籍,打造吸引眼球的标题之王