問題描述：使用者有一個這樣一個需求，在一張表裡會不時出現 “違規” 字樣的欄位，需要在出現這個欄位的時候，把整行的資料刪掉。這是個採集任務，如果發現有“違規”字樣的資料，會整點或者什麼時間進行統一上報，也無法對源頭進行控制讓這種資料不生成。
現在需要實現以下需求：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: 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)
實現效果：
