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

共 2195字,需浏览 5分钟

 ·

2021-07-20 04:29

文章简介

在日常开发中,我们会经常遇到某一张表中某列或者多列的值是唯一的,不能重复插入同一个值。遇到这样的设计,我们一般会设置一个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不会做插入操作。2e6a78a9588d2f55b16dff0275ed2673.webp

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

增加ignore之后,出现重复插入的情况,MySQL会返回Affected rows: 0。只是插入的数据为条数0,并且id内部也会自增,导致id的值不是连续的。此时我们增加一条不重复的数据,在来查询数据表,就会发现id字段不是连续的。a1bb9cd766dbb1ea578d7adfb88c1c67.webp

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条数据。

推荐阅读

Mysql百万级数据迁移实战笔记

彻底讲明白MySQL的乐观锁和悲观锁

这一次终于有人把MySQL主从复制讲全面了

开发人员必备的MySQL事务原理分析与总结



浏览 74
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报