MySql數(shù)據(jù)庫(kù)觸發(fā)器如何使用

這篇文章主要講解了“MySql數(shù)據(jù)庫(kù)觸發(fā)器如何使用”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySql數(shù)據(jù)庫(kù)觸發(fā)器如何使用”吧！

一、介紹

1、觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程。觸發(fā)器和存儲(chǔ)過(guò)程一樣，是一個(gè)能夠完成特定功能、存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器上的SQL片段，但是觸發(fā)器無(wú)語(yǔ)調(diào)用，當(dāng)對(duì)數(shù)據(jù)庫(kù)表中的數(shù)據(jù)執(zhí)行DML操作時(shí)自動(dòng)觸發(fā)這個(gè)SQL片段的執(zhí)行，無(wú)需手動(dòng)調(diào)用。

2、在MySql中，只有執(zhí)行insert，delete，update操作時(shí)才能觸發(fā)觸發(fā)器的執(zhí)行

3、觸發(fā)器的這種特性可以協(xié)助應(yīng)用在數(shù)據(jù)庫(kù)端確保數(shù)據(jù)的完整性，日志記錄，數(shù)據(jù)校驗(yàn)等操作

4、使用別名OLD和NEW來(lái)引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容，這與其他的數(shù)據(jù)庫(kù)是相似的，現(xiàn)在觸發(fā)器只支持行級(jí)觸發(fā)，不支持語(yǔ)句級(jí)觸發(fā)

二、操作

1、表數(shù)據(jù)準(zhǔn)備

# 用戶(hù)表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用戶(hù)名',
  `sex` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '性別',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


# 用戶(hù)操作日志表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_log
-- ----------------------------
DROP TABLE IF EXISTS `user_log`;
CREATE TABLE `user_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2、觸發(fā)器格式

# 創(chuàng)建只有一個(gè)執(zhí)行語(yǔ)句的觸發(fā)器

create trigger 觸發(fā)器名 before|after 觸發(fā)事件
on 表名 for each row
執(zhí)行語(yǔ)句；

# 創(chuàng)建有多個(gè)執(zhí)行語(yǔ)句的觸發(fā)器

create trigger 觸發(fā)器名 before|after 觸發(fā)事件
on 表名 for each row
begin  
   執(zhí)行語(yǔ)句列表
end;

3、操作

drop TRIGGER if EXISTS TRIGGER_test;
-- 需求1：當(dāng)users表添加一行數(shù)據(jù)，則會(huì)自動(dòng)在user_log添加日志記錄
delimiter $$
CREATE TRIGGER TRIGGER_test after INSERT
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES('添加了一條數(shù)據(jù)',NOW());
end $$
delimiter ;

INSERT INTO users(user_name,sex) VALUES('xiaohemaio','男');


-- 需求2：當(dāng)users表修改一行數(shù)據(jù)，則會(huì)自動(dòng)在user_log添加日志記錄
drop TRIGGER if EXISTS TRIGGER_test1;

delimiter $$
CREATE TRIGGER TRIGGER_test1  BEFORE UPDATE
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES('修改了一條數(shù)據(jù)',NOW());
end $$
delimiter ;

update users set user_name='迪麗熱巴' WHERE id=3;

三、觸發(fā)器NEW和OLD的使用

MySql中定義了NEW和OLD,用來(lái)表示觸發(fā)器的所在表中，觸發(fā)了觸發(fā)器的那一行數(shù)據(jù)，來(lái)引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容。

使用方法：NEW.columnName (columnName為相應(yīng)數(shù)據(jù)表某一列名)

1、案例

-- 案例一
drop TRIGGER if EXISTS TRIGGER_test2;

delimiter $$
CREATE TRIGGER TRIGGER_test2 after INSERT
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES(CONCAT('添加的用戶(hù)信息為：',NEW.user_name,' 性別為：',NEW.sex ),NOW());
end $$
delimiter ;

INSERT INTO users(user_name,sex) VALUES('xiaohemaio','男');


-- 案例二 
drop TRIGGER if EXISTS TRIGGER_test3;

delimiter $$
CREATE TRIGGER TRIGGER_test3  BEFORE UPDATE
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES(CONCAT('將：',OLD.user_name,' 修改為：',NEW.user_name ),NOW());
end $$
delimiter ;

update users set user_name='迪麗熱巴' WHERE id=4;

-- 案例三
drop TRIGGER if EXISTS TRIGGER_test4;

delimiter $$
CREATE TRIGGER TRIGGER_test4  BEFORE DELETE
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES(CONCAT('將id為：',OLD.user_name,' 已刪除' ),NOW());
end $$
delimiter ;

DELETE FROM  users WHERE id=4;

四、其他操作

-- 查看觸發(fā)器
SHOW TRIGGERS；

-- 刪除觸發(fā)器
drop TRIGGER if EXISTS 觸發(fā)器名;

五、注意事項(xiàng)

1、觸發(fā)器中不能對(duì)本表進(jìn)行insert，update，delete操作，以免遞歸循環(huán)觸發(fā)

2、盡量少使用 觸發(fā)器，假設(shè)觸發(fā)器觸發(fā)每次執(zhí)行1s，insert table 500條數(shù)據(jù)，那么就需要觸發(fā)500次觸發(fā)器，光是觸發(fā)器執(zhí)行的時(shí)間就花費(fèi)了500s，而insert 500條數(shù)據(jù)一共是1s，那么這個(gè)insert 的效率就非常低了。

3、觸發(fā)器是針對(duì)每一行的數(shù)據(jù)，對(duì)增刪改非常頻繁的表上切記不要使用觸發(fā)器，因?yàn)榉浅Ｏ馁Y源。

補(bǔ)充：驗(yàn)證觸發(fā)器

向用戶(hù)表users插入數(shù)據(jù)。

mysql> INSERT INTO users (userName, password, name, nickName, sex, email, isManager) VALUE ('itbilu', 'e10adc3949ba59abbe56e057f20f883e', 'IT筆錄', 'itbilu', 1, 'cn.liuht@gmail.com', 0);

users原來(lái)沒(méi)有數(shù)據(jù)，剛插入的數(shù)據(jù)userId為1。向用戶(hù)表角色表userRoles插入數(shù)據(jù)，使觸發(fā)器觸發(fā)：

mysql> INSERT INTO userRoles (userId, roleId) VALUE (1, 1);

剛才插入的數(shù)據(jù)已經(jīng)使觸發(fā)器觸發(fā)，查看結(jié)果如下：

mysql> select isManager from users;
+-----------+
| isManager |
+-----------+
|         1 |
+-----------+

感謝各位的閱讀，以上就是“MySql數(shù)據(jù)庫(kù)觸發(fā)器如何使用”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)MySql數(shù)據(jù)庫(kù)觸發(fā)器如何使用這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！