【MySql 项目实战优化】复杂触发器案例分享

上一篇 mysql 的复习文讲到了 触发器 的基本操作，这篇，分享一个我在项目中遇到的案例

原始需求

负责人希望物料表中的数据，被其他人变更之后，系统内需要留下痕迹，比如，记录 1，在某时，由某人，更新了哪些数据，这些数据的旧值，以及新值，分别为什么

此外，负责人也需要知道 数据被更新了 这个动作，方便一些工作上的调整（跨部门の痛）

需求分析

直观的感受，当表在更新时，需要记录下相关的数据，这些数据最好是存放在一张新的，类似于日志的表中

考虑两种方案：

一、使用 aop

通过 aop ，切面定位到对应的 xxMapper.update 方法上，当 java 执行更新操作之前，aop 也开始工作，这个时候的工作逻辑如下：

1. 方法调入进入 update，触发 aop 切面

2. 在 aop 中，先去 DB 对应的表获取对应记录现有的数据

3. 对比新、旧两份数据中的各个属性的值，记录下变动的数据

4. 将变动的数据，记录到库

5. 美化数据显示格式，将这份数据发送到负责人

这份方案，基本是在 java 中实现此项功能，谈不上好坏，中规中矩

二、使用触发器

整个的逻辑和上个方案差不多，同样的需要新旧值的对比+记录，以及通知到人

不过，这个是数据库层面的，而且触发器在被触发的时候，就可以直接拿到两版数据，并且，在 mysql 中也支持一些逻辑的操作

最终，考虑采用方案 2，无他，对源码的耦合性较低，用不上去 java 中添加 aop 代码和处理逻辑

设计日志表

日志表的核心要素：

• 谁干的

• 动了哪条记录

• 操作的时间

• 变动的数据

• 这条日志记录是否处理过（如发送过通知）

最终的建表 sql 见下：

drop table if EXISTS mdm_basics_change_log;create table mdm_basics_change_log(    id BIGINT PRIMARY key auto_increment COMMENT 'id',    mdm_code varchar(50) COMMENT '物料编码',    update_by varchar(20) COMMENT '更新人账号',    update_name varchar(20) COMMENT '更新人姓名',    update_date TIMESTAMP DEFAULT now() COMMENT '更新的日期',    change_data LONGTEXT COMMENT '变更的数据',    notified int default 0 COMMENT '是否已经通知过负责人');

触发器功能分析

由于需要记录下数据有 val1 -> val2 这个状态，那只能是在更新之前触发

由于物料表中的字段过多（70+列），并且不一定会全用用上，不方便在日志表里做一一映射存储，比如记录 1 用了 col1, col2 ，记录 2 用了 col1, col3，那么一个合适的方案就是，以字符串的形式，存储变动的数据，那么存储的格式就定义为如下：

字段1_旧值_新值字段3_旧值_新值

触发器在执行的时候，需要挨个去校验我们关注的字段新旧两个值，是否一致，不一致的情况下，需要记录下来，追加到临时的字符串变量 str 上

等全部检查完成后，我们通过判断 str 的值是否为 ''，是则没必要记录到日志表

在通知这个环节，直接对这个字符串做截取+格式化显示后，再通过邮件的方式发送给负责人即可 本文不会关注这块细节

触发器编写

由上述的分析，可以明确触发器的主体会有许多条 sql 操作，因此需要用到 begin end 代码块

需要用到一个字符串类型的 str 变量接收拼接的结果

需要使用 IF 判断关键字段新旧值是否有变化，只有在变化的情况下，才需要拼接到 str 变量中

处理好拼接在 str 的数据后，再通过条件判断，是否需要将该记录插入到日志表中

最后触发器的成品效果如下（其中省略了大量篇幅的 if 新旧值判断）：

DELIMITER //drop TRIGGER if EXISTS trigger_recordMdmBasicsUpdate ;CREATE TRIGGER trigger_recordMdmBasicsUpdate BEFORE UPDATE ON mdm_basics FOR EACH ROWBEGIN    DECLARE str LONGTEXT DEFAULT '';    DECLARE split_op LONGTEXT DEFAULT ',,.';  IF old.mdm_name!=new.mdm_name THEN    set str = CONCAT_WS(split_op,'物料名称',old.mdm_name,new.mdm_name,'\n');  END IF;  IF old.mdm_note!=new.mdm_note THEN    set str = CONCAT_WS(split_op,'物料描述(中文)',old.mdm_note,new.mdm_note,'\n');  END IF;  if str != '' and old.mdm_code != '' then     insert into mdm_basics_change_log(mdm_code,update_by,update_name,change_data) VALUES (new.mdm_code,new.update_by,new.update_name,str);  end if;END//DELIMITER ;