MySQL联合唯一索引相关问题以及在kratos项目中演示
演示项目的地址
https://gitee.com/huoyingwhw/kratos_rockscache.git
建立联合唯一索引的字段一定要设置为 NOT NULL
否则就会出现下面这样的“重复”数据:
插入时发现联合唯一索引冲突可以修改is_del
如果表中有软删除的标志,使用MySQL的 on duplicate key update 语句,当发现插入数据时有唯一索引冲突,在一些业务场景下我们其实是需要“恢复”数据的~此时更新is_del字段就好了~~这种方案的好处是,不用再在数据库中新增新的记录与字段了,节省了空间,而且绝大多数情况下,我们在业务中做联合唯一索引其实就是为了“防重复”的!但是这种“防重复”往往会跟软删除的逻辑冲突~~实际中绝大多数的情况都会是“新增一条原来已经软删除的数据”,这样倒不如直接将原来的数据设置为未删除~~当然这个是有业务场景限制的~大家根据实际的情况来决定用不同的方案吧~
data层片段代码:
func (g *greeterRepo) BatchCreateStudentCourses(ctx context.Context, scs []*biz.StudentCourse) error { db := g.data.Mysql.Table(biz.StudentCourseTableName).WithContext(ctx) db.CreateBatchSize = biz.BatchCreateSize // Notice 看实际的业务情况 // 唯一索引冲突 修改 is_del 字段 err := db.Clauses(clause.OnConflict{ Columns: []clause.Column{{Name: "student_id"}, {Name: "course_id"}}, DoUpdates: clause.Assignments(map[string]interface{}{ "is_del": biz.SCModelNotDeleted, }), }).Create(&scs).Error return err }
✅ 注意on duplicate key update语句如果发现没有“唯一索引”此时会新建多条相同的记录达不到效果!
所以,在使用 on duplicate key update 语句前,数据库必须提前创建好 联合唯一索引!
~~~
前面聊过如果表中有逻辑删除功能,不太好加唯一索引,但通过文中介绍的三种方案,可以顺利的加上唯一索引。 但来自灵魂的一问:如果某张表中,已存在历史重复数据,该如何加索引呢?最简单的做法是,增加一张防重表,然后把数据初始化进去。
可以写一条类似这样的SQL:
insert into product_unqiue(id,name,category_id,unit_id,model) select max(id), select name,category_id,unit_id,model from product group by name,category_id,unit_id,model;
这样做可以是可以,但今天的主题是直接在原表中加唯一索引,不用防重表。那么,这个唯一索引该怎么加呢?其实可以借鉴上一节中,增加id字段的思路。增加一个delete_id字段。不过在给product表创建唯一索引之前,先要做数据处理。获取相同记录的最大id:
select max(id), name,category_id,unit_id,model from product group by name,category_id,unit_id,model;
然后将delete_id字段设置成1。然后将其他的相同记录的delete_id字段,设置成当前的主键。这样就能区分历史的重复数据了。当所有的delete_id字段都设置了值之后,就能给name、model、delete_status和delete_id,四个字段加唯一索引了。
其他的方案
增加delete_id字段
其实,增加时间戳字段基本可以解决问题。但在在极限的情况下,可能还是会产生重复数据。有没有办法解决这个问题呢?
答:增加主键字段:delete_id。
该方案的思路跟增加时间戳字段一致,即在添加数据时给delete_id设置默认值1,然后在逻辑删除时,给delete_id赋值成当前记录的主键id。
把name、model、delete_status和delete_id,四个字段同时做成唯一索引。这可能是最优方案,无需修改已有删除逻辑,也能保证数据的唯一性。
我们都知道唯一索引非常简单好用,但有时候,在表中它并不好加。不信,我们一起往下看。通常情况下,要删除表的某条记录的话,如果用delete语句操作的话。例如:
delete from product where id=123;
这种delete操作是物理删除,即该记录被删除之后,后续通过sql语句基本查不出来。(不过通过其他技术手段可以找回,那是后话了)还有另外一种是逻辑删除,主要是通过update语句操作的。例如:
update product set delete_status=1,edit_time=now(3) where id=123;
逻辑删除需要在表中额外增加一个删除状态字段,用于记录数据是否被删除。在所有的业务查询的地方,都需要过滤掉已经删除的数据。通过这种方式删除数据之后,数据任然还在表中,只是从逻辑上过滤了删除状态的数据而已。
其实对于这种逻辑删除的表,是没法加唯一索引的。为什么呢?
假设之前给商品表中的name和model加了唯一索引,如果用户把某条记录删除了,delete_status设置成1了。后来,该用户发现不对,又重新添加了一模一样的商品。由于唯一索引的存在,该用户第二次添加商品会失败,即使该商品已经被删除了,也没法再添加了。这个问题显然有点严重。
有人可能会说:把name、model和delete_status三个字段同时做成唯一索引不就行了?
答:这样做确实可以解决用户逻辑删除了某个商品,后来又重新添加相同的商品时,添加不了的问题。但如果第二次添加的商品,又被删除了。该用户第三次添加相同的商品,不也出现问题了?由此可见,如果表中有逻辑删除功能,是不方便创建唯一索引的。 但如果真的想给包含逻辑删除的表,增加唯一索引,该怎么办呢?
通过前面知道,如果表中有逻辑删除功能,是不方便创建唯一索引的。其根本原因是,记录被删除之后,delete_status会被设置成1,默认是0。相同的记录第二次删除的时候,delete_status被设置成1,但由于创建了唯一索引(把name、model和delete_status三个字段同时做成唯一索引),数据库中已存在delete_status为1的记录,所以这次会操作失败。
我们为啥不换一种思考:不要纠结于delete_status为1,表示删除,当delete_status为1、2、3等等,只要大于1都表示删除。这样的话,每次删除都获取那条相同记录的最大删除状态,然后加1。 这样数据操作过程变成:
❓❓❓ 但是不断删除记录a时,怎么知道原来就是1、2、3,然后再做累加呢???
添加记录a,delete_status=0。
删除记录a,delete_status=1。
添加记录a,delete_status=0。
删除记录a,delete_status=2。
添加记录a,delete_status=0。
删除记录a,delete_status=3。
由于记录a,每次删除时,delete_status都不一样,所以可以保证唯一性。该方案的优点是:不用调整字段,非常简单和直接。 缺点是:可能需要修改sql逻辑,特别是有些查询sql语句,有些使用delete_status=1判断删除状态的,需要改成delete_status>=1。
增加时间戳字段
导致逻辑删除表,不好加唯一索引最根本的地方在逻辑删除那里。我们为什么不加个字段,专门处理逻辑删除的功能呢? 答:可以增加时间戳字段。
把name、model、delete_status和timeStamp,四个字段同时做成唯一索引在添加数据时,timeStamp字段写入默认值1。 然后一旦有逻辑删除操作,则自动往该字段写入时间戳。这样即使是同一条记录,逻辑删除多次,每次生成的时间戳也不一样,也能保证数据的唯一性。 时间戳一般精确到秒。除非在那种极限并发的场景下,对同一条记录,两次不同的逻辑删除操作,产生了相同的时间戳。这时可以将时间戳精确到毫秒。 该方案的优点是:可以在不改变已有代码逻辑的基础上,通过增加新字段实现了数据的唯一性。缺点是:在极限高并发的情况下,可能还是会产生重复数据。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
2020-05-31 刷题系列
2018-05-31 并发编程之——多线程基础