逻辑删除与业务主键

小半 • 2022年5月29日下午8:39 • 后端开发 • 阅读 206

假设某个表的业务主键列包括：col1、col2、col3，显然，它们不得为null。
有些公司有规定，程序里不允许物理删除（即delete from …），只能逻辑删除，即新增一个类似于yn_flag的列，1表示正常，0表示已删除。如何在表中建唯一键呢？
只使用一张表时，可以考虑：

约定某一行一旦被逻辑删除，就不能再还原了。即yn_flag列一旦由1变为0，就不能再由0变为1了。
约定更新时，只能更新yn_flag为1的，yn_flag为0的数据，只读，永远不会更新。
业务上可能多次删除，显然，col1、col2、col3、yn_flag这四列是不能作唯一键的。业务上有“停用”、“启用”需求的，可以考虑再新增一列，而不要使用yn_flag。
新增一列：alter table xxx add virtual_col VARCHAR(50) NULL DEFAULT NULL COMMENT ‘防重键’。
alter table xxx add unique index uniq_some (virtual_col);
新增记录时，virtual_col的值等于col1、col2、col3这三列的字符串加和，再Md5。显然，重复新增记录时，必然违反步骤5中定义的唯一约束。
逻辑删除时，先使用分布式ID生成器或UUID.randomUUID()生成一个值假设是N，更新：update … set yn_flag=‘0’, virtual_col=‘N’ where virtual_col=步骤6中的Md5 and yn_flag=‘1’。
再次新增记录时，重复步骤6。
再次逻辑删除时，重复步骤7。

使用两张表时，例如t_some_biz 和t_some_biz_history ，可以考虑：

约定t_some_biz表存放最新的数据，无yn_flag列，t_some_biz_history表存放被删除的历史数据。
t_some_biz表唯一键为 (col1, col2, col3)，t_some_biz_history无唯一键。
新增时，t_some_biz表里插入一条记录。为方便描述，假设其id为M。
再次新增，违反唯一约束，插入失败。
删除时，先删除t_some_biz表里id为M的记录，或者根据唯一键删除，即delete from where col1=xxx and col2=yyy and col3=zzz，若删除成功，则插入同样的数据到t_some_biz_history。
再次新增时，重复步骤3。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/17252.html