删库不必跑路，谈数据库删除设计
作者：杜哥
原文链接：https://my.oschina.net/yangyishe/blog/3138204
1 常见删除策略
凡是做业务逻辑系统, 总是离不开对删除逻辑的处理.
本文论述重点是伪删除, 即字段标示状态, 这是在一些中小型系统开发中的单据等较重要数据的主流做法.
但在此之前, 不妨先将常见删除策略列举一下:
数据库设置级联
这个我没太懂是怎么回事, 不过网上也说缺点较多, 很少用到, 在此就不考虑了触发器控制
-- 本文所写sql默认数据库均为mysql
CREATE TRIGGER `tg_bf_insert_t_product_only` BEFORE INSERT ON `t_product` FOR EACH ROW begin
insert into t_product_only (p_id,only_code) values (new.p_id,concat(new.group_code,',',new.p_code)) ;
end;
CREATE TRIGGER `tg_af_delete_product_only` AFTER DELETE ON `t_product` FOR EACH ROW begin
insert into t_product_deleted (p_id,group_code,p_code) values (old.p_id,old.group_code,old.p_code);
delete from t_product_only where p_id=old.p_id;
end;
优点是代码业务逻辑简单化, 且可以使用unique index,
缺点是对于一些级联数据的恢复不好控制, 如主表单和明细表单, 另在表结构变更的时候, 对于触发器的维护也是一件需要注意的事情.
3. 字段标示状态/伪删除(下文中将统称为伪删除)
即用状态表示已删除,如status=-1或者is_del=1,
之所以说是中小型系统开发的主流做法, 是因为对于重要数据, 为了控制风险, 不会直接将数据删除, 而使用触发器前面的缺点也说了, 维护十分不易.
中小型系统本来就有逻辑变更频繁, 以及数据量不会太高(单据数量几百万上千万, 但很少上亿)的特点, 权衡之下, 伪删除往往成为首选.
2 伪删除不同设计的优劣点
即使是伪删除, 也有几种不同的设计方式, 以下以财务系统中常常使用到的记账凭证为例, 介绍下几种伪删除的设计方案, 及实际中应当如何选择.
以下是现实生活当中的一张记账凭证图片.
由图片可以看出, 传统的记账凭证, 在数据库设计中, 至少需要两种表: 凭证主表和凭证明细表.
凭证主表记录凭证日期, 凭证字(如记), 凭证数(如图片右上角的1号)等信息,
凭证明细需要记录摘要, 会计科目, 借贷方向/借方金额/贷方金额(这三个内容在数据库表中至少需要保存2个).
实际凭证设计中, 可能还需要考虑辅助核算等信息, 本文简化处理不考虑这些.
由于凭证比较重要, 多数情况都是原始信息源(即不是可从其他数据中推导出的冗余), 故选择伪删除是较常见的.
2.1 状态设计字段的选择
如前文所述, 对于伪删除字段的选择, 一般有两种:
将凭证的常见状态全都放在一个字段中, 包含已保存status=0, 已审核status=1, 已删除status=-1;
将已删除单独设为一个字段, 如is_del, 删除状态时为0, 伪删除状态时为1.
第一种选择, 好处是状态字段只有一个, 且恰好凭证在已审核状态下是不允许被删除的, where约束起来比较简单.
-- voucher_month表示凭证月份,记账凭证均是以月为基本周期的
-- voucher_mark表示凭证字+凭证数的一种冗余,和凭证月联合起来用以表示某一公司的一张唯一记账
update fnc_voucher
set status=-1
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-1'
and status=0
第二种选择, 好处是将删除状态与正常的审批流程状态区分开, 使得两种逻辑得以解耦, 还有一种好处, 下文中会有提及.
is_audited是是否已审核的意思
update fnc_voucher
set is_del=1
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-1'
and is_audited=0
and is_del=0
虽然第一种选择的sql看起来更简短, 但个人还是建议第二种选择.
2.2 唯一索引的设计协同
设计数据库表格时, 一般建议是每一张数据库表格至少需设置一个唯一索引, 个别情况还需要设计多个唯一索引. 伪删除带来的状态标识字段增加, 可能会给唯一索引的设计带来一些影响.
当凭证不考虑伪删除的时候, 其唯一索引的设计方式如下:
alter table fnc_voucher
add unique key uk_voucher_cmm (company_id,voucher_month,voucher_mark);
当用户允许凭证断号时, 如在'记-7'和'记-9'之间允许存在一个空的凭证号时,以上的唯一索引仍能正常发挥作用.
但当用户不允许凭证断号(至少不允许自动断号,可以增加手动凭证弥补断号)时, 上面的唯一索引就不再符合逻辑. 当'记-8'凭证已伪删除时, 如果再增加一张同月的'记-8'凭证, 无疑会报duplicate key错误.
为了消除这种问题, 就需要将删除信息体现在唯一索引中.
这就是我建议将删除状态单独设置为一个字段的另一个原因: 当凭证被删除时, 不将is_del设为1, 而改为id值:
update fnc_voucher
set is_del=id
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-8'
and is_audited=0
and is_del=0
这样, 唯一索引就可设计为:
alter table fnc_voucher
add unique key uk_voucher_cmmd (company_id,voucher_month,voucher_mark,is_del);
对于均放在一个字段中的设计, 当然也可考虑将删除后的状态值设置为id的负值:
update fnc_voucher
set status=-id
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-8'
and status=0
只是这样做, 又会更进一步增加删除与正常流程的耦合性, 给以后的设计带来较大的困扰, 是不很建议这样做的. 只有当已经将删除设计为单字段混合状态时, 才考虑使用这种方法.
2.3 对凭证明细的影响
前文所述的重点, 都是在对凭证主表上, 而一旦考虑到凭证明细, 就又回出现新的问题.
查询凭证明细表的时候, 有两种选择:
根据主表的id查询;
select summary,subject_code,debit_amount,credit_amount
from fnc_voucher_detail
where company_id=1001
and voucher_id=12345
此sql理论上可以不在约束条件中加company_id限制,加只是为了格式统一
根据主表的凭证标识来查询;
select summary,subject_code,debit_amount,credit_amount
from fnc_voucher_detail
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-8'
每种都有各自的优势, 就笔者个人而言, 习惯使用第二种: 根据主表的凭证表示查询凭证明细, 但这就产生衍生了一个新的问题:
当一个凭证伪删除时, 且又生成了一个与已删除凭证标识相同的新凭证, 则新凭证就会共享已删除凭证的明细, 导致明细逻辑的错误!
解决这个问题, 就必须要在凭证明细上也增加删除状态标识, 当伪删除凭证时, 同时也对凭证明细进行伪删除处理.
update fnc_voucher_detail
set is_del=id
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-8'
and is_del=0;
select summary,subject_code,debit_amount,credit_amount
from fnc_voucher_detail
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='记-8'
and is_del=0;
如果使用根据id关联查询, 当然可以规避这种情况, 只是用id需要关联查询, 一般会带来一些效率上的差异.
具体使用哪种明细关联方式, 见仁见智吧.
扫描下方二维码，在公众号对话框回复关键字：redis，参与抽取3本《redis技术手册》技术书籍。