mysql觸發(fā)器實(shí)時(shí)檢測(cè)一條語句進(jìn)行備份刪除思路詳解
問題描述：用戶有一個(gè)這樣一個(gè)需求，在一張表里會(huì)不時(shí)出現(xiàn) “違規(guī)” 字樣的字段，需要在出現(xiàn)這個(gè)字段的時(shí)候，把整行的數(shù)據(jù)刪掉。這是個(gè)采集任務(wù)，如果發(fā)現(xiàn)有“違規(guī)”字樣的數(shù)據(jù)，會(huì)整點(diǎn)或者什么時(shí)間進(jìn)行統(tǒng)一上報(bào)，也無法對(duì)源頭進(jìn)行控制讓這種數(shù)據(jù)不生成。
現(xiàn)在需要實(shí)現(xiàn)以下需求：
1.實(shí)時(shí)檢測(cè)這條數(shù)據(jù)的產(chǎn)生，發(fā)現(xiàn)后刪除
2.在刪除之前作備份這條數(shù)據(jù)
解決思路：
需要明確解決思路，
1.首先是如何實(shí)時(shí)探測(cè)刪除？詢問開發(fā)，這條數(shù)據(jù)的生成方式為insert，就可以做一個(gè)當(dāng)表做插入的時(shí)候，然后做一個(gè)after insert 做delete數(shù)據(jù)的觸發(fā)器
2.如何進(jìn)行備份？何種方式備份？能不能備份到一個(gè)表里，這個(gè)表里記錄每次插入的時(shí)間，建立這個(gè)備份表可以取與原表結(jié)構(gòu)基本相同，但是備份表要?jiǎng)h除原表的自增屬性，主鍵，外鍵等屬性，新增一個(gè)時(shí)間戳字段，方便記錄每次備份數(shù)據(jù)的時(shí)間，刪除以上屬性是為了能夠把數(shù)據(jù)寫入備份表中
3.如何在刪除之前做備份呢？一開始想的是放到一個(gè)觸發(fā)器就行，先把數(shù)據(jù)進(jìn)行備份，然后后面跟著一條刪除，測(cè)試的時(shí)候行不通
測(cè)試方案：
先準(zhǔn)備一些測(cè)試數(shù)據(jù)和測(cè)試表
1.建立測(cè)試數(shù)據(jù)
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.建立備份表。查看建表語句，正式環(huán)境是不知道原表的表結(jié)構(gòu)的，需要有改原表結(jié)構(gòu)，并創(chuàng)建新備份表的操作的
原表建表語句
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.準(zhǔn)備備份語句，刪除語句，插入測(cè)試語句
備份語句（因?yàn)閭浞荼矶嘁粋€(gè)時(shí)間戳的字段，所以備份語句要做修改一下）
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)
備份效果
插入測(cè)試語句：
insert into student values('201215124','張三','男',20,'EL');
刪除語句（刪除數(shù)據(jù)一定要寫精準(zhǔn)了，到時(shí)候刪什么，就要備份什么）
delete from student where Sdept='EL';
4.實(shí)際測(cè)試方案
4.1 把兩條語句寫進(jìn)一個(gè)觸發(fā)器(操作失敗，邏輯執(zhí)行不成功)
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 準(zhǔn)備兩個(gè)單獨(dú)的觸發(fā)器，一個(gè)是當(dāng)原表出現(xiàn)這條數(shù)據(jù)時(shí)，插入到備份表實(shí)現(xiàn)備份的效果。第二個(gè)觸發(fā)器是做備份表備份完數(shù)據(jù)之后，在做一個(gè)刪除原表該條數(shù)據(jù)的觸發(fā)器，然后實(shí)現(xiàn)備份完刪除的效果（還是操作失敗），執(zhí)行報(bào)錯(cuò)，說的是觸發(fā)器有沖突還是什么的，這樣做讓數(shù)據(jù)庫不知道執(zhí)行邏輯了。
4.3 做一個(gè)在原表如果進(jìn)行刪除目標(biāo)數(shù)據(jù)，然后備份該條數(shù)據(jù)到備份表的觸發(fā)器。最后再實(shí)現(xiàn)實(shí)時(shí)探測(cè)目標(biāo)數(shù)據(jù)出現(xiàn)然后刪除的操作就行了，不局限于觸發(fā)器的思維，做一個(gè)定時(shí)任務(wù)就可以了（操作成功）
比如下面測(cè)試的當(dāng)數(shù)據(jù)庫的表中Sdept字段出現(xiàn)了一個(gè)叫‘EL'的字段時(shí)，需要把整行數(shù)據(jù)刪除掉
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 ;
這個(gè)觸發(fā)器就實(shí)現(xiàn)了，如果原表的目標(biāo)數(shù)據(jù)被刪除了，觸發(fā)器觸發(fā)了就會(huì)備份該條數(shù)據(jù)
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)
最后實(shí)現(xiàn)一個(gè)定時(shí)任務(wù)，來循環(huán)的刪除一個(gè)叫‘EL'的字段的整行數(shù)據(jù)，這里的定時(shí)任務(wù)是面向全局的，一定要加上數(shù)據(jù)庫名和具體表名，定時(shí)任務(wù)的執(zhí)行速度是可以手動(dòng)調(diào)整的，下面為3s/次,以便實(shí)現(xiàn)需要的效果
create event if not exists e_test_event on schedule every 3 second on completion preserve do delete from abc.student where Sdept='EL';
關(guān)閉定時(shí)任務(wù)：
mysql> alter event e_test_event ON COMPLETION PRESERVE DISABLE; Query OK, 0 rows affected (0.00 sec)
查看定時(shí)任務(wù)：
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)
查看觸發(fā)器：
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)
實(shí)現(xiàn)效果：
到此這篇關(guān)于mysql觸發(fā)器實(shí)時(shí)檢測(cè)一條語句進(jìn)行備份刪除思路詳解的文章就介紹到這了,更多相關(guān)mysql觸發(fā)器備份刪除內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信