mysql觸發器實時檢測一條語句進行備份刪除的操作方法是怎樣的

本篇文章給大家分享的是有關mysql觸發器實時檢測一條語句進行備份刪除的操作方法是怎樣的，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

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

現在需要實現以下需求：

1.實時檢測這條數據的產生，發現后刪除

2.在刪除之前作備份這條數據

解決思路：

需要明確解決思路，

1.首先是如何實時探測刪除？詢問開發，這條數據的生成方式為insert，就可以做一個當表做插入的時候，然后做一個after insert 做delete數據的觸發器

2.如何進行備份？何種方式備份？能不能備份到一個表里，這個表里記錄每次插入的時間，建立這個備份表可以取與原表結構基本相同，但是備份表要刪除原表的自增屬性，主鍵，外鍵等屬性，新增一個時間戳字段，方便記錄每次備份數據的時間，刪除以上屬性是為了能夠把數據寫入備份表中

3.如何在刪除之前做備份呢？一開始想的是放到一個觸發器就行，先把數據進行備份，然后后面跟著一條刪除，測試的時候行不通

測試方案：

先準備一些測試數據和測試表

1.建立測試數據

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.建立備份表。查看建表語句，正式環境是不知道原表的表結構的，需要有改原表結構，并創建新備份表的操作的

原表建表語句

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)

3.準備備份語句，刪除語句，插入測試語句

備份語句（因為備份表多一個時間戳的字段，所以備份語句要做修改一下）

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)

備份效果

插入測試語句：

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)

實現效果：

以上就是mysql觸發器實時檢測一條語句進行備份刪除的操作方法是怎樣的，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。