首頁 > 軟體

mysql 觸發器的使用及注意點

2022-08-02 14:07:24

前言

在上一篇，我們詳細瞭解了mysql 儲存過程 相關的內容，儲存過程屬於資料庫程式設計的一種，使用儲存過程可以在一定程度上減少程式與mysql服務的IO互動的次數，提升效能；

本篇要介紹的是mysql的觸發器，也屬於資料庫程式設計的一種，相對儲存過程來說，使用起來更加簡單，在某些特定的場景下使用觸發器，同樣可以達到減少應用程式與mysql伺服器互動次數從而提升效能的目的；

一、觸發器簡介

觸發器是一種特殊的儲存過程，在定義觸發器時會定義觸發器的觸發條件，使得觸發器在滿足觸發條件時自動執行而不需要人為呼叫（儲存過程需要人為參與）；

觸發器操作的是與表有關的資料庫物件，比如在insert/update/delete之前(BEFORE)或之後(AFTER)，觸發並執行觸發器中定義的SQL語句集合；

二、觸發器特點及使用場景

1、增強資料庫的安全性

可以實現對使用者運算元據庫的限制，比如只允許使用者在特定時間段內運算元據表，不允許使用者對某些資料更改超過指定的範圍

2、實現資料庫操作的紀錄檔審計

使用觸發器，可以跟蹤使用者對資料庫的操作行為，把使用者執行的一些操作自動記錄到紀錄檔跟蹤表中

3、實現複雜的級聯操作

比如當建立一條訂單資料時，需要同時往訂單詳情表，庫存表，財務收支表等插入資料類似這樣的操作就可以考慮使用觸發器；

使用觸發器，可以實現更加複雜的級聯操作

三、觸發器類似與核心引數

實際使用的時候，主要有3種型別的觸發器可供選擇：INSERT ，UPDATE ，和DELETE ，三種不同型別的觸發器對應3種不同的使用場景；

  • INSERT 型別：通常涉及到資料新增的時候，定義這種型別的觸發器，表示新增一條資料後，接下來要觸發的動作；
  • UPDATE 型別：通常發生在修改一條資料時，定義這種型別的觸發器，可以記錄資料修改之前與修改之後的核心欄位值；
  • DELETE 型別觸發器：通常記錄在某一次刪除資料時，通過這種型別的觸發器，記錄某次刪除資料時的核心引數；

在編寫觸發器的時候，有兩個非常重要的引數物件，即 NEW 和 OLD，可以簡單理解為，NEW 中儲存並傳遞即將要完成插入的引數物件，而 OLD 記錄的是插入或修改或刪除之前的引數物件；

觸發器型別與引數物件的對應關係如下所示：

觸發器型別NEW 和 OLD
INSERT 型觸發器NEW 表示將要或者已經新增的資料
UPDATE 型觸發器OLD 表示修改之前的資料 , NEW 表示將要或已經修改後的資料
DELETE 型觸發器OLD 表示將要或者已經刪除的資料

四、觸發器語法

瞭解了mysql觸發器相關的理論知識後，下面就來了解下觸發器的使用吧；

1、建立語法

CREATE TRIGGER trigger_name				-- 定義觸發器名稱
BEFORE/AFTER INSERT/UPDATE/DELETE		-- 定義觸發器觸發時機和型別
ON tbl_name FOR EACH ROW 				-- 行級觸發器
BEGIN
	trigger_stmt ;						-- 觸發器實際要執行的業務邏輯
END;

2、檢視觸發器

SHOW TRIGGERS ;

3、刪除觸發器

DROP TRIGGER [schema_name.]trigger_name ;  -- 如果沒有指定 schema_name，預設為當前數 據庫

五、觸發器使用案例

準備兩張表，一張業務表，一張紀錄檔記錄表，模擬當業務表資料的增刪改的時候，通過觸發器將紀錄檔資料寫到紀錄檔表；

業務表 user

CREATE TABLE `user` (
  `user_id` varchar(32) NOT NULL COMMENT '使用者ID',
  `user_name` varchar(64) DEFAULT NULL COMMENT '暱稱,表示使用者真實姓名',
  `passwd` varchar(64) NOT NULL COMMENT '密碼',
  `email` varchar(64) DEFAULT NULL COMMENT '郵箱',
  `mobile` varchar(32) DEFAULT NULL COMMENT '手機號',
  `address` varchar(128) DEFAULT NULL COMMENT '手機號',
  `ID` varchar(18) DEFAULT NULL COMMENT '身份證號',
  `sex` int(11) DEFAULT NULL COMMENT '使用者性別 1:男 2:女',
  `info` varchar(255) DEFAULT NULL,
  `age` int(12) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `idx_name` (`user_name`),
  KEY `idx_mobile` (`mobile`),
  FULLTEXT KEY `info` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

紀錄檔表

CREATE TABLE user_logs (
	id INT (11) NOT NULL auto_increment,
	operation VARCHAR (20) NOT NULL COMMENT '操作型別, insert/update/delete',
	operate_time datetime NOT NULL COMMENT '操作時間',
	operate_id INT (11) NOT NULL COMMENT '操作的ID',
	operate_params VARCHAR (500) COMMENT '操作引數',
	PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT charset = utf8;

六、INSERT型別 觸發器使用

CREATE TRIGGER user_insert_trigger 
	after insert on `user` for each row 
begin 
	insert INTO user_logs (
		id,
		operation,
		operate_time,
		operate_id,
		operate_params
	)
values
	(
		null,
		'insert',
		now(),
		new.user_id,
		concat(
			'insert params: id=',
			new.user_id,

			',user_name = ',
			new.user_name,

			', passwd=',
			NEW.passwd,

			', email=',
			NEW.email,

			', mobile=',
			NEW.mobile,

			', address=',
			NEW.address

		)
	);
END;O user_logs (
		id,
		operation,
		operate_time,
		operate_id,
		operate_params
	)
values
	(
		null,
		'insert',
		now(),
		new.id,
		concat(
			'insert params: id=',
			new.id,

			',user_name = ',
			new.user_name,

			', passwd=',
			NEW.passwd,

			', email=',
			NEW.email,

			', mobile=',
			NEW.mobile,

			', address=',
			NEW.address

		)
	);
END;

建立完畢後，檢視下剛剛建立的觸發器

SHOW TRIGGERS ;

接下來通過給user表插入一條資料

insert into user(user_id,user_name,passwd,email,mobile,address)
values ('5','xiaowang','123456','xiaowang@qq.com','13325556761','杭州市餘杭區')

資料插入成功後，檢查紀錄檔表是否有資料寫入

可以看到紀錄檔資料寫入成功，說明觸發器被觸發了；

七、UPDATE 型別觸發器使用

執行下面的語句進行觸發器的建立

CREATE TRIGGER user_update_trigger AFTER UPDATE ON `user` FOR EACH ROW
BEGIN
	INSERT INTO user_logs (
		id,
		operation,
		operate_time,
		operate_id,
		operate_params
	)
VALUES
	(
		NULL,
		'update',
		now(),
		new.user_id,
		concat(
			'before update params: id=',
			OLD.user_id,

			',user_name=',
			OLD.user_name,

			', passwd=',
			OLD.passwd,

			', email=',
			OLD.email,

			', mobile=',
			OLD.mobile,

			' | after update params: id=',
			NEW.user_id,

			',user_name=',
			NEW.user_name,

			', passwd=',
			NEW.passwd,

			', email=',
			NEW.email,

			', mobile=',
			NEW.mobile
		)
	);
end;

然後執行下面的 update語句驗證下該型別的觸發器是否生效；

update `user` set user_name = 'xiaowang_1',passwd = '123456',email = 'xiaowang_update@qq.com' where user_id= '5';

cja

八、DELETE 型別觸發器使用

執行下面的sql創進行觸發器建立

create trigger user_delete_trigger
	after delete on `user` for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
	(null, 'delete', now(), old.user_id,
concat('before delete: id=',old.user_id,',user_name=',old.user_name, ', mobile=', old.mobile, ', email=', old.email, ', address=', old.address));
end;

觸發器建立完畢後，執行下面的delete sql，觀察紀錄檔表是否有一條新增的資料

delete from user where user_id = ‘2’;

通過紀錄檔表發現，刪除的觸發器生效了；

九、觸發器常用場景

1、使用觸發器實現兩表或多表資料同步

舉例來說，現在有A表和A_copy表，A_copy表的存在的目的相對於是備份表，儲存了A表中的關鍵業務欄位，應用程式向A表插入一條資料時，需同步向A_copy表插入一條資料，這樣的業務場景就可以考慮使用觸發器；

2、審計紀錄檔記錄

如上面的案例，向核心業務主表進行增刪改操作時，記錄審計紀錄檔可以考慮使用觸發器；

3、合規性檢查

比如向核心業務表新增涉及到金錢相關的資料時，可以設定一定的檢查規則，比如當金額超過一定的數量時，及時提示錯誤，防止錯誤資料進入系統；

十、觸發器使用注意點

合理利用觸發器可以幫助應用程式減少與資料庫的IO次數，一定程度上提升效能，但是觸發器也有一些自身的缺點，在使用的時候需要注意，現做如下總結，

1、可讀性較差

觸發器最大的一個問題就是可讀性差，因為觸發器儲存在資料庫中，並且由事件驅動，這就意味著觸發器有可能不受應用層的控制 ，這對系統維護是非常有挑戰的；

2、相關資料的變更，可能會導致觸發器出錯

特別是涉及到資料的表結構變更，都有可能導致觸發器出錯，進而影響資料操作的正常執行。這些都會由於觸發器本身的隱蔽性，影響到應用中錯誤原因排查的效率；

到此這篇關於mysql 觸發器使用詳解的文章就介紹到這了,更多相關mysql 觸發器使用內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！


相關文章

  • 德漢姆中學就讀條件 德漢姆中學就讀條件

    <em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011

    2021-06-01 09:32:01

  • 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列

    综合看Anker超能充系列的性价比很高，并且与不仅和iPhone12/苹果<em>Mac</em>Book很配，而且适合多设备充电需求的日常使用或差旅场景，不管是安卓还是Switch同样也能用得上它，希望这次分享能给准备购入充电器的小伙伴们有所

    2021-06-01 09:31:42

  • 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！ 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！

    除了L4WUDU与吴亦凡已经多次共事，成为了明面上的厂牌成员，吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节，这也是20XXCLUB首次全员合照，王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让

    2021-06-01 09:31:34

  • IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？ IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？

    目前应用IPFS的机构：1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆，历史资料永久保存在 IPFS 6 加

    2021-06-01 09:31:24

  • 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊

    开拓者的车机是兼容苹果和<em>安卓</em>，虽然我不怎么用，但确实兼顾了我家人的很多需求：副驾的门板还配有解锁开关，有的时候老婆开车，下车的时候偶尔会忘记解锁，我在副驾驶可以自己开门：第二排设计很好，不仅配置了一个很大的

    2021-06-01 09:30:48

  • iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？ iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？

    不仅是<em>安卓</em>手机，苹果手机的降价力度也是前所未有了，iPhone12也“跳水价”了，发布价是6799元，如今已经跌至5308元，降价幅度超过1400元，最新定价确认了。iPhone12是苹果首款5G手机，同时也是全球首款5nm芯片的智能机，它

    2021-06-01 09:30:45

IT145.com E-mail:sddin#qq.com