我发现了一个可以解决逻辑删除与唯一字段间矛盾的新方案

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 值改为实际删除时间，从而保证已删除记录的这俩字段的组合一定与其他记录不同。
不采用逻辑删除，而是直接将被删除数据转移到其他地方。比如做一个“历史表”将原本应该删掉的记录转移到里面。

眼前一亮

正当我在考虑到底要如何决定我的最终表结构设计时，我盯着屏幕上的 Navicat，那一列整整齐齐的“虚拟”可选框引起了我的注意（这排面真不错）。

title=

于是我搜了下这个东西。不搜不知道一搜吓一跳，这不正是我所需要的东西吗？！

生成列的定义

这是 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，反正也不会有任何效果。
貌似无法使一个列在普通列与生成列之间转换，或者在虚拟列与存储列之间转换。故如果确实需要进行转换，可以考虑：
或者先备份再导入
1. 先将原来的列重命名
2. 新建目标列，命名为之前那个列所使用的名称
3. 将作用在原列上的索引转移到为目标列上，或者为目标列重建索引。
4. 删除原列及其索引
支持生成列的存储引擎不多。比如：
- 大多数存储引擎不支持生成列。
- InnoDB、MyISAM 支持生成列，但 MyISAM 不支持虚拟列被索引。
- MySQL 5.7 中虚拟列不可被索引。

结论

DONE.🎉

我发现了一个可以解决逻辑删除与唯一字段间矛盾的新方案

背景

初步探索

眼前一亮

生成列的定义

生成列的使用限制

开始折腾

使用效果

注意事项

结论

多云转晴

引用和评论

关于（小米）手机电池容量测量值的个人看法

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

使用 MongoDB 构建 AI：Gradient Accelerator Block 如何在几秒钟内让您从零开发 AI

MongoDB Atlas与YoMio.AI近乎完美适配:推理更快速、查询更灵活、场景更丰富

MongoDB 8.0已全面可用