一、insert-on-duplicate語(yǔ)句語(yǔ)法

注意：ON DUPLICATE KEY UPDATE只是 MySQL的特有語(yǔ)法，并不是SQL標(biāo)準(zhǔn)語(yǔ)法！

INSERT INTO … ON DUPLICATE KEY UPDATE 是 MySQL 中一種用于插入數(shù)據(jù)并處理重復(fù)鍵沖突的語(yǔ)法。

這個(gè)語(yǔ)法適用于在 insert的時(shí)候，如果insert的數(shù)據(jù)會(huì)引起唯一索引(包括主鍵索引)的沖突，即唯一值重復(fù)了，則不會(huì)執(zhí)行insert操作，而執(zhí)行后面的update操作。

基本語(yǔ)法為：

INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...) 
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

-- 一般 Update子句可以使用 VALUES(col_name)獲取 insert部分的值
INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...) 
ON DUPLICATE KEY UPDATE column1 = VALUES(column1), column2 = VALUES(column2), ...;

說(shuō)明：

• table_name 是要插入數(shù)據(jù)的表名。
• (column1, column2, …) 是要插入的列名列表。
• (value1, value2, …) 是要插入的對(duì)應(yīng)列的值列表。
• ON DUPLICATE KEY UPDATE 子句后面指定了在沖突時(shí)需要執(zhí)行的更新操作。
• column1 = value1, column2 = value2, … 是要更新的列和對(duì)應(yīng)的新值。
• column1 = VALUES(column1), column2 = VALUES(column2), … 是要更新的列和對(duì)應(yīng)的新值（insert部分的值）。

insert-on-duplicate語(yǔ)句處理邏輯：

語(yǔ)句是根據(jù)唯一索引判斷記錄是否重復(fù)的。當(dāng)執(zhí)行插入操作時(shí)，如果唯一鍵不沖突(表中不存在記錄)，則執(zhí)行插入操作；如果遇到唯一鍵沖突(表中存在記錄)，則會(huì)執(zhí)行更新操作，使用給定的新值來(lái)更新沖突行中的列。

• 如果不存在記錄，插入，則影響的行數(shù)為1；
• 如果存在記錄，可以更新字段，則影響的行數(shù)為2；
• 如果存在記錄，并且更新的值和原有的值相同，則影響的行數(shù)為0。

注意：如果表同時(shí)存在多個(gè)唯一索引，只會(huì)根據(jù)第一個(gè)在數(shù)據(jù)庫(kù)中存在相應(yīng)value的列唯一索引做duplicate判斷。

二、示例表操作使用

t_user表結(jié)構(gòu)：表中有一個(gè)主鍵id、一個(gè)唯一索引idx_name；

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `user_name` varchar(30) NOT NULL  COMMENT '用戶名',
  `age` int NOT NULL DEFAULT '0' COMMENT '年齡',
  `height` int DEFAULT '0' COMMENT '身高cm',
  `type` int(1) DEFAULT NULL COMMENT '類型',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`user_name`) USING BTREE,
  KEY `idx_type` (`type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8  COMMENT='用戶表';

1、不存在記錄，插入的情況

INSERT into t_user(user_name, age, height) VALUES("lisi", 17, 180) on DUPLICATE KEY UPDATE age = 18;

2、存在記錄，可以更新字段的情況

INSERT into t_user(user_name, age, height) VALUES("lisi", 17, 180) on DUPLICATE KEY UPDATE age = 18;

3、存在記錄，不可以更新字段的情況

INSERT into t_user(user_name, age, height, type) VALUES("lisi", 18, 180, 1) on DUPLICATE KEY UPDATE age = 18;

4、存在多個(gè)唯一索引時(shí)

如果表同時(shí)存在多個(gè)唯一索引，只會(huì)根據(jù)第一個(gè)在數(shù)據(jù)庫(kù)中存在相應(yīng)value的列唯一索引做duplicate判斷。

1）數(shù)據(jù)庫(kù)中id = 2的記錄不存在，user_name="lisi"的記錄存在，所以會(huì)根據(jù)第二個(gè)唯一索引 user_name做duplicate判斷：執(zhí)行 update操作。

INSERT into t_user(id, user_name, age, height, type) VALUES(2, "lisi", 27, 280, 0) on DUPLICATE KEY UPDATE age = 28;

2）數(shù)據(jù)庫(kù)中id = 2的記錄不存在，user_name="lisisi"的記錄不存在，所以不存在重復(fù)鍵沖突：執(zhí)行 insert操作。

INSERT into t_user(id, user_name, age, height, type) VALUES(2, "lisisi", 27, 280, 0) on DUPLICATE KEY UPDATE age = 28;

3）數(shù)據(jù)庫(kù)中 id = 2的記錄存在，user_name="lisisi"的記錄存在，所以會(huì)根據(jù)第一個(gè)唯一索引id做duplicate判斷：執(zhí)行 update操作。

INSERT into t_user(id, user_name, age, height, type) VALUES(2, "lisisi", 37, 380, 1) on DUPLICATE KEY UPDATE age = 38;

4）數(shù)據(jù)庫(kù)中 id = 2的記錄存在，user_name="lisisi2"的記錄不存在，所以會(huì)根據(jù)第一個(gè)唯一索引id做duplicate判斷：執(zhí)行 update操作。

INSERT into t_user(id, user_name, age, height, type) VALUES(2, "lisisi2", 47, 480, 0) on DUPLICATE KEY UPDATE age = 48;

5、VALUES(col_name)使用

一般 Update子句可以使用 VALUES(col_name)獲取 insert部分的值。也是項(xiàng)目中使用最多的方式。

注意：VALUES()函數(shù)只在INSERT…UPDATE語(yǔ)句中有意義，其它時(shí)候會(huì)返回NULL。

INSERT into t_user(id, user_name, age, height, type) VALUES(2, "lisisi", 57, 480, 0) on DUPLICATE KEY UPDATE age = VALUES(age) + 100;

6、批量操作

批量操作之前表中數(shù)據(jù)如下：

批量語(yǔ)句如下：

INSERT INTO t_user(user_name, age, height, type) 
VALUES
("lisi", 71, 701, 0),
("lisisi", 72, 280, 1),
("zhangsan", 73, 703, 0),
("wangwu", 74, 704, null),
("laoliu", 75, null, null)
ON DUPLICATE KEY UPDATE 
user_name = VALUES(user_name),
age = VALUES(age),
height = VALUES(height),
type = VALUES(type);

批量語(yǔ)句執(zhí)行操作之后表中數(shù)據(jù)如下：

參考文章：

• 官方文檔：https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

總結(jié)

到此這篇關(guān)于MySQL中insert-on-duplicate語(yǔ)句的文章就介紹到這了,更多相關(guān)MySQL的insert-on-duplicate語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論