業(yè)務(wù)場(chǎng)景
- 我們經(jīng)常會(huì)遇到類似的業(yè)務(wù)場(chǎng)景，插入一條數(shù)據(jù)如果他不存在則執(zhí)行 insert ，當(dāng)這條記錄存在的時(shí)候，我們?nèi)?update 他的一些屬性（或者什么都不做）。
解決方案：
- 使用
ON DUPLICATE KEY UPDATE
在 主鍵 或者 唯一約束 重復(fù)時(shí)，執(zhí)行更新操作。 - 使用
REPLACE INTO
在 主鍵 或者 唯一約束 重復(fù)時(shí)，先 delete 再 insert。
ON DUPLICATE KEY UPDATE
- 創(chuàng)建表，建立唯一約束，準(zhǔn)備一條數(shù)據(jù)
CREATE TABLE `stu_class_ref` (
`id` varchar(30) NOT NULL,
`stu_id` varchar(30) DEFAULT NULL,
`class_id` varchar(30) DEFAULT NULL,
`note` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `stu_id` (`stu_id`,`class_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO
`test`.`stu_class_ref`(`id`, `stu_id`, `class_id`, `note`)
VALUES ('001', 'zhangsan', 'yuwen', NULL);
準(zhǔn)備數(shù)據(jù)
- 使用 ON DUPLICATE KEY UPDATE
INSERT INTO
`test`.`stu_class_ref`(`id`, `stu_id`, `class_id`, `note`)
VALUES (UUID_SHORT(), 'zhangsan', 'yuwen', '我喜歡語文:)')
ON DUPLICATE KEY UPDATE note = '我喜歡語文:)'
> Affected rows: 2
> 時(shí)間: 0.042s
-
Affected rows: 2，MySQL 檢查插入的行是否會(huì)產(chǎn)生重復(fù)鍵錯(cuò)誤，如果會(huì)則執(zhí)行update
ON DUPLICATE KEY UPDATE
- 如果想要引用 VALUES 中的值，參考如下
INSERT INTO
`test`.`stu_class_ref`(`id`, `stu_id`, `class_id`, `note`)
VALUES (UUID_SHORT(), 'zhangsan', 'yuwen', NULL)
ON DUPLICATE KEY UPDATE note = VALUES(class_id)
> Affected rows: 2
> 時(shí)間: 0.006s
ON DUPLICATE KEY UPDATE
REPLACE INTO
- MySQL 中 還有一個(gè)黑科技語法
REPLACE INTO
REPLACE INTO `stu_class_ref`(`id`, `stu_id`, `class_id`, `note`)
VALUES (UUID_SHORT(), 'zhangsan', 'yuwen', NULL)
> Affected rows: 2
> 時(shí)間: 0.004s
REPLACE INTO
-
REPLACE INTO就比較簡(jiǎn)單粗暴了，他會(huì)先執(zhí)行delete 操作，然后insert
ON DUPLICATE KEY UPDATE 與 REPLACE INTO
- 再來創(chuàng)建一張表, 創(chuàng)建三個(gè)唯一約束, 插入三條數(shù)據(jù)
CREATE TABLE `interesting` (
`id` varchar(30) NOT NULL,
`uni_a` varchar(30) DEFAULT NULL,
`uni_b` varchar(30) DEFAULT NULL,
`uni_c` varchar(30) DEFAULT NULL,
`version` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_a` (`uni_a`) USING BTREE,
UNIQUE KEY `uni_b` (`uni_b`) USING BTREE,
UNIQUE KEY `uni_c` (`uni_c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `test`.`interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`)
VALUES ('1', 'a', 'a', 'a', NULL);
INSERT INTO `test`.`interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`)
VALUES ('2', 'b', 'b', 'b', NULL);
INSERT INTO `test`.`interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`)
VALUES ('3', 'c', 'c', 'c', NULL);
準(zhǔn)備數(shù)據(jù)
- 執(zhí)行
ON DUPLICATE KEY UPDATE
INSERT INTO `interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`)
VALUES (UUID_SHORT(), 'a', 'b', 'c', NULL)
ON DUPLICATE KEY UPDATE version = 666
> Affected rows: 2
> 時(shí)間: 0.049s
ON DUPLICATE KEY UPDATE
-
Affected rows: 2但是其實(shí)三條主鍵都有沖突了
- 再看一下
REPLACE INTO
REPLACE INTO `interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`)
VALUES (UUID_SHORT(), 'a', 'b', 'c', NULL)
> Affected rows: 4
> 時(shí)間: 0.026s
REPLACE INTO
-
Affected rows: 4REPLACE INTO 將三條有沖突的全部delete 然后 insert
總結(jié)：
-
ON DUPLICATE KEY UPDATE只會(huì)對(duì)所匹配的第一行進(jìn)行update, -
REPLACE INTO會(huì)對(duì)所有匹配行進(jìn)行delete, insert - 所以應(yīng)避免對(duì)有多個(gè)唯一索引的表使用
