巧用*_his表記錄操作歷史

文章轉載自「開發者圓桌」一個關于開發者入門、進階、踩坑的微信公眾號

許多OLTP應用的開發者都知道，一些重要的操作要記錄操作歷史，把操作前的數據備份到歷史表，然后再執行相應的修改操作。這樣可以獲取某個時點的操作日志和操作前的記錄值。

要記錄操作歷史有兩個層面，一個是應用層即通過應用程序邏輯實現歷史記錄的保存，一個是數據庫層即數據庫歸檔、審計等DBA管理的功能。這里僅討論前者。

比如有一個用戶表t_user包含id,name,sex,age字段，因為t_user表的修改非常重要，要記錄操作歷史，一般的做法是建立和t_user結構相同的一個歷史表命名為t_user_his，它包含id,name,sex,age字段，同時添加對應的his_id,oper_time,oper_remark等記錄操作說明的字段。

OK，表設計完畢了，我們需要在修改時記錄用戶之前的信息，一般的做法是先來個insert插入歷史表，然后再去修改原表數據，把這兩個操作放到同一個事務中去處理，釋義代碼如下：

setAutoCommit(false);

insert into t_user_his(id,name,sex,age,his_id,oper_time,oper_remark) select id,name,sex,age,'歷史表id',timestamp,'修改姓名' from t_user where id=18970；

update t_user set name='xxx' where id=18970；

commit();

部署上線一切運行正常，領導非常滿意，突然有一天需要在t_user中添加一個phone字段來記錄用戶的電話號碼，軟件產品唯一不變的就是下面這個詞

沒辦法只能修改了，需要按照下面幾個步驟修改：

首先，修改t_user表添加一個phone字段。

其次，修改t_user_his表添加一個phone字段。

第三，修改涉及到的記錄操作歷史的SQL，如果有多處也需要一并修改：

setAutoCommit(false);

insert into t_user_his(id,name,sex,age,phone,his_id,oper_time,oper_remark) select id,name,sex,age,phone,'歷史表id',timestamp,'修改姓名' from t_user where id=18970；

update t_user set name='xxx' where id=18970；

commit();

最后，編譯程序，測試，提交，部署上線，一個流程下來可能要好多天。

問題來了，只能這樣處理嗎？如何規避字段修改帶來的重復修改呢？有沒有更好的解決方案呢？答案是肯定的，只需要通過一個簡單的修改就可以做到。

我們可以這樣來設計t_user_his表的字段，把歷史操作相關字段前置，后續字段保持與t_user完全一致，例如t_user_his字段可以這樣安排：his_id,oper_time,oper_remark,id,name,sex,age。

應用程序修改如下：

setAutoCommit(false);

insert into t_user_his select ,'歷史表id',timestamp,'修改姓名' ,a.* from t_user a  where id=18970；

update t_user set name='xxx' where id=18970；

commit();

這樣的話，要添加一個新的phone字段，該如何操作呢？

首先，修改t_user表添加一個phone字段。

其次，修改t_user_his表添加一個phone字段與t_user字段順序一致。

好了，兩步就可以實現字段的自由添加、修改或者刪除，不需要修改任何應用程序代碼，也不再需要復雜的部署、測試、上線流程，僅在數據庫中完成操作就可以了。

上述方法，修改一個字段就要記錄整條數據到歷史表，會占用較多的空間，要么就把字段寫死。具體如何選擇還是要分析系統的業務要求，不可盲目照搬。