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

mysql触发器实时检测一条语句进行备份删除,第1张

概述问题描述:用户有一个这样一个需求,在一张表里会不时出现 “违规” 字样的字段,需要在出现这个字段的时候,把整行的数据删掉。这是个采集任务,如果发现有“违规”字样的数据,会整点或者什么时间进行统一上报,

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

现在需要实现以下需求:1.实时检测这条数据的产生,发现后删除

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

 

解决思路:需要明确解决思路,

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

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

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

 

测试方案:先准备一些测试数据和测试表

1.建立测试数据

create table Student(Sno char(9) primary key,Sname char(20) not null,Ssex char(2),Sage smallint,Sdept char(20));
create table Course(Cno char(4) primary key,Cname char(40) not null,Cpno char(4) references Course(Cno),Ccredit smallint);
create table SC(Sno char(9),Cno char(4),Grade smallint,primary key(Sno,Cno),foreign key(Sno) references Student(Sno),foreign key (Cno) references Course(Cno));


insert into Student(Sno,Sname,Ssex,Sage,Sdept)values('201215121','李勇','男','20','CS');
insert into Student(Sno,Sdept)values('201215122','刘晨','女','19',Sdept)values('201215123','王敏','18','MA');

insert into Course(Cno,Cname,Cpno,Ccredit)values('1','数据库','5','4');
insert into Course(Cno,Ccredit)values('2','数学','null','2');
insert into Course(Cno,Ccredit)values('3','信息系统','1',Ccredit)values('4',' *** 作系统','6','3');
insert into Course(Cno,Ccredit)values('5','数据结构','7',Ccredit)values('6','数据处理',Ccredit)values('7','PASCLA语言','4');

insert into SC(Sno,Cno,Grade)values('201215121','92');
insert into SC(Sno,'2','85');
insert into SC(Sno,'3','88');
insert into SC(Sno,Grade)values('201215122','90');
insert into SC(Sno,'80');

 

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

原表建表语句

MysqL> show create table student;+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| table   | Create table                                                                                                                                                                                                                                                             |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| student | CREATE table `student` (  `Sno` char(9) NOT NulL,`Sname` 202) DEFAulT NulL,`Sage` smallint DEFAulT NulL,`Sdept` 1 row in set (0.01 sec)

修改后的备份表

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)

 

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

备份语句(因为备份表多一个时间戳的字段,所以备份语句要做修改一下)

insert into  student_bak(Sno,Sdept) select * from student where Sdept='MA'; 

备份效果

 

插入测试语句:

insert into student values(201215124',张三20,1)">EL');

删除语句(删除数据一定要写精准了,到时候删什么,就要备份什么)

delete ';

 

4.实际测试方案

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

drop trigger if exists test_trigger;
DEliMITER $
CREATE TRIGGER test_trigger
AFTER
INSERT ON student2
FOR EACH ROW
BEGIN
insert into student_bak(Sno,Sdept) select * from student where Sdept='EL';
delete from student where Sdept='EL';
END $
DEliMITER ;

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

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

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

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,Sdept) ';END $DEliMITER ;

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

MysqL> from student;+-----------+--------+------+------+-------+| Sno       | Sname  | Ssex | Sage | Sdept |+-----------+--------+------+------+-------+| 201215121 | 李勇   | 男   |   20 | CS    || 201215122 | 刘晨   | 女   |   19 | CS    || 201215123 | 王敏   | 女   |   18 | MA    || 201215130 | 兵丁   | 男   |   20 | CH    |+-----------+--------+------+------+-------+4 rows 0.00 sec)MysqL>  student_bak;+-----------+--------+------+------+-------+---------------------+| Sno       | Sname  | Ssex | Sage | Sdept | create_date         |+-----------+--------+------+------+-------+---------------------+| 201215124 | 张三   | 男   |   20 | EL    | 2021-09-18 15:42:20 |+-----------+--------+------+------+-------+---------------------+ sec)MysqL> insert into student values(201215125王五30,1)">);query OK,1 row affected (201215125 | 王五   | 男   |   30 | EL    || 5 rows  sec)MysqL> delete ;query OK,1)">0.0120 || 30 | EL    | 47:28 |+-----------+--------+------+------+-------+---------------------+2 rows 0.00 sec)

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

create event  not exists e_test_eventon schedule every 3 second on completion preservedo delete from abc.student '; 

关闭定时任务:

MysqL> alter event e_test_event ON COMPLETION PRESERVE disABLE;query OK,1)">0 rows affected (0.00 sec)

查看定时任务:

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 EVENT_TYPE: RECURRING EXECUTE_AT: NulL INTERVAL_VALUE: 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: 17 13:35:44 ENDS: NulL STATUS: ENABLED ON_COMPLETION: PRESERVE CREATED: LAST_ALTERED: LAST_EXECUTED: 43:35 EVENT_COMMENT: ORIGINATOR: 3330614CHaraCTER_SET_CLIENT: utf8mb4ColLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_ColLATION: utf8mb4_0900_ai_ci0.00 sec)

查看触发器:

MysqL>  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,1)">;END        ACTION_ORIENTATION: ROW             ACTION_TIMING: BEFOREACTION_REFERENCE_olD_table: NulLACTION_REFERENCE_NEW_table: NulL  ACTION_REFERENCE_olD_ROW: olD  ACTION_REFERENCE_NEW_ROW: NEW                   CREATED: 41:48.53                  sql_mode: ONLY_FulL_GROUP_BY,NO_ENGINE_SUBSTITUTION                   defineR: root@%      CHaraCTER_SET_CLIENT: utf8mb4      ColLATION_CONNECTION: utf8mb4_0900_ai_ci        DATABASE_ColLATION: utf8mb4_0900_ai_ci0.00 sec)

 

实现效果:

 

 

 

 

                  

总结

以上是内存溢出为你收集整理的mysql触发器实时检测一条语句进行备份删除全部内容,希望文章能够帮你解决mysql触发器实时检测一条语句进行备份删除所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: https://outofmemory.cn/sjk/1151478.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-05-31
下一篇 2022-05-31

发表评论

登录后才能评论

评论列表(0条)

保存