INSERT ON DUPLICATE KEY UPDATE

语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
  {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
  [ ON DUPLICATE KEY UPDATE
    col=expr
      [, col=expr] ... ]

或:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] ... ]

或:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] ... ]

描述

INSERT ... ON DUPLICATE KEY UPDATE是MariaDB/MySQL对INSERT语句的扩展,当插入数据的时候发现有重复值冲突,将对原记录执行UPDATE操作来替代INSERT操作。

当插入数据的时候没有执行UPDATE而是单纯地插入数据,受影响的行数为1,如果执行了UPDATE操作,受影响的行数为2,除非设置了API的CLIENT_FOUND_ROWS标记。

如果有多个unique索引被匹配,则只有第一个会被更新。因此,对于有多个唯一索引的表,不建议使用该语句来插入数据。

如果表具有AUTO_INCREMENT的主键列,且插入了或更新了一行,则LAST_INSERT_ID()函数将返回它的AUTO_INCREMEN值。

VALUES()函数只能应用在ON DUPLICATE KEY UPDATE子句中,在其他格式的INSERT语句中该函数无意义。它会返回INSERT语句中列对应的值。该函数在多行插入的环境中非常有用。

如果使用了 ON DUPLICATE KEY UPDATE子句,则IGNOREDELAYED选项会被忽略。

MariaDB starting with 10.0

PARTITION子句是从MariaDB 10.0开始引入的,详细信息见Partition Pruning and Selection

该语句会激活INSERT和UPDATE触发器。详细信息见Trigger Overview

还有一个与之类似的语句REPLACE

示例

CREATE TABLE ins_duplicate (id INT PRIMARY KEY, animal VARCHAR(30));
INSERT INTO ins_duplicate VALUES (1,'Aardvark'), (2,'Cheetah'), (3,'Zebra');

由于没有键值重复冲突,下面的语句将以普通的INSERT操作插入数据:

INSERT INTO ins_duplicate VALUES (4,'Gorilla') ON DUPLICATE KEY UPDATE animal='Gorilla';
Query OK, 1 row affected (0.07 sec)
SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Aardvark |
|  2 | Cheetah  |
|  3 | Zebra    |
|  4 | Gorilla  |
+----+----------+

由于键值重复,下面的INSERT语句插入失败:

INSERT INTO ins_duplicate VALUES (1,'Antelope');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

此时,可以使用INSERT ON DUPLICATE KEY UPDATE来更新重复值:

INSERT INTO ins_duplicate VALUES (1,'Antelope') ON DUPLICATE KEY UPDATE animal='Antelope';
Query OK, 2 rows affected (0.09 sec)

注意,上面的语句将会报告受影响的行数为2,这是UPDATE的行为。

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Antelope |
|  2 | Cheetah  |
|  3 | Zebra    |
|  4 | Gorilla  |
+----+----------+

添加一个新的具有唯一性的字段:

ALTER TABLE ins_duplicate ADD id2 INT;
UPDATE ins_duplicate SET id2=id+10;
ALTER TABLE ins_duplicate ADD UNIQUE KEY(id2);

由于匹配了两个唯一键,因此只有第一个被匹配的键被更新。这是不安全的行为,因此不推荐在多个唯一键的表上执行INSERT ON DUPLICATE KEY UPDATE语句,除非你直到自己在做什么。注意,下面的警告只在MariaDB 5.5之前才会显示,在MariaDB 10.0中已经移除了,因为MariaDB假定按照顺序去检查各个键,顺序可由SHOW CREATE TABLE获取。

INSERT INTO ins_duplicate VALUES (2,'Lion',13) ON DUPLICATE KEY UPDATE animal='Lion';
Query OK, 2 rows affected, 1 warning (0.06 sec)

SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                  |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT * FROM ins_duplicate;
+----+----------+------+
| id | animal   | id2  |
+----+----------+------+
|  1 | Antelope |   11 |
|  2 | Lion     |   12 |
|  3 | Zebra    |   13 |
|  4 | Gorilla  |   14 |
+----+----------+------+

尽管第三行id=3对应的id2=13也被INSERT语句匹配到了,但却没有去更新该行的记录。

将id字段更改为自增字段auto_increment。如果insert插入了一个新行,auto_increment的只将向前移动。如果更新了已存在的行,则auto_increment保留原有的值。

ALTER TABLE `ins_duplicate` CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE ins_duplicate DROP id2;
SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
|              5 |
+----------------+

INSERT INTO ins_duplicate VALUES (2,'Leopard') ON DUPLICATE KEY UPDATE animal='Leopard';
Query OK, 2 rows affected (0.00 sec)

SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
|              5 |
+----------------+

INSERT INTO ins_duplicate VALUES (5,'Wild Dog') ON DUPLICATE KEY UPDATE animal='Wild Dog';
Query OK, 1 row affected (0.09 sec)

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Antelope |
|  2 | Leopard  |
|  3 | Zebra    |
|  4 | Gorilla  |
|  5 | Wild Dog |
+----+----------+

SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
|              6 |
+----------------+

从INSERT语句中引用字段的值来更新数据:

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

更多内容参见VALUES()函数。

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.