1 常見刪除策略

凡是做業(yè)務(wù)邏輯系統(tǒng), 總是離不開對刪除邏輯的處理.
本文論述重點(diǎn)是偽刪除, 即字段標(biāo)示狀態(tài), 這是在一些中小型系統(tǒng)開發(fā)中的單據(jù)等較重要數(shù)據(jù)的主流做法.
但在此之前, 不妨先將常見刪除策略列舉一下:

1. 數(shù)據(jù)庫設(shè)置級聯(lián)
   這個我沒太懂是怎么回事, 不過網(wǎng)上也說缺點(diǎn)較多, 很少用到, 在此就不考慮了

2. 觸發(fā)器控制

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;

優(yōu)點(diǎn)是代碼業(yè)務(wù)邏輯簡單化, 且可以使用unique index,
缺點(diǎn)是對于一些級聯(lián)數(shù)據(jù)的恢復(fù)不好控制, 如主表單和明細(xì)表單, 另在表結(jié)構(gòu)變更的時候, 對于觸發(fā)器的維護(hù)也是一件需要注意的事情.
3. 字段標(biāo)示狀態(tài)/偽刪除(下文中將統(tǒng)稱為偽刪除)
即用狀態(tài)表示已刪除,如status=-1或者is_del=1,
之所以說是中小型系統(tǒng)開發(fā)的主流做法, 是因?yàn)閷τ谥匾獢?shù)據(jù), 為了控制風(fēng)險, 不會直接將數(shù)據(jù)刪除, 而使用觸發(fā)器前面的缺點(diǎn)也說了, 維護(hù)十分不易.
中小型系統(tǒng)本來就有邏輯變更頻繁, 以及數(shù)據(jù)量不會太高(單據(jù)數(shù)量幾百萬上千萬, 但很少上億)的特點(diǎn), 權(quán)衡之下, 偽刪除往往成為首選.

2 偽刪除不同設(shè)計(jì)的優(yōu)劣點(diǎn)

即使是偽刪除, 也有幾種不同的設(shè)計(jì)方式, 以下以財務(wù)系統(tǒng)中常常使用到的記賬憑證為例, 介紹下幾種偽刪除的設(shè)計(jì)方案, 及實(shí)際中應(yīng)當(dāng)如何選擇.

以下是現(xiàn)實(shí)生活當(dāng)中的一張記賬憑證圖片.

image

由圖片可以看出, 傳統(tǒng)的記賬憑證, 在數(shù)據(jù)庫設(shè)計(jì)中, 至少需要兩種表: 憑證主表和憑證明細(xì)表.
憑證主表記錄憑證日期, 憑證字(如記), 憑證數(shù)(如圖片右上角的1號)等信息,
憑證明細(xì)需要記錄摘要, 會計(jì)科目, 借貸方向/借方金額/貸方金額(這三個內(nèi)容在數(shù)據(jù)庫表中至少需要保存2個).
實(shí)際憑證設(shè)計(jì)中, 可能還需要考慮輔助核算等信息, 本文簡化處理不考慮這些.

由于憑證比較重要, 多數(shù)情況都是原始信息源(即不是可從其他數(shù)據(jù)中推導(dǎo)出的冗余), 故選擇偽刪除是較常見的.

2.1 狀態(tài)設(shè)計(jì)字段的選擇

如前文所述, 對于偽刪除字段的選擇, 一般有兩種:

1. 將憑證的常見狀態(tài)全都放在一個字段中, 包含已保存status=0, 已審核status=1, 已刪除status=-1;

2. 將已刪除單獨(dú)設(shè)為一個字段, 如is_del, 刪除狀態(tài)時為0, 偽刪除狀態(tài)時為1.

第一種選擇, 好處是狀態(tài)字段只有一個, 且恰好憑證在已審核狀態(tài)下是不允許被刪除的, where約束起來比較簡單.

update fnc_voucher
set status=-1
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='記-1'
and status=0

第二種選擇, 好處是將刪除狀態(tài)與正常的審批流程狀態(tài)區(qū)分開, 使得兩種邏輯得以解耦, 還有一種好處, 下文中會有提及.

-- 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 唯一索引的設(shè)計(jì)協(xié)同

設(shè)計(jì)數(shù)據(jù)庫表格時, 一般建議是每一張數(shù)據(jù)庫表格至少需設(shè)置一個唯一索引, 個別情況還需要設(shè)計(jì)多個唯一索引. 偽刪除帶來的狀態(tài)標(biāo)識字段增加, 可能會給唯一索引的設(shè)計(jì)帶來一些影響.

當(dāng)憑證不考慮偽刪除的時候, 其唯一索引的設(shè)計(jì)方式如下:

alter table fnc_voucher
add unique key uk_voucher_cmm (company_id,voucher_month,voucher_mark);

當(dāng)用戶允許憑證斷號時, 如在'記-7'和'記-9'之間允許存在一個空的憑證號時,以上的唯一索引仍能正常發(fā)揮作用.
但當(dāng)用戶不允許憑證斷號(至少不允許自動斷號,可以增加手動憑證彌補(bǔ)斷號)時, 上面的唯一索引就不再符合邏輯. 當(dāng)'記-8'憑證已偽刪除時, 如果再增加一張同月的'記-8'憑證, 無疑會報duplicate key錯誤.

為了消除這種問題, 就需要將刪除信息體現(xiàn)在唯一索引中.
這就是我建議將刪除狀態(tài)單獨(dú)設(shè)置為一個字段的另一個原因: 當(dāng)憑證被刪除時, 不將is_del設(shè)為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

這樣, 唯一索引就可設(shè)計(jì)為:

alter table fnc_voucher
add unique key uk_voucher_cmmd (company_id,voucher_month,voucher_mark,is_del);

對于均放在一個字段中的設(shè)計(jì), 當(dāng)然也可考慮將刪除后的狀態(tài)值設(shè)置為id的負(fù)值:

update fnc_voucher
set status=-id
where company_id=1001
and voucher_month='2019-01'
and voucher_mark='記-8'
and status=0

只是這樣做, 又會更進(jìn)一步增加刪除與正常流程的耦合性, 給以后的設(shè)計(jì)帶來較大的困擾, 是不很建議這樣做的. 只有當(dāng)已經(jīng)將刪除設(shè)計(jì)為單字段混合狀態(tài)時, 才考慮使用這種方法.

2.3 對憑證明細(xì)的影響

前文所述的重點(diǎn), 都是在對憑證主表上, 而一旦考慮到憑證明細(xì), 就又回出現(xiàn)新的問題.
查詢憑證明細(xì)表的時候, 有兩種選擇:

1. 根據(jù)主表的id查詢;

select summary,subject_code,debit_amount,credit_amount
from fnc_voucher_detail
where company_id=1001
and voucher_id=12345
-- 此sql理論上可以不在約束條件中加company_id限制,加只是為了格式統(tǒng)一

1. 根據(jù)主表的憑證標(biāo)識來查詢;

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'

每種都有各自的優(yōu)勢, 就筆者個人而言, 習(xí)慣使用第二種: 根據(jù)主表的憑證表示查詢憑證明細(xì), 但這就產(chǎn)生衍生了一個新的問題:
當(dāng)一個憑證偽刪除時, 且又生成了一個與已刪除憑證標(biāo)識相同的新憑證, 則新憑證就會共享已刪除憑證的明細(xì), 導(dǎo)致明細(xì)邏輯的錯誤!

解決這個問題, 就必須要在憑證明細(xì)上也增加刪除狀態(tài)標(biāo)識, 當(dāng)偽刪除憑證時, 同時也對憑證明細(xì)進(jìn)行偽刪除處理.

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;

如果使用根據(jù)id關(guān)聯(lián)查詢, 當(dāng)然可以規(guī)避這種情況, 只是用id需要關(guān)聯(lián)查詢, 一般會帶來一些效率上的差異.

具體使用哪種明細(xì)關(guān)聯(lián)方式, 見仁見智吧.

end