mysql觸發器實時檢測一條語句進行備份删除

我愛睡蓮 2021-09-18 16:04:43 阅读数:629

mysql 删除

問題描述:用戶有一個這樣一個需求,在一張錶裏會不時出現 “違規” 字樣的字段,需要在出現這個字段的時候,把整行的數據删掉。這是個采集任務,如果發現有“違規”字樣的數據,會整點或者什麼時間進行統一上報,也無法對源頭進行控制讓這種數據不生成。

現在需要實現以下需求: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'; 

備份效果

 

插入測試語句:

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: [email protected]% 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: [email protected]%
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
DATABASE_COLLATION: utf8mb4_0900_ai_ci
5 rows in set (0.00 sec)

 

實現效果:

 

 

 

 

                  

版权声明:本文为[我愛睡蓮]所创,转载请带上原文链接,感谢。 https://gsmany.com/2021/09/20210918160442577c.html