如何優(yōu)雅的向MySQL唯一索引列增加值
文章簡介
在日常開發(fā)中，我們會經(jīng)常遇到某一張表中某列或者多列的值是唯一的，不能重復(fù)插入同一個(gè)值。遇到這樣的設(shè)計(jì)，我們一般會設(shè)置一個(gè)unique的索引。也就是在要求值不能是重復(fù)的列或者多列上添加一個(gè)唯一索引。例如，會執(zhí)行這一條SQL語句：
alter?table?table_name?add?unique?[index_name]?(col_name(lenght))
或者
create unique index index_name on table_name(col_name(length))
這兩條語句都表示給表中創(chuàng)建一條唯一索引的字段。
當(dāng)我們創(chuàng)建好唯一索引之后，如果給索引列插入了重復(fù)值之后，MySQL會報(bào)一個(gè)下列的錯(cuò)誤信息。
insert into userinfo(nickname, openid) VALUE (ENCRYPT(RAND() * 1000), 'jf/IxWYA060PA');
-- 具體得錯(cuò)誤信息。
Duplicate entry 'jf/IxWYA060PA' for key 'ixd_openid'
表示不能在索引ixd_openid列上重復(fù)插入值
jf/IxWYA060PA。
針對這種情況，我們在業(yè)務(wù)代碼中，可以直接根據(jù)改錯(cuò)誤信息來做具體得處理。同時(shí)也可以讓MySQL層面對該情況做處理。下面針對該情況做一個(gè)實(shí)際案例的顏值。
表結(jié)構(gòu)
首先我們創(chuàng)建一張userinfo表，表中結(jié)構(gòu)如下：
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;
方案實(shí)現(xiàn)
先查詢在插入
按照常規(guī)的邏輯，我們先查詢索引列的值是否存在，如果不存在則插入，存在則返回用戶信息。這種方式操作簡單，但是在并發(fā)情況下，就會存在問題。
-- 先執(zhí)行查詢操作
select openid from userinfo where openid = 'jf/IxWYA060PA';
-- 如果MySQL返回空，表示數(shù)據(jù)不存在則執(zhí)行插入操作
insert into userinfo(nickname, openid) VALUE (ENCRYPT(RAND() * 1000), 'jf/IxWYA060PA');
使用ignore
當(dāng)我們重復(fù)插入數(shù)據(jù)時(shí)，MySQL會返回一個(gè)Duplicate entry xxx for xxx的信息，表示該列重復(fù)。適用ignore就會忽略該錯(cuò)誤信息，只是MySQL不會做插入操作。
insert ignore into userinfo(nickname, openid) VALUE (ENCRYPT(RAND() * 1000), 'jf/IxWYA060PA');
增加ignore之后，出現(xiàn)重復(fù)插入的情況，MySQL會返回Affected rows: 0。只是插入的數(shù)據(jù)為條數(shù)0，并且id內(nèi)部也會自增，導(dǎo)致id的值不是連續(xù)的。此時(shí)我們增加一條不重復(fù)的數(shù)據(jù)，在來查詢數(shù)據(jù)表，就會發(fā)現(xiàn)id字段不是連續(xù)的。
on duplicate key update
使用該方式插入，當(dāng)存在重復(fù)插入的情況下，MySQL同樣的不會返回重復(fù)插入的信息。
insert into userinfo(nickname, openid) VALUE (ENCRYPT(RAND() * 1000), 'jf/IxWYA060PA') on duplicate key update openid = 'jf/IxWYA060PA';
此時(shí)MySQL會返回一個(gè)Affected rows: 0信息。只是插入的數(shù)據(jù)為條數(shù)0，并且id內(nèi)部也會自增，導(dǎo)致id的值不是連續(xù)的。
replace
使用該方式，會將原來存在的數(shù)據(jù)進(jìn)行刪除，然后新增一條數(shù)據(jù)。
replace into userinfo(nickname, openid) value(ENCRYPT(RAND() * 1000), 'jf/IxWYA060PA');
此時(shí)MySQL會返回一個(gè)Affected rows: 2信息。這里返回2。表示你1條數(shù)據(jù)被刪除的數(shù)據(jù)，另外新增1條數(shù)據(jù)。
推薦閱讀
Mysql百萬級數(shù)據(jù)遷移實(shí)戰(zhàn)筆記
開發(fā)人員必備的MySQL事務(wù)原理分析與總結(jié)