mysql触发器实时检测一条语句进行备份删除思路详解

吾爱主题阅读：143 2024-04-02 08:06:02 评论：0

问题描述：用户有一个这样一个需求，在一张表里会不时出现 “违规” 字样的字段，需要在出现这个字段的时候，把整行的数据删掉。这是个采集任务，如果发现有“违规”字样的数据，会整点或者什么时间进行统一上报，也无法对源头进行控制让这种数据不生成。

现在需要实现以下需求：

1.实时检测这条数据的产生，发现后删除

2.在删除之前作备份这条数据

解决思路：

需要明确解决思路，

1.首先是如何实时探测删除？询问开发，这条数据的生成方式为insert，就可以做一个当表做插入的时候，然后做一个after insert 做delete数据的触发器

2.如何进行备份？何种方式备份？能不能备份到一个表里，这个表里记录每次插入的时间，建立这个备份表可以取与原表结构基本相同，但是备份表要删除原表的自增属性，主键，外键等属性，新增一个时间戳字段，方便记录每次备份数据的时间，删除以上属性是为了能够把数据写入备份表中

3.如何在删除之前做备份呢？一开始想的是放到一个触发器就行，先把数据进行备份，然后后面跟着一条删除，测试的时候行不通

测试方案：

先准备一些测试数据和测试表

1.建立测试数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> show create table student; + ---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + ---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `Sno` char (9) NOT NULL , `Sname` char (20) NOT NULL , `Ssex` char (2) DEFAULT NULL , `Sage` smallint DEFAULT NULL , `Sdept` char (20) DEFAULT NULL , PRIMARY KEY (`Sno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci | + ---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)

2.建立备份表。查看建表语句，正式环境是不知道原表的表结构的，需要有改原表结构，并创建新备份表的操作的

原表建表语句

3.准备备份语句，删除语句，插入测试语句

备份语句（因为备份表多一个时间戳的字段，所以备份语句要做修改一下）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> show create table student_bak; + -------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + -------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student_bak | CREATE TABLE `student_bak` ( `Sno` char (9) NOT NULL , `Sname` char (20) NOT NULL , `Ssex` char (2) DEFAULT NULL , `Sage` smallint DEFAULT NULL , `Sdept` char (20) DEFAULT NULL , `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci | + -------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

备份效果

插入测试语句：

1	`insert` `into` `student` `values` `(` `'201215124'` `,` `'张三'` `,` `'男'` `,20,` `'EL'` `);`

删除语句（删除数据一定要写精准了，到时候删什么，就要备份什么）

1	`delete` `from` `student` `where` `Sdept=` `'EL'` `;`

4.实际测试方案

4.1 把两条语句写进一个触发器(操作失败，逻辑执行不成功)

1 2 3 4 5 6 7 8 9 10 11 drop trigger if exists test_trigger; DELIMITER $ CREATE TRIGGER test_trigger AFTER INSERT ON student2 FOR EACH ROW BEGIN insert into student_bak(Sno,Sname,Ssex,Sage,Sdept) select * from student where Sdept= 'EL' ; delete from student where Sdept= 'EL' ; END $ DELIMITER ;

4.2 准备两个单独的触发器，一个是当原表出现这条数据时，插入到备份表实现备份的效果。第二个触发器是做备份表备份完数据之后，在做一个删除原表该条数据的触发器，然后实现备份完删除的效果（还是操作失败），执行报错，说的是触发器有冲突还是什么的，这样做让数据库不知道执行逻辑了。

4.3 做一个在原表如果进行删除目标数据，然后备份该条数据到备份表的触发器。最后再实现实时探测目标数据出现然后删除的操作就行了，不局限于触发器的思维，做一个定时任务就可以了（操作成功）

比如下面测试的当数据库的表中Sdept字段出现了一个叫‘EL'的字段时，需要把整行数据删除掉

1 2 3 4 5 6 7 8 9 10 drop trigger if exists student_bak_trigger; DELIMITER $ CREATE TRIGGER student_bak_trigger BEFORE DELETE ON student FOR EACH ROW BEGIN insert into student_bak(Sno,Sname,Ssex,Sage,Sdept) select * from student where Sdept= 'EL' ; END $ DELIMITER ;

这个触发器就实现了，如果原表的目标数据被删除了，触发器触发了就会备份该条数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 mysql> select * from student; + -----------+--------+------+------+-------+ | Sno | Sname | Ssex | Sage | Sdept | + -----------+--------+------+------+-------+ | 201215121 | 李勇 | 男 | 20 | CS | | 201215122 | 刘晨 | 女 | 19 | CS | | 201215123 | 王敏 | 女 | 18 | MA | | 201215130 | 兵丁 | 男 | 20 | CH | + -----------+--------+------+------+-------+ 4 rows in set (0.00 sec) mysql> select * from student_bak; + -----------+--------+------+------+-------+---------------------+ | Sno | Sname | Ssex | Sage | Sdept | create_date | + -----------+--------+------+------+-------+---------------------+ | 201215124 | 张三 | 男 | 20 | EL | 2021-09-18 15:42:20 | + -----------+--------+------+------+-------+---------------------+ 1 row in set (0.00 sec) mysql> insert into student values ( '201215125' , '王五' , '男' ,30, 'EL' ); Query OK, 1 row affected (0.00 sec) mysql> select * from student; + -----------+--------+------+------+-------+ | Sno | Sname | Ssex | Sage | Sdept | + -----------+--------+------+------+-------+ | 201215121 | 李勇 | 男 | 20 | CS | | 201215122 | 刘晨 | 女 | 19 | CS | | 201215123 | 王敏 | 女 | 18 | MA | | 201215125 | 王五 | 男 | 30 | EL | | 201215130 | 兵丁 | 男 | 20 | CH | + -----------+--------+------+------+-------+ 5 rows in set (0.00 sec) mysql> select * from student_bak; + -----------+--------+------+------+-------+---------------------+ | Sno | Sname | Ssex | Sage | Sdept | create_date | + -----------+--------+------+------+-------+---------------------+ | 201215124 | 张三 | 男 | 20 | EL | 2021-09-18 15:42:20 | + -----------+--------+------+------+-------+---------------------+ 1 row in set (0.00 sec) mysql> delete from student where Sdept= 'EL' ; Query OK, 1 row affected (0.01 sec) mysql> select * from student_bak; + -----------+--------+------+------+-------+---------------------+ | Sno | Sname | Ssex | Sage | Sdept | create_date | + -----------+--------+------+------+-------+---------------------+ | 201215124 | 张三 | 男 | 20 | EL | 2021-09-18 15:42:20 | | 201215125 | 王五 | 男 | 30 | EL | 2021-09-18 15:47:28 | + -----------+--------+------+------+-------+---------------------+ 2 rows in set (0.00 sec)

最后实现一个定时任务，来循环的删除一个叫‘EL'的字段的整行数据，这里的定时任务是面向全局的，一定要加上数据库名和具体表名，定时任务的执行速度是可以手动调整的，下面为3s/次,以便实现需要的效果

1 2 3 4 create event if not exists e_test_event on schedule every 3 second on completion preserve do delete from abc.student where Sdept= 'EL' ;

关闭定时任务：

1 2	`mysql>` `alter` `event e_test_event` `ON` `COMPLETION PRESERVE DISABLE;` `Query OK, 0` `rows` `affected (0.00 sec)`

查看定时任务：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 mysql> select * from information_schema.events\G;*************************** 4. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: abc EVENT_NAME: e_test_event DEFINER: root@% TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: delete from abc.student where Sdept= 'EL' EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 3 INTERVAL_FIELD: SECOND SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION STARTS: 2021-09-17 13:35:44 ENDS: NULL STATUS: ENABLED ON_COMPLETION: PRESERVE CREATED: 2021-09-17 13:35:44 LAST_ALTERED: 2021-09-17 13:35:44 LAST_EXECUTED: 2021-09-18 15:43:35 EVENT_COMMENT: ORIGINATOR: 3330614 CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci 4 rows in set (0.00 sec)

查看触发器：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 mysql> select * from information_schema.triggers\G; *************************** 5. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: abc TRIGGER_NAME: student_bak_trigger EVENT_MANIPULATION: DELETE EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: abc EVENT_OBJECT_TABLE: student ACTION_ORDER: 1 ACTION_CONDITION: NULL ACTION_STATEMENT: BEGIN insert into student_bak(Sno,Sname,Ssex,Sage,Sdept) select * from student where Sdept= 'EL' ; END ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2021-09-18 15:41:48.53 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION DEFINER: root@% CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci 5 rows in set (0.00 sec)

实现效果：

到此这篇关于mysql触发器实时检测一条语句进行备份删除思路详解的文章就介绍到这了,更多相关mysql触发器备份删除内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.cnblogs.com/houzhiheng/p/15309058.html

可以去百度分享获取分享代码输入这里。

声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。