mysql觸發(fā)器實時檢測一條語句進(jìn)行備份刪除思路詳解
問題描述:用戶有一個這樣一個需求,在一張表里會不時出現(xiàn) “違規(guī)” 字樣的字段,需要在出現(xiàn)這個字段的時候,把整行的數(shù)據(jù)刪掉。這是個采集任務(wù),如果發(fā)現(xiàn)有“違規(guī)”字樣的數(shù)據(jù),會整點或者什么時間進(jìn)行統(tǒng)一上報,也無法對源頭進(jìn)行控制讓這種數(shù)據(jù)不生成。
現(xiàn)在需要實現(xiàn)以下需求:
1.實時檢測這條數(shù)據(jù)的產(chǎn)生,發(fā)現(xiàn)后刪除
2.在刪除之前作備份這條數(shù)據(jù)
解決思路:
需要明確解決思路,
1.首先是如何實時探測刪除?詢問開發(fā),這條數(shù)據(jù)的生成方式為insert,就可以做一個當(dāng)表做插入的時候,然后做一個after insert 做delete數(shù)據(jù)的觸發(fā)器
2.如何進(jìn)行備份?何種方式備份?能不能備份到一個表里,這個表里記錄每次插入的時間,建立這個備份表可以取與原表結(jié)構(gòu)基本相同,但是備份表要刪除原表的自增屬性,主鍵,外鍵等屬性,新增一個時間戳字段,方便記錄每次備份數(shù)據(jù)的時間,刪除以上屬性是為了能夠把數(shù)據(jù)寫入備份表中
3.如何在刪除之前做備份呢?一開始想的是放到一個觸發(fā)器就行,先把數(shù)據(jù)進(jìn)行備份,然后后面跟著一條刪除,測試的時候行不通
測試方案:
先準(zhǔn)備一些測試數(shù)據(jù)和測試表
1.建立測試數(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)備備份語句,刪除語句,插入測試語句
備份語句(因為備份表多一個時間戳的字段,所以備份語句要做修改一下)
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');
刪除語句(刪除數(shù)據(jù)一定要寫精準(zhǔn)了,到時候刪什么,就要備份什么)
delete from student where Sdept='EL';
4.實際測試方案
4.1 把兩條語句寫進(jìn)一個觸發(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)備兩個單獨的觸發(fā)器,一個是當(dāng)原表出現(xiàn)這條數(shù)據(jù)時,插入到備份表實現(xiàn)備份的效果。第二個觸發(fā)器是做備份表備份完數(shù)據(jù)之后,在做一個刪除原表該條數(shù)據(jù)的觸發(fā)器,然后實現(xiàn)備份完刪除的效果(還是操作失敗),執(zhí)行報錯,說的是觸發(fā)器有沖突還是什么的,這樣做讓數(shù)據(jù)庫不知道執(zhí)行邏輯了。
4.3 做一個在原表如果進(jìn)行刪除目標(biāo)數(shù)據(jù),然后備份該條數(shù)據(jù)到備份表的觸發(fā)器。最后再實現(xiàn)實時探測目標(biāo)數(shù)據(jù)出現(xiàn)然后刪除的操作就行了,不局限于觸發(fā)器的思維,做一個定時任務(wù)就可以了(操作成功)
比如下面測試的當(dāng)數(shù)據(jù)庫的表中Sdept字段出現(xiàn)了一個叫‘EL'的字段時,需要把整行數(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 ;
這個觸發(fā)器就實現(xiàn)了,如果原表的目標(biāo)數(shù)據(jù)被刪除了,觸發(fā)器觸發(fā)了就會備份該條數(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)
最后實現(xiàn)一個定時任務(wù),來循環(huán)的刪除一個叫‘EL'的字段的整行數(shù)據(jù),這里的定時任務(wù)是面向全局的,一定要加上數(shù)據(jù)庫名和具體表名,定時任務(wù)的執(zhí)行速度是可以手動調(diào)整的,下面為3s/次,以便實現(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)閉定時任務(wù):
mysql> alter event e_test_event ON COMPLETION PRESERVE DISABLE; Query OK, 0 rows affected (0.00 sec)
查看定時任務(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)
實現(xiàn)效果:
到此這篇關(guān)于mysql觸發(fā)器實時檢測一條語句進(jìn)行備份刪除思路詳解的文章就介紹到這了,更多相關(guān)mysql觸發(fā)器備份刪除內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
DBeaver如何實現(xiàn)導(dǎo)入excel中的大量數(shù)據(jù)
使用DBeaver導(dǎo)入Excel數(shù)據(jù)需先將文件轉(zhuǎn)換為CSV格式,詳細(xì)步驟包括:將Excel文件另存為CSV,確保列名與數(shù)據(jù)庫表字段對應(yīng),然后在DBeaver中創(chuàng)建表和導(dǎo)入CSV文件,注意選擇正確的編碼格式以防中文亂碼2024-10-10給mysql數(shù)據(jù)庫的字段設(shè)默認(rèn)值方式
這篇文章主要介紹了給mysql數(shù)據(jù)庫的字段設(shè)默認(rèn)值方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-10-10mysql數(shù)據(jù)庫中各種鎖歸納總結(jié)
相對于其他的數(shù)據(jù)庫而言,MySQL的鎖機制比較簡單,最顯著的特點就是不同的存儲引擎支持不同的鎖機制,這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫中各種鎖歸納總結(jié)的相關(guān)資料,需要的朋友可以參考下2024-08-08MySQL大內(nèi)存配置方案 如my-medium.ini、my-huge.ini等
這篇文章主要介紹了MySQL大內(nèi)存配置方案 如my-medium.ini、my-huge.ini等,經(jīng)測試確實不錯,訪問mysql速度快了很多2014-08-08mysql中l(wèi)eft join設(shè)置條件在on與where時的用法區(qū)別分析
這篇文章主要介紹了mysql中l(wèi)eft join設(shè)置條件在on與where時的用法區(qū)別,結(jié)合實例形式分析了mysql中l(wèi)eft join設(shè)置條件在on與where時的相關(guān)用法區(qū)別與操作注意事項,需要的朋友可以參考下2020-02-02