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
子句,则IGNORE
和DELAYED
选项会被忽略。
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()函数。