mysql触发器实时检测一条语句进行备份删除思路详解
16lz
2021-12-09
问题描述:用户有一个这样一个需求,在一张表里会不时出现 “违规” 字样的字段,需要在出现这个字段的时候,把整行的数据删掉。这是个采集任务,如果发现有“违规”字样的数据,会整点或者什么时间进行统一上报,也无法对源头进行控制让这种数据不生成。
现在需要实现以下需求:
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)
原表建表语句
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)
插入测试语句:
insert into student values('201215124','张三','男',20,'EL');
delete from student where Sdept='EL';
4.1 把两条语句写进一个触发器(操作失败,逻辑执行不成功)
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.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)
create event if not exists e_test_eventon schedule every 3 second on completion preservedo 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: 3330614CHARACTER_SET_CLIENT: utf8mb4COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci4 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: BEFOREACTION_REFERENCE_OLD_TABLE: NULLACTION_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_ci5 rows in set (0.00 sec)
更多相关文章
- Android(安卓)-- Android(安卓)JUint 与 Sqlite
- android 当系统存在多个Launcher时,如何设置开机自动进入默认的La
- Android(安卓)SQLiteDatabase的使用
- android 通话记录次数
- Android(安卓)SQLiteDatabase的使用
- android实现关键字搜索功能
- Android初始化语言 (init.*.rc、init.conf文件格式)
- 我的android 第14天 - 使用SQLiteDatabase操作SQLite数据库
- Android(安卓)编码规范