hia~hia~hia~
本文首次发表于:链滴社区 (ld246.com)
本文采用:CC BY-SA 4.0
全文假设: 需要建立唯一性约束的字段为
name
,表示逻辑删除的字段为is_deleted
。
背景
当我在思考后端究竟是否需要在正式插入数据前查一次数据库对相关字段做个唯一性校验时,一个沉睡很久的问题再次从我脑壳里冒了出来:如何解决记录被逻辑删除后仍占用唯一性字段资源,从而导致的字段值无法复用的问题。
提醒:这是废话,不需要看!
像逻辑删除嘛,就是说通过将记录标记为“已删除”状态来模拟出删除的效果,而并非在物理级别上将数据彻底抹除。文件系统也是一样的,当你删掉一个文件后,那文件并没有彻底消失,它仅仅只是被标记为“已删除”然后默默地待在它原来的那个角落里只是你感知不到它的存在而已。这就解释了为什么传入一个大文件要很久而删除它却只要“嗖”的一下。(文件系统:因为我“压根就没删”啊!)
(啥?你说格式化?不好意思,格式化也一样的,不同之处仅仅在于格式化标记的是整个分区而非单个文件。)
那么怎样才能真正将数据抹除呢?那自然就是要进行“写入”的逆操作了(你之前怎么来的现在就得怎么走)。也就是要确保你被删文件所使用的那一块数据区域要被完整地再覆盖填充一次。至于填充什么东西,填 0 也好,填随机数据也罢,填一些新文件进来也不是问题。
所以要彻底删除一些文件,通常有以下思路:
- 最直接的办法还是老办法——格式化。不过与我们最常用的“快速格式化”不同,这个是“标准格式化”——真正的格式化。也就是格式化的同时会重新填充整个磁盘分区以彻底抹除分区数据,故格式化时间会比较长,表现就跟要写入一份有分区大小那么大的文件一样。
- 既然文件系统认为这文件已经被删除了,那这位置自然就不能就这么闲着(房东:这块地我又可以租给别人了),当有新文件要写进来时,要是看上了你这块宝地,说不定就拿来用了。(哦?又有新客人来了?欢迎欢迎~来里边请!)
- 虽然上面两种常见的情形确实可以达到抹除数据的目的,但不是白白浪费时间去抹了不需要抹的东西,就是抹除操作不在可控范围之内,要等数据被自然覆盖掉那你就等吧我也不知道啥时候会有新客人来你这办理入住。于是要定向抹除文件数据最可行的方法是——自己动手丰衣足食。(来人,把它给我拿下!)——通过专业的磁盘管理软件来彻底覆盖掉那些分配给文件的数据区域,比如 DiskGenius 软件就具备该功能。另外,一些管家软件附带的“文件粉碎机”功能也是同样的道理。甚至,即便你把数据覆盖掉了,通常还是会留下记录(哎这位客人,你这是想干啥呢,别以为换了身衣服我就不认得你了,你的入住登记表可是在我手上!)。即,要想让文件就像“从未存在过”一样,除了要把数据替换掉之外,还得把文件分配表里边的相关记录也给清掉。(算了,我还是选择格式化吧,不!干脆直接把硬盘锯掉得了“这才算得上是真正的「在物理上抹除」嘛!”就是删个文件而已,哪来那么多费事!)
不好意思扯远了~~
回到数据库的冲突之——逻辑删除与 UNIQUE 唯一性约束同时使用导致的“灵异事件”。
比如我插入一条记录,看他不爽我又把它给删掉了,用的是逻辑删除并且 name
字段使用了唯一索引。这时我尝试以同样的 name
值再次插入数据,从用户的角度来讲按理说之前那条记录已经删了现在应该可以正常插入才对,但数据库不这么认为。「你以为的你以为的就是你以为的!」
- 这时候你去找前端吧,前端说“查无此人”;
- 找后端吧,它告诉你:「没有一点点防备 也没有一丝顾虑 你就这样出现 在我的世界里 带给我惊喜 情不自已 / 可是你偏又这样 在我不知不觉中 悄悄地消失 从我的世界里没有音讯 剩下的只是回忆 / 你存在 我深深的脑海里 我的梦里 我的心里 我的歌声里」
- 而你去找数据库:「这谁是孙悟空啊?」
- 而你:「大王,我是小钻风啊。」
以前做毕业设计的时候遇到过该问题,但还没来得及解决。由于后端已经做了唯一性校验,就干脆暂时把数据库字段的唯一性约束给取消掉了。
但那样是远远不够的。现在再次注意到该问题,于是便试着寻找答案。
初步探索
首先自己想了下,要实现在允许逻辑删除的情况下保证字段的非空性、唯一性那就不能仅单纯地依赖于对那一个字段的唯一性约束。思路大概就是:
- 重命名被删除记录的唯一性字段。可以尝试在要删除记录时,对其唯一性字段进行某种“编码”处理,使得该字段值可以被其他记录复用。
- 打破单一唯一性。搞个组合索引,使得唯一性不单由那一个字段来完全控制,而是另请一位字段来辅助控制唯一性策略。
以上是我的思路。然后我在网上搜了下,看看网上的各位大神是怎么处理的。大致就是这么几种方案:
-
使
is_deleted
字段“已删除”情况下对应为NULL
值。并为name
、is_deleted
两字段建立 UNIQUE 组合索引。由于 UNIQUE 约束只约束“非 NULL”值,故当
is_deleted
字段为NULL
时,其便不再在约束范围内。 -
使
is_deleted
字段“已删除”情况下对应为非固定的值,比如主键的值、删除时间等能与name
组合成“唯一组合”的值。并为name
、is_deleted
两字段建立 UNIQUE 组合索引。由于未删除数据的
is_deleted
字段均为统一值,已删除数据的is_deleted
字段(与name
的组合)均为唯一值,故未删除的记录能正常受到约束且已删除的记录不会“诈尸”“霸座”。 -
不更改
is_deleted
字段的原有设计,而是新增一个字段is_unique
,使该字段与以上两种方案中某个is_deleted
字段的设计一致。并为name
、is_unique
两字段建立 UNIQUE 组合索引,删除记录时同时将这俩字段进行更改。逻辑删除时将
delete_time
值改为实际删除时间,从而保证已删除记录的这俩字段的组合一定与其他记录不同。 -
不采用逻辑删除,而是直接将被删除数据转移到其他地方。比如做一个“历史表”将原本应该删掉的记录转移到里面。
相关参考内容如下。
- SQL 约束 | 菜鸟教程 (runoob.com)
- 【数据库】字段逻辑删除的设计 - DeepInThought - 博客园 (cnblogs.com)
- Mybatis-Plus 中逻辑删除与唯一索引冲突的解决方案 (baobao555.tech)
- 如何解决逻辑删除与数据库唯一约束冲突 - 知乎 (zhihu.com)
可见,我的思路中忽略了一个事实:
NULL
值不在 UNIQUE 的管控范围内。即 UNIQUE 只保证那些“非空的值”是唯一存在的,而作为一种特殊的存在——“不存在”的代表,NULL
值可以自由地存在多份。(NULL:「我想要怒放的生命 就像飞翔在辽阔天空 就像穿行在无边的旷野 拥有挣脱一切的力量」,NULL:「我要飞得更高 飞得更高 狂风一样舞蹈 挣脱怀抱」,NULL:「哈哈哈哈哈皮不过我吧 啦啦啦啦啦啦啦啦啦啦啦」)
以上方案中前 3 种确实可行,能够确保未删记录的唯一性的同时使用逻辑删除。但是它们都各有各的弊端,使得我并不是特别希望采用它们。
- 方案 1、方案 2 都必须更改原有数据表字段的设计:方案 1 要求允许逻辑删除字段值为 NULL;方案 2 要求更改逻辑删除字段的数据类型,使得其与主键的数据类型一致或更改为某种特定的类型。
- 方案 2 由于删除时需分配给逻辑删除字段一个非固定的值,极大地增加了系统的复杂性,降低了可维护性。
- 方案 3 由于删除时需要同时操纵多个字段,也极大地增加了系统的复杂性,降低了可维护性。
- 方案 2 的设计,基本上可以说逻辑删除字段就是“死了之后还要抢别人的事做”。多存一份主键,或者存一份原本“更新时间”就能负责的“删除时间”显得很没有必要。并且这种能在一定程度上标识唯一性的东西占用空间还不小,比如主键通常使用 bigint 类型,需要占用 8 字节,删除时间即便是使用时间戳 timestamp 类型也需要占用 4 字节。
而方案 4 则与“初衷”不太相符,直接取消了逻辑删除策略。虽然在目标“历史表”不设置唯一性约束的情况下确实可以把数据转移过去,但把每个表都做两份并不是一个明智之举,并且谁能保证记录转移过程中不会出现问题?
总的来说:以上方案论谁最省事,那便是方案 1 和 方案 3,一个只需更改逻辑删除的“逻辑”,一个不会改变原来的字段设计。
最终,都要求后端来为其变动“买单”。
眼前一亮
正当我在考虑到底要如何决定我的最终表结构设计时,我盯着屏幕上的 Navicat,那一列整整齐齐的“虚拟”可选框引起了我的注意(这排面真不错)。
于是我搜了下这个东西。不搜不知道一搜吓一跳,这不正是我所需要的东西吗?!
相关参考内容如下。
- MySQL :: MySQL 8.0 Reference Manual :: 15.1.20.8 CREATE TABLE and Generated Columns
- MySQL :: MySQL 8.0 Reference Manual :: 15.1.20.9 Secondary Indexes and Generated Columns
- MySQL :: Generated Columns in MySQL 5.7.5
- Generated columns in MariaDB and MySQL – SQL And Its Sequels (ocelot.ca)
- MySQL 虚拟列 - JaxYoun - 博客园 (cnblogs.com)
- MySQL GENERATED COLUMN 生成列 (sjkjc.com)
生成列的定义
这是 MySQL 官网中对生成列的最新(8.0 版)介绍:
CREATE TABLE supports the specification of generated columns. Values of a generated column are computed from an expression included in the column definition.
CREATE TABLE 支持生成列的规范。生成列的值是根据列定义中包含的表达式计算的。
这是 MySQL 官网中一篇博客中对 5.7 版本的介绍:
There are two kinds of Generated Columns: virtual (default) and stored. Virtual means that the column will be calculated on the fly when a record is read from a table. Stored means that the column will be calculated when a new record is written in the table, and after that it will be treated as a regular field. Both types can have NOT NULL restrictions, but only a stored Generated Column can be be a part of an index.
有两种生成列:虚拟(默认)和存储。虚拟意味着当从表中读取记录时,将动态计算该列。存储意味着当向表中写入新记录时,该列将被计算,之后它将被视为常规字段。两种类型都可以有 NOT NULL 限制,但只有存储的生成列可以成为索引的一部分。
A:这东西怎么有点熟悉的感觉?响应式编程?
B:诶你先别说,我也有种说不出的熟悉感!
C:这让我想起了 Excel。
B:对对对!就是 Excel!
简单来说,「生成列」,是一种特殊的列——其值是根据列定义中包含的表达式计算得来的,并且是只读的。MySQL 5.7 就已经支持该功能了,有「虚拟列」、「存储列」两种形式,其中「虚拟列」不占用存储空间。并且 「生成列」支持被索引(MySQL 5.7 中「虚拟列」不可被索引) 。
生成列的定义语法:
AS (expr)
指示生成列并定义用于计算列值的表达式。 AS 前面可以有GENERATED ALWAYS
以使生成的列的性质更加明确。
VIRTUAL
或STORED
关键字指示如何存储列值,这对列的使用有影响:
VIRTUAL:不存储列值,而是在任何 BEFORE 触发器之后读取行时立即计算列值。虚拟列不占用存储空间。
- InnoDB 支持虚拟列上的二级索引。
STORED:插入或更新行时计算并存储列值。存储列确实需要存储空间并且可以建立索引。
如果没有指定关键字,则默认值为
VIRTUAL
。允许在表中混合使用
VIRTUAL
和STORED
列。
col_name data_type [GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
生成列的使用限制
注意:本部分根据 MySQL 官网对生成列的介绍翻译而来,机器翻译可能存在不准确之处,请以官网为准。
生成的列表达式必须遵守以下规则。如果表达式包含不允许的结构,则会发生错误。
-
允许使用文字、确定性内置函数和运算符。如果给定表中相同的数据,多次调用会产生相同的结果,而与连接的用户无关,则该函数是确定性的。不确定性且未通过此定义的函数示例:
CONNECTION_ID()
、CURRENT_USER()
、NOW()
。 -
不允许存储函数和可加载函数。
-
不允许使用存储过程和函数参数。
-
不允许使用变量(系统变量、用户定义变量和存储程序局部变量)。
-
不允许子查询。
-
生成的列定义可以引用其他生成的列,但只能引用表定义中较早出现的列。生成的列定义可以引用表中的任何基本(非生成)列,无论其定义发生得早还是晚。
即定义生成列的表达式中,如果引用了其他生成列,那么被引用的那个生成列必须比当前生成列“先定义” 。
-
AUTO_INCREMENT
属性不能在生成的列定义中使用。即生成列不可以设置“自动递增”属性。
-
AUTO_INCREMENT
列不能用作生成的列定义中的基列。 -
即生成列的定义中不能引用具备“自动递增”属性的列。
-
如果表达式求值导致截断或向函数提供了不正确的输入,则
CREATE TABLE
语句将因错误而终止,并且 DDL 操作将被拒绝。
开始折腾
由于这家伙与生俱来的牛逼特性,我感觉这玩意特别适合用来与需要唯一性约束的字段共同组建成 UNIQUE 组合索引。
比如:
当逻辑删除字段
is_deleted
使用0
代表正常、1
代表删除。此时增加一个虚拟列
is_unique
,依赖于if((is_deleted = 1),NULL,1)
表达式。如果觉得 IF 还不够给力,那 CASE 总该能够满足你吧:
(case is_deleted when 1 then NULL else 1 end)
(case when (is_deleted = 1) then NULL else 1 end)
然后为
name
、is_unique
两个字段组建 UNIQUE 组合索引。这样当
is_deleted
值为1
时,is_unique
的值为NULL
,即相当于“已删除的记录其name
值无需保持唯一性”;而当
is_deleted
值为其他值时,is_unique
的值为1
,即相当于“未删除的记录其name
值需要保持唯一性”。
使用效果
建表并插入测试数据
-- 建表
CREATE TABLE `test_table`
(
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(16) NOT NULL COMMENT '角色名称',
`code` varchar(16) NOT NULL COMMENT '角色代号',
`is_deleted` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否逻辑删除',
`is_unique` tinyint UNSIGNED GENERATED ALWAYS AS (if((`is_deleted` = 1), NULL,1)) VIRTUAL COMMENT '是否需要唯一性约束' NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `uk_name_is_unique`(`name` ASC, `is_unique` ASC) COMMENT '角色名称唯一(逻辑删除的不进行约束)',
UNIQUE INDEX `uk_code_is_unique`(`code` ASC, `is_unique` ASC) COMMENT '角色代号唯一(逻辑删除的不进行约束)'
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '角色' ROW_FORMAT = DYNAMIC;
-- 插入数据
INSERT INTO `test_table` VALUES (1, 'A01', 'admin01', 0, DEFAULT);
INSERT INTO `test_table` VALUES (2, 'A02', 'admin02', 0, DEFAULT);
测试“删除”逻辑
mysql> -- 查看表中全部记录
mysql> SELECT * FROM `test_table`;
+----+------+---------+------------+-----------+
| id | name | code | is_deleted | is_unique |
+----+------+---------+------------+-----------+
| 1 | A01 | admin01 | 0 | 1 |
| 2 | A02 | admin02 | 0 | 1 |
+----+------+---------+------------+-----------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql> -- 尝试更改记录的 is_unique 值
mysql> UPDATE `test_table` SET `is_unique` = NULL WHERE `id` = 1;
ERROR 3105 (HY000): The value specified for generated column 'is_unique' in table 'test_table' is not allowed.
mysql>
mysql>
mysql> -- 尝试插入一条 name 值为 A02 的记录(A02已被占用)
mysql> INSERT INTO `test_table` (`name`, `code`) VALUES ('A02', 'admin666');
ERROR 1062 (23000): Duplicate entry 'A02-1' for key 'test_table.uk_name_is_unique'
mysql>
mysql>
mysql> -- “删除”占用 name 值的那条记录
mysql> UPDATE `test_table` SET `is_deleted` = 1 WHERE `name` = 'A02';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql>
mysql> -- 检查“删除”效果
mysql> SELECT * FROM `test_table`;
+----+------+---------+------------+-----------+
| id | name | code | is_deleted | is_unique |
+----+------+---------+------------+-----------+
| 1 | A01 | admin01 | 0 | 1 |
| 2 | A02 | admin02 | 1 | NULL |
+----+------+---------+------------+-----------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql> -- 再次尝试插入
mysql> INSERT INTO `test_table` (`name`, `code`) VALUES ('A02', 'admin666');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> -- 检查插入
mysql> SELECT * FROM `test_table`;
+----+------+----------+------------+-----------+
| id | name | code | is_deleted | is_unique |
+----+------+----------+------------+-----------+
| 1 | A01 | admin01 | 0 | 1 |
| 2 | A02 | admin02 | 1 | NULL |
| 4 | A02 | admin666 | 0 | 1 |
+----+------+----------+------------+-----------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> -- 尝试“复活”已删除的那条 A02 记录
mysql> UPDATE `test_table` SET `is_deleted` = 0 WHERE `id` = 2;
ERROR 1062 (23000): Duplicate entry 'A02-1' for key 'test_table.uk_name_is_unique'
mysql>
mysql>
mysql> -- 把新插入的记录也“删掉”
mysql> UPDATE `test_table` SET `is_deleted` = 1 WHERE `id` = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql>
mysql> -- 检查“删除”效果
mysql> SELECT * FROM `test_table`;
+----+------+----------+------------+-----------+
| id | name | code | is_deleted | is_unique |
+----+------+----------+------------+-----------+
| 1 | A01 | admin01 | 0 | 1 |
| 2 | A02 | admin02 | 1 | NULL |
| 4 | A02 | admin666 | 1 | NULL |
+----+------+----------+------------+-----------+
3 rows in set (0.00 sec)
试了下,果然好用!
该方案:
-
无需对数据表原来存在的字段重新设计改造
-
只需新增一个生成列字段,该字段:
-
无需手动管理(也不可手动管理),其完全依赖于表达式的值自动生成。
即只需要在数据库层面稍加修改,完全不影响后端项目原来的“删除”逻辑,后端项目无需为此变更“买单”。
-
用一个字节的
tinyint
类型足矣。占用空间小,甚至用虚拟列还可以不占用磁盘空间。
-
-
“你们这些死去的东西应该归阎王管,不归我管。”非常符合“删除”逻辑的设计。(真香~)
我直呼完美!
妙啊!妙啊!
注意事项
-
如果硬是要给生成列字段填一个值,那就赋
DEFAULT
,反正也不会有任何效果。 -
貌似无法使一个列在普通列与生成列之间转换,或者在虚拟列与存储列之间转换。故如果确实需要进行转换,可以考虑:
或者先备份再导入
- 先将原来的列重命名
- 新建目标列,命名为之前那个列所使用的名称
- 将作用在原列上的索引转移到为目标列上,或者为目标列重建索引。
- 删除原列及其索引
-
支持生成列的存储引擎不多。比如:
- 大多数存储引擎不支持生成列。
- InnoDB、MyISAM 支持生成列,但 MyISAM 不支持虚拟列被索引。
- MySQL 5.7 中虚拟列不可被索引。
结论
DONE.🎉
欢迎来到这里!
我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。
注册 关于