背景
实际业务中大多数的表采用的都是逻辑删除,而许多业务字段比如编码之类的都要求唯一,这个时候不能使用唯一索引来做,因为可能会存在删除再次添加的情况。
1 解决思路
代码中肯定做了对字段唯一性的校验,例如设备的编码不能重复,在代码中一定是做了校验,当编码重复时会提示用户。但是这个校验并不能完全保证唯一性,在并发的条件下会存在校验逻辑查询的时候不存在这个编码,但是保存之后发现编码重复的情况,这是并发带来的问题,只依靠代码无法规避,需要去数据库层面着手。
我们很容易想到使用唯一索引来限制编码的唯一性,但是逻辑删除的前提下,这个行不通。从MySQL5.7开始,支持一个叫做 生成列
的东西。语法举例如下:
CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),birthdate DATE,age INT AS (TIMESTAMPDIFF(YEAR, birthdate, CURDATE())) STORED, -- 生成列,存储计算后的年龄full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL -- 生成列,虚拟生成全名
);
编码上创建唯一索引会因为逻辑删除的问题冲突,那么我们就创建一个只有未删除状态的生成列,这样就满足了唯一性的要求,当设备被删除的时候,这条记录的这个生成列就会自动由编码变为 null ,完美。
2 动手实践
我们可以使用它来实现逻辑删除下的字段唯一,以上面的设备编码为例,表名为 device ,编码字段 code ,假设现在 device 表已存在,那么实现的 sql 如下:
-- 添加未删除编码code生成列
ALTER TABLE deviceADD COLUMN code_not_deleted varchar(32) GENERATED ALWAYS AS (CASE WHEN is_deleted = 0 THEN code END) STORED;
-- 创建唯一索引
CREATE UNIQUE INDEX device_idx_unique_code_not_deleted ON device (code);
这样就实现了,逻辑删除的前提下,设备表中编码的唯一。