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

安逸的咸鱼
353 阅读4分钟

「这是我参与2022首次更文挑战的第 4 天，活动详情查看：2022首次更文挑战」

上一篇 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 ROW
BEGIN
    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 ;

原创文章，未经允许，禁止转载

create by：安逸的咸鱼

avatar
文章
阅读
粉丝