Mysql中INSERT ... ON DUPLICATE KE

2021-03-09 本文已影响0人牧码人zhouz

系统环境：

Mysql：5.7.32

基本功能

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE语句，并且新插入的行会导致UNIQUE KEY（唯一索引）或PRIMARY KEY（主键值）重复，那么会对原有记录进行UPDATE操作；如果不会导致唯一索引或主键值重复，则执行INSERT操作；

解决了什么问题

我们在实际的项目中，经常有以下需求：

向表中插入一条记录时，如果UNIQUE KEY（唯一索引）或PRIMARY KEY已存在，则更新记录，否则插入一条记录

逻辑上我们会这么写（伪代码）：

result = mysql_query("select...")
row = mysql_fetch(result)
if row：
    mysql_query("update...")
else:
    mysql_query("insert...")

这么做有两个问题：

1、效率低下，每次需要执行两条语句
2、高并发时会出问题，不能保证两条语句的原子性

Mysql通过ON DUPLICATE KEY UPDATE语句为我们解决了以上问题，既保证了原子性也保证了效率。

PS：在使用INSET...ON DUPLICATE KEY UPDATE这样的语句来插入记录时，如果遇到主键或者唯一二级索引列的值重复，会对B+树中已存在的相同键值的记录加X锁。

与`UPDATE`语句的异同

1、假设列a为UNIQUE索引且a=1的记录已经存在，下面两条语句效果相同：

INSERT INTO t1 (a, b, c) VALUES (1, 2, 3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

区别：

如果a是Innodb表中的自增键，则INSERT语句会导致auto-increment的值增加，UPDATE语句则不会

2、如果b也是UNIQUE索引，下面两条语句效果相同：

INSERT INTO t1 (a, b, c) VALUES (1, 2, 3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

区别：

如果一条记录中包含多个唯一索引（含主键），那么即使匹配上多条记录也只会更新一条记录。所以，一般情况在，不要在有多个唯一索引的表上使用ON DUPLICATE KEY UPDATE语句；

存在什么问题

1、对于Innodb引擎的表，使用INSET...ON DUPLICATE KEY UPDATE语句会导致auto-increment的值增加；
2、如果一条记录中包含多个唯一索引（含主键），那么即使匹配上多条记录也只会更新一条记录。所以，一般情况在，不要在有多个唯一索引的表上使用ON DUPLICATE KEY UPDATE语句；
3、如果是插入操作，影响的行数为1；如果是更新操作，影响的行数为2；如果更新的数据和已有的数据一样（就相当于没变，所有值保持不变），影响的行数为0；
4、这是Mysql独有的语法，其它数据库可能不支持。

VALUES函数

在ON DUPLICATE KEY UPDATE语句的赋值表达式中，可以使用VALUES(列名)来引用INSERT语句中该列的值，也就是将要插入的列的值。在多行插入时VALUES函数特别有用。
示例：

INSERT INTO t1 (a, b, c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

等同于

INSERT INTO t1 (a, b, c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a, b, c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

Tips：VALUES()函数只在ON DUPLICATE KEY UPDATE或INSERT语句中有意义，其它时候会返回NULL。

实验

1、实验前准备，创建表，并插入一条记录

CREATE TABLE t1 (
      a int(11) NOT NULL AUTO_INCREMENT,
      b int(11),
      c int(11),
      PRIMARY KEY(a)
    )ENGINE=Innodb default charset=utf8;

INSERT INTO t1(a, b, c) VALUES(1, 1, 1);

查看表中记录

mysql> SELECT * FROM t1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
+---+------+------+
1 row in set (0.01 sec)

查看当前Auto_increment值：

mysql> show table status like 't1'\G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 2
    Create_time: 2021-03-09 03:32:51
    Update_time: 2021-03-09 03:33:22
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

2、单UNIQUE键测试
插入ON DUPLICATE KEY UPDATE语句并查看：

mysql> INSERT INTO t1 (a, b, c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
Query OK, 2 rows affected (0.01 sec)

从插入结果看，影响了2行记录。

再次查看表中记录及Auto_increment值：

mysql> SELECT * FROM t1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
+---+------+------+
1 row in set (0.00 sec)

mysql> show table status like 't1'\G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 2  // 与官网文档不符，按照官网文档，这里应该增加，实际却并没有增加，不知道是哪里不对
    Create_time: 2021-03-09 03:32:51
    Update_time: 2021-03-09 03:34:40
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

3、多UNIQUE键测试
修改t1表结构，将b列改为UNIQUE索引

mysql> ALTER TABLE t1 ADD UNIQUE KEY uk_b(b);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

增加一列

mysql> INSERT INTO t1(a, b, c) VALUES(2,  2,  3);
Query OK, 1 row affected (0.01 sec)

查看

mysql> desc t1;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| b     | int(11) | YES  | UNI | NULL    |                |
| c     | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
| 2 |    2 |    3 |
+---+------+------+
2 rows in set (0.00 sec)

mysql> show table status like 't1'\G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 3
    Create_time: 2021-03-09 03:40:17
    Update_time: 2021-03-09 03:41:41
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

插入ON DUPLICATE KEY UPDATE语句，使a、b列同时冲突：

mysql> INSERT INTO t1(a, b, c) VALUES(1, 2, 4) ON DUPLICATE KEY UPDATE c=c+1;
Query OK, 2 rows affected (0.00 sec)

查看

mysql> SELECT * FROM t1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    3 |
| 2 |    2 |    3 |
+---+------+------+
2 rows in set (0.00 sec)

从结果看，只有第一行执行更新，验证了多行记录满足条件只会更新一行的说法。

4、VALUSE()函数实验
先把b列索引删除，改为普通列

mysql> ALTER TABLE t1 DROP KEY uk_b;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

插入记录

mysql> INSERT INTO t1 (a, b, c) VALUES (1,2,3), (2,5,6), (3, 4, 5) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 1  Warnings: 0

查看结果

mysql> SELECT * FROM t1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    3 |
| 2 |    2 |    7 |
| 3 |    4 |    5 |
+---+------+------+
3 rows in set (0.00 sec)

可见，所有重复主键的行已经更新，没有重复主键的按原数据执行插入操作。

还可以将原数据与新插入的数据一起操作，示例如下：

mysql> INSERT INTO t1 (a, b, c) VALUES (1,2,5) ON DUPLICATE KEY UPDATE c=c+VALUES(c);
Query OK, 2 rows affected (0.01 sec)

查看结果

mysql> SELECT * FROM t1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    8 |
| 2 |    2 |    7 |
| 3 |    4 |    5 |
+---+------+------+
3 rows in set (0.00 sec)

总结

INSERT...ON DUPLICATE KEY UPDATE语句，会在UNIQUE KEY（唯一索引）或PRIMARY KEY（主键值）重复时，对原有记录进行UPDATE操作；不重复，则执行INSERT操作；

VALUSE()函数在INSERT...ON DUPLICATE KEY UPDATE语句中非常有用，它可以获取到将要插入的列的值，对多行操作非常有用

INSERT...ON DUPLICATE KEY UPDATE有一些副作用，使用时要注意

AUTO_INCREMENT自增值问题（这个在实验中未验证）

多行匹配时，只能更新一行（当表中存在多个唯一索引时慎用）

参考

Mysql官方文档
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Mysql中INSERT ... ON DUPLICATE KE

基本功能

解决了什么问题

与`UPDATE`语句的异同

存在什么问题

VALUES函数

实验

总结

参考

猜你喜欢

热点阅读

Mysql中INSERT ... ON DUPLICATE KE

基本功能

解决了什么问题

与UPDATE语句的异同

存在什么问题

VALUES函数

实验

总结

参考

猜你喜欢

热点阅读

与`UPDATE`语句的异同