如何优雅的向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条数据。