如何優雅的向MySQL唯一索引列增加值

文章簡介

在日常開發中，我們會經常遇到某一張表中某列或者多列的值是唯一的，不能重複插入同一個值。遇到這樣的設計，我們一般會設定一個unique的索引。也就是在要求值不能是重複的列或者多列上新增一個唯一索引。例如，會執行這一條SQL語句：

alter table table_name add unique [index_name] (col_name(lenght))

或者

create unique index index_name on table_name(col_name(length))

這兩條語句都表示給表中建立一條唯一索引的欄位。

當我們建立好唯一索引之後，如果給索引列插入了重複值之後，MySQL會報一個下列的錯誤資訊。

insert into userinfo(nickname, openid) VALUE (ENCRYPT(RAND() * 1000), 'jf/IxWYA060PA');
-- 具體得錯誤資訊。
Duplicate entry 'jf/IxWYA060PA' for key 'ixd_openid'

表示不能在索引ixd_openid列上重複插入值jf/IxWYA060PA。

針對這種情況，我們在業務程式碼中，可以直接根據改錯誤資訊來做具體得處理。同時也可以讓MySQL層面對改情況做處理。下面針對該情況做一個實際案例的顏值。

表結構

首先我們建立一張userinfo表，表中結構如下：

CREATE TABLE `demo`.`userinfo`  (
  `id` int(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `openid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `ixd_openid`(`openid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

方案實現

先查詢在插入

按照常規的邏輯，我們先查詢索引列的值是否存在，如果不存在則插入，存在則返回使用者資訊。這種方式操作簡單，但是在併發情況下，就會存在問題。

-- 先執行查詢操作
select openid from userinfo where openid = 'jf/IxWYA060PA';
-- 如果MySQL返回空，表示資料不存在則執行插入操作
insert into userinfo(nickname, openid) VALUE (ENCRYPT(RAND() * 1000), 'jf/IxWYA060PA');

使用ignore

當我們重複插入資料時，MySQL會返回一個Duplicate entry xxx for xxx的資訊，表示該列重複。適用ignore就會忽略該錯誤資訊，只是MySQL不會做插入操作。

insert ignore into userinfo(nickname, openid) VALUE (ENCRYPT(RAND() * 1000), 'jf/IxWYA060PA');

增加ignore之後，出現重複插入的情況，MySQL會返回Affected rows: 0。只是插入的資料為條數0，並且id內部也會自增，導致id的值不是連續的。此時我們增加一條不重複的資料，在來查詢資料表，就會發現id欄位不是連續的。

on duplicate key update

使用該方式插入，當存在重複插入的情況下，MySQL同樣的不會返回重複插入的資訊。

insert  into userinfo(nickname, openid) VALUE (ENCRYPT(RAND() * 1000), 'jf/IxWYA060PA') on duplicate key update openid = 'jf/IxWYA060PA';

此時MySQL會返回一個Affected rows: 0資訊。只是插入的資料為條數0，並且id內部也會自增，導致id的值不是連續的。

replace

使用該方式，會將原來存在的資料進行刪除，然後新增一條資料。

replace into userinfo(nickname, openid) value(ENCRYPT(RAND() * 1000), 'jf/IxWYA060PA');

此時MySQL會返回一個Affected rows: 2資訊。這裡返回2。表示你1條資料被刪除的資料，另外新增1條資料。

本作品採用《CC 協議》，轉載必須註明作者和本文連結