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

皇冠管理端登3网站皇冠管理端登3网站(www.9cx.net)实时更新发布最新最快最有效的皇冠管理端登3网站手机网址,包括新2登3手机网址,新2登3备用网址,皇冠登3最新网址,新2足球登3网址,新2网址大全。

问题形貌:用户有一个这样一个需求,在一张内外会不时泛起 “违规” 字样的字段,需要在泛起这个字段的时刻,把整行的数据删掉。这是个采集义务,若是发现有“违规”字样的数据,会整点或者什么时间举行统一上报,也无法对源头举行控制让这种数据不天生。

现在需要实现以下需求: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,Sname,Ssex,Sage,Sdept)values('201215122','刘晨','女','19','CS');
insert into Student(Sno,Sname,Ssex,Sage,Sdept)values('201215123','王敏','女','18','MA');

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

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

 

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

原表建表语句

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)

修改后的备份表

 

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,Sname,Ssex,Sage,Sdept) select * from student where Sdept='MA'; 

备份效果

欧博手机版下载

欢迎进入欧博手机版下载(www.aLLbetgame.us),欧博官网是欧博集团的官方网站。欧博官网开放Allbet注册、Allbe代理、Allbet电脑客户端、Allbet手机版下载等业务。

 

插入测试语句:

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

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

delete from student where Sdept='EL';

 

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,Sname,Ssex,Sage,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,Sname,Ssex,Sage,Sdept) select * from student where Sdept='EL';
END $
DELIMITER ;

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

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/次,以便实现需要的效果

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

关闭准时义务:

mysql> alter event e_test_event ON COMPLETION PRESERVE DISABLE;
Query OK, 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 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)

查看触发器:

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)

 

实现效果:

 

 

 

 

                  

欧博客户端下载

欢迎进入欧博客户端下载(www.aLLbetgame.us),欧博官网是欧博集团的官方网站。欧博官网开放Allbet注册、Allbe代理、Allbet电脑客户端、Allbet手机版下载等业务。

  • 评论列表:
  •  怎么买usdt便宜(www.usdt8.vip)
     发布于 2021-09-21 00:05:27  回复
  • RISC-V的应用之三:服务器市场 。 虽然现在RISC-V的高性能市场一片空缺,但RISC-V自己用来设计高性能芯片是没有问题的,学术界已经有基于RISC-V架构的511核处置器(Celerity)。只是基于RISC-V的低门槛特点,进入的企业体量较为小巧,没有足够的资金做耐久结构与研发,高性能等需要较长研发时间的领域尚无人实验。只能夸你美丽帅气
  •  怎么买usdt便宜(www.usdt8.vip)
     发布于 2021-09-29 00:08:49  回复
  • 更有网友直批不合理,示意张嘉倪的脸吊打“三金”影后周冬雨。示意两人角色反了吧!有读者跟我互动吗

添加回复:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。