个人中心
gpt4 book ai didi

mysql触发器实时检测一条语句进行备份删除思路详解

转载 作者：qq735679552 更新时间：2022-09-27 22:32:09 25 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章mysql触发器实时检测一条语句进行备份删除思路详解由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

问题描述：用户有一个这样一个需求，在一张表里会不时出现 “违规” 字样的字段，需要在出现这个字段的时候，把整行的数据删掉。这是个采集任务，如果发现有“违规”字样的数据，会整点或者什么时间进行统一上报，也无法对源头进行控制让这种数据不生成.

现在需要实现以下需求：

1.实时检测这条数据的产生，发现后删除 。

2.在删除之前作备份这条数据 。

解决思路：

需要明确解决思路， 。

1.首先是如何实时探测删除？询问开发，这条数据的生成方式为insert，就可以做一个当表做插入的时候，然后做一个after insert 做delete数据的触发器 。

2.如何进行备份？何种方式备份？能不能备份到一个表里，这个表里记录每次插入的时间，建立这个备份表可以取与原表结构基本相同，但是备份表要删除原表的自增属性，主键，外键等属性，新增一个时间戳字段，方便记录每次备份数据的时间，删除以上属性是为了能够把数据写入备份表中 。

3.如何在删除之前做备份呢？一开始想的是放到一个触发器就行，先把数据进行备份，然后后面跟着一条删除，测试的时候行不通 。

测试方案：

先准备一些测试数据和测试表 。

1.建立测试数据 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.建立备份表。查看建表语句，正式环境是不知道原表的表结构的，需要有改原表结构，并创建新备份表的操作的 。

原表建表语句 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.准备备份语句，删除语句，插入测试语句 。

备份语句（因为备份表多一个时间戳的字段，所以备份语句要做修改一下） 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)

备份效果 。

mysql触发器实时检测一条语句进行备份删除思路详解

插入测试语句:

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

删除语句（删除数据一定要写精准了，到时候删什么，就要备份什么） 。

?
1
delete from student where Sdept= 'EL' ;

4.实际测试方案 。

4.1 把两条语句写进一个触发器(操作失败，逻辑执行不成功) 。

?
1
2
3
4
5
6
7
8
9
10
11
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'的字段时，需要把整行数据删除掉 。

?
1
2
3
4
5
6
7
8
9
10
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 ;

这个触发器就实现了，如果原表的目标数据被删除了，触发器触发了就会备份该条数据 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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/次,以便实现需要的效果 。

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

关闭定时任务:

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

查看定时任务:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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)

查看触发器:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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触发器实时检测一条语句进行备份删除思路详解

到此这篇关于mysql触发器实时检测一条语句进行备份删除思路详解的文章就介绍到这了,更多相关mysql触发器备份删除内容请搜索我以前的文章或继续浏览下面的相关文章希望大家以后多多支持我！ 。

原文链接：https://www.cnblogs.com/houzhiheng/p/15309058.html 。

最后此篇关于mysql触发器实时检测一条语句进行备份删除思路详解的文章就讲到这里了,如果你想了解更多关于mysql触发器实时检测一条语句进行备份删除思路详解的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

25 4 0
文章推荐： Springboot中使用缓存的示例代码
文章推荐： 详解python 条件语句和while循环的实例代码
文章推荐： Java并发编程包中atomic的实现原理示例详解
文章推荐： 一个非常简单好用的Python图形界面库(PysimpleGUI)
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com