? ? ? ? 最近項目中遇到這么個需求，往數(shù)據(jù)表中批量插入一批數(shù)據(jù)，由于唯一索引，導(dǎo)致表中某條數(shù)據(jù)可能已經(jīng)存在。

? ? ? ? 雖然在插入之前可以先用 select 判斷數(shù)據(jù)是否存在，如果不存在就 insert，否則 update。這樣對于每條數(shù)據(jù)都得至少兩次連庫操作，效率較低。

? ? ? ? 并且，在批量插入之前，可能數(shù)據(jù)并不存在，但是在批量插入過程中，唯一索引會導(dǎo)致部分?jǐn)?shù)據(jù)即使有兩條也只會插入一條。而事實上，我們希望看到的是，第一條插入后，當(dāng)?shù)诙l再插入時，根據(jù)唯一索引該條記錄就變成 update 而不是僅僅 insert 失敗或者忽略，所以也不能加關(guān)鍵字 ignore。

? ? ? ? 這里 mysql 關(guān)鍵字 on duplicate key update 就可以完美地實現(xiàn)這部分需求。實驗如下，先看下表結(jié)構(gòu)：

CREATE TABLE `test_grade` (`id` BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名', `course` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '課程',`score` SMALLINT(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '分?jǐn)?shù)',PRIMARY `test_grade`KEY (`id`),UNIQUE KEY `idx_name_course` (`name`,`course`) ) ENGINE=INNODB DEFAULT CHARSET=utf8

? ? ? ? 列 name 和 course 構(gòu)成唯一索引。目前表中數(shù)據(jù)如下：

mysql> select * from test_grade; +----+-------+--------+-------+ | id | name | course | score | +----+-------+--------+-------+ | 1 | bob | 數(shù)學(xué) | 82 | | 2 | allen | 語文 | 76 | +----+-------+--------+-------+ 2 rows in set (0.00 sec)

? ? ? ? 此時如果直接執(zhí)行下面語句：

mysql> INSERT INTO test_grade(NAME, course, score) VALUES('bob', '數(shù)學(xué)', 95); ERROR 1062 (23000): Duplicate entry 'bob-數(shù)學(xué)' for key 'idx_name_course'

? ? ? ? 加上關(guān)鍵字?on duplicate key update 試試：

mysql> INSERT INTO test_grade(NAME, course, score) VALUES('bob', '數(shù)學(xué)', 95) on duplicate key update score = values(score); Query OK, 2 rows affected (0.09 sec) mysql> mysql> select * from test_grade; +----+-------+--------+-------+ | id | name | course | score | +----+-------+--------+-------+ | 1 | bob | 數(shù)學(xué) | 95 | | 2 | allen | 語文 | 76 | +----+-------+--------+-------+ 2 rows in set (0.00 sec)

? ? ? ? 盡管唯一索引已經(jīng)存在，但仍然執(zhí)行成功，score 值也更新為95。如果同時插入兩條相同唯一索引數(shù)據(jù)呢：

mysql> INSERT INTO test_grade(NAME, course, score) VALUES('jack', '物理', 88), ('jack', '物理', 85); ERROR 1062 (23000): Duplicate entry 'jack-物理' for key 'idx_name_course' mysql> mysql> select * from test_grade; +----+-------+--------+-------+ | id | name | course | score | +----+-------+--------+-------+ | 1 | bob | 數(shù)學(xué) | 93 | | 2 | allen | 語文 | 76 | +----+-------+--------+-------+ 2 rows in set (0.00 sec)

? ? ? ? 可以看到兩條都 insert 失敗，加上 on duplicate key update 試試：

mysql> INSERT INTO test_grade(NAME, course, score) VALUES('jack', '物理', 88), ('jack', '物理', 85) on duplicate key update score = values(score); Query OK, 3 rows affected (0.36 sec) Records: 2 Duplicates: 1 Warnings: 0mysql> select * from test_grade; +----+-------+--------+-------+ | id | name | course | score | +----+-------+--------+-------+ | 1 | bob | 數(shù)學(xué) | 93 | | 2 | allen | 語文 | 76 | | 9 | jack | 物理 | 85 | +----+-------+--------+-------+ 3 rows in set (0.00 sec)

? ? ? ? 可以看到 insert 成功了，并有提示重復(fù)數(shù)為1，而且 score 值也是最后一次插入時的值。

? ? ? ? 至此，我們的需求就得以實現(xiàn)。不過也得注意到，此時主鍵 id 并不是連續(xù)增長的了。

?

?

總結(jié)

以上是生活随笔為你收集整理的mysql批量插入on duplicate key update的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。