Detailed explanation of the idea of MySQL trigger detecting a statement in real time for backup and deletion

Problem description: The user has a requirement that a field with the word "Violation" will appear from time to time in a table, and the user needs to delete the entire row of data when this field appears. This is a collection task. If data containing the word "violation" is found, it will be reported uniformly at the hour or at a certain time. There is no way to control the source to prevent such data from being generated.

Now we need to meet the following requirements:

1. Detect the generation of this data in real time and delete it after discovery

2. Back up this data before deleting it

Solution:

A clear solution is needed.

1. First of all, how to detect deletion in real time? Ask the developer, if the data is generated by insert, then you can create a trigger to delete the data when the table is inserted.

2. How to perform backup? What method to backup? Can the backup be done in a table that records the time of each insertion? The backup table can be basically the same as the original table structure, but the backup table needs to delete the original table's auto-increment attributes, primary key, foreign key and other attributes, and add a timestamp field to facilitate recording the time of each backup data. The above attributes are deleted in order to be able to write data into the backup table

3. How to make a backup before deleting? At first I thought I could just put it in a trigger, back up the data first, and then delete it afterwards, but it didn't work when I tested it.

Test plan:

Prepare some test data and test tables first

1. Create test data

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. Create a backup table. Check the table creation statement. The formal environment does not know the table structure of the original table. It is necessary to modify the original table structure and create a new backup table.

Original table creation statement

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. Prepare backup statements, delete statements, and insert test statements

Backup statement (because the backup table has an additional timestamp field, the backup statement needs to be modified)

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)

Backup effect

Insert test statement:

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

Delete statement (be sure to write the deletion statement accurately, and back up what you want to delete)

delete from student where Sdept='EL';

4. Actual test plan

4.1 Write two statements into one trigger (operation failed, logic execution failed)

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 Prepare two separate triggers. One is to insert the data into the backup table to achieve the backup effect when the data appears in the original table. The second trigger is to create a trigger to delete the data in the original table after backing up the data in the backup table, and then achieve the effect of deletion after backup (the operation still fails), and the execution reports an error, saying that there is a trigger conflict or something. This makes the database unaware of the execution logic.

4.3 Create a trigger that deletes the target data in the original table and then backs up the data to the backup table. Finally, we just need to detect the target data in real time and delete it. We are not limited to the trigger thinking. We can just do a scheduled task (the operation is successful).

For example, in the following test, when a field called 'EL' appears in the Sdept field in the database table, the entire row of data needs to be deleted.

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 ;

This trigger is implemented. If the target data in the original table is deleted, the trigger will be triggered to back up the data.

mysql> select * from student;
+-----------+--------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 201215121 | Li Yong | Male | 20 | CS |
| 201215122 | Liu Chen | Female | 19 | CS |
| 201215123 | Wang Min | Female | 18 | MA |
| 201215130 | Soldier | Male | 20 | CH |
+-----------+--------+------+------+-------+
4 rows in set (0.00 sec)

mysql> select * from student_bak;
+-----------+--------+------+-------+---------------------+
| Sno | Sname | Ssex | Sage | Sdept | create_date |
+-----------+--------+------+-------+---------------------+
| 201215124 | Zhang San | Male | 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 | Li Yong | Male | 20 | CS |
| 201215122 | Liu Chen | Female | 19 | CS |
| 201215123 | Wang Min | Female | 18 | MA |
| 201215125 | Wang Wu | Male | 30 | EL |
| 201215130 | Soldier | Male | 20 | CH |
+-----------+--------+------+------+-------+
5 rows in set (0.00 sec)

mysql> select * from student_bak;
+-----------+--------+------+-------+---------------------+
| Sno | Sname | Ssex | Sage | Sdept | create_date |
+-----------+--------+------+-------+---------------------+
| 201215124 | Zhang San | Male | 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 | Zhang San | Male | 20 | EL | 2021-09-18 15:42:20 |
| 201215125 | Wang Wu | Male | 30 | EL | 2021-09-18 15:47:28 |
+-----------+--------+------+-------+---------------------+
2 rows in set (0.00 sec)

Finally, implement a scheduled task to cyclically delete the entire row of data in a field called 'EL'. The scheduled task here is global, and the database name and specific table name must be added. The execution speed of the scheduled task can be manually adjusted. The following is 3s/time to achieve the desired effect.

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

Disable scheduled tasks:

mysql> alter event e_test_event ON COMPLETION PRESERVE DISABLE;
Query OK, 0 rows affected (0.00 sec)

View scheduled tasks:

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)

View triggers:

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)

Result:

This is the end of this article about the detailed explanation of the idea of MySQL trigger real-time detection of a statement for backup and deletion. For more relevant MySQL trigger backup and deletion content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

MySQL events and triggers topic refinement
MySQL database triggers from beginner to proficient
Getting Started Guide for MySQL Stored Procedures, Triggers, and Event Schedulers
Detailed explanation of MySQL trigger trigger example
Introduction to the use and advantages and disadvantages of MySQL triggers
Use of MySQL trigger
An article to give you a deep understanding of Mysql triggers
Use of MySQL triggers
MySQL trigger usage in simple terms

<<: Overview and application of position attributes (absolute|relative|static|fixed) in CSS

>>: Detailed explanation of four solutions to floating problems in CSS layout

HTML table tag tutorial (44): table header tag

Detailed explanation of the idea of MySQL trigger detecting a statement in real time for backup and deletion

Now we need to meet the following requirements:

Solution:

Test plan:

HTML table tag tutorial (44): table header tag

Basic JSON Operation Guide in MySQL 5.7

base target="" controls the link's target open frame

MySQL functional index optimization solution

JavaScript css3 to implement simple video barrage function

The best way to automatically mount shared directories on Ubuntu 16.04 startup under Virtualbox

Nginx service 500: Internal Server Error one of the reasons

Analysis of Difficulties in Hot Standby of MySQL Database

Tomcat multi-instance deployment and configuration principles

Solution to the problem of MySQL data delay jump

Recommend

Detailed tutorial for installing the unzipped version of mysql5.7.28 winx64 on windows

4 flexible Scss compilation output styles

A detailed explanation of the overlapping and soft color matching method in web page color matching

MySQL 5.7.21 installation and password configuration tutorial

Explanation of the execution priority of mySQL keywords

Solution to the problem that crontab output redirection does not take effect in Linux

Causes and solutions for MySQL master-slave synchronization delay

Css3 realizes seamless scrolling and anti-shake

In-depth analysis of MySQL data type DECIMAL

Implementation of mysql8.0.11 data directory migration

Example of how to configure nginx in centos server

Concat() of combined fields in MySQL

CSS Pick-up Arrows, Catalogs, Icons Implementation Code

Example of how to set up a multi-column equal height layout with CSS

A brief discussion on JS regular RegExp object