Shulou(Shulou.com)06/03 Report--

This article is about mysql trigger real-time detection of a statement for backup deletion of the operation method is how, the editor feels very practical, so share with you to learn, I hope you can learn something after reading this article, say no more, follow the editor to have a look.

Problem description: users have a requirement that a field with the word "violation" appear in a table from time to time, and the entire row of data needs to be deleted when this field appears. This is a collection task. If data with the word "violation" is found, it will be uniformly reported on time or at what time, and there is no way to control the source so that this kind of data is not generated.

Now you need to implement the following requirements:

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

two。 Back up this data before deleting it

The solution is as follows:

Clear ideas are needed to solve the problem.

1. First of all, how to detect deletion in real time? If you ask the developer that this data is generated in insert, you can do a trigger for delete data when the table is inserted, and then do an after insert to do the data.

two。 How do I make a backup? How to back up? Can it be backed up into a table where the time of each insertion is recorded, and the backup table can be basically the same as the original table structure, but the backup table needs to delete the self-increasing attributes, primary key, foreign key and other attributes of the original table, and add a timestamp field to facilitate recording the time of each backup data. Delete the above attributes in order to write the data into the backup table.

3. How do I make a backup before deleting it? At first, I just want to put a trigger on it, first back up the data, followed by a deletion, which won't work during the test.

Test scenario:

Prepare some test data and test tables first

1. Establish 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)

two。 Set up a backup list. To view the table creation statement, the formal environment does not know the table structure of the original table. It requires the operation of changing the original table structure and creating a new backup table.

Original table creation table 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, insert test statements

Backup statement (because the backup table has an extra timestamp field, so 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 the test statement:

Insert into student values ('201215124' Zhang San', 'male', 20 'clock EL')

Delete statement (delete data must be written accurately, and back up what is deleted at that time)

Delete from student where Sdept='EL'

4. Actual test scheme

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

Drop trigger if exists test_trigger;DELIMITER $CREATE TRIGGER test_triggerAFTERINSERT ON student2FOR EACH ROWBEGINinsert 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 when this data appears in the original table, insert it into the backup table to achieve the backup effect. The second trigger is to do the backup table to prepare the data, then do a trigger to delete the data in the original table, and then achieve the effect of deletion after the backup (or the operation failed), and execute the error message, saying that the trigger has a conflict or something, so that the database does not know the execution logic.

4.3 make a trigger that deletes the target data in the original table, and then backs up the data to the backup table. Finally, the real-time operation of detecting the occurrence of the target data and then deleting it is fine. It is not limited to the thinking of the trigger, but a scheduled task can be done (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

The trigger is implemented. If the target data of the original table is deleted, the trigger will 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 | soldiers | 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' 'Wangwu', 'male', 30 minutes ELT) 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 | soldiers | male | 20 | CH | +-+ 5 rows in set (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.01sec) 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, a scheduled task is implemented to delete the entire row of data in a field called 'EL'. The scheduled task here is global-oriented. Be sure to add the database name and specific table name. The execution speed of the scheduled task can be adjusted manually. The following is 3s/ times, in order to achieve the desired effect.

Create event if not exists e_test_eventon schedule every 3 second on completion preservedo delete from abc.student where Sdept='EL'

Turn off 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: 3330614CHARACTERSTET CLIENT: utf8mb4COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci4 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: BEFOREACTION_REFERENCE_OLD_TABLE: NULLACTION_REFERENCE_NEW_TABLE: NULLACTION_REFERENCE_ OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2021-09-18 15 purl 41purl 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_ci5 rows in set (0.00 sec)

Achieve results:

This is how the mysql trigger detects a statement for backup deletion in real time. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.