觸發器

基本概念

概述：

• 觸發器，就是一種特殊的存儲過程。觸發器和存儲過程一樣是一個能夠完成特定功能、存儲在數據庫服務器上的 SQL 片段，但是觸發器無需調用，當對數據庫表中的數據執行 DML 操作時自動觸發這個 SQL 片段的執行，無需手動條用
• 在 MySQL 中，只有執行 insert,delete,update 操作時才能觸發觸發器的執行
• 觸發器的這種特性可以協助應用在數據庫端確保數據的完整性，日志記錄，數據校驗等操作
• 使用別名 OLD 和 NEW 來引用觸發器中發生變化的記錄內容，這與其他的數據庫是相似的。現在觸發器還只支持行級觸發，不支持語句級觸發

觸發器特點：

• 什么條件會觸發？insert、delete、update
• 什么時候觸發？在增刪改前或者后
• 觸發頻率：針對每一行執行
• 觸發器定義在表上，附著在表上

觸發器操作

創建觸發器

創建單執行語句的觸發器

格式如下：

create trigger 觸發器名 before|after 觸發事件
on 表名 for each row
執行語句；

創建多執行語句的觸發器

格式如下：

create trigger 觸發器名 before|after 觸發事件
on 表名 for each row
begin執行語句列表
end;

代碼示例：

-- 用戶表
create table user(uid int primary key ,username varchar(50) not null ,password varchar(50) not null
);-- 用戶信息操作日志
create table user_logs(id int primary key auto_increment ,time timestamp ,log_text varchar(255)
);-- 定義觸發器
create trigger trigger1 after inserton user for each rowinsert into user_logs values (null,now(),'有新用戶添加');-- 在user表中添加數據
insert into user values (1,'張三','123456');

結果如下：

NEW 與 OLD

MySQL 中定義了 NEW 和 OLD，用來表示觸發器的所在表中，觸發了觸發器的那一行數據，來引用觸發器中發生變化的記錄內容

觸發器類型	觸發器類型 NEW 和 OLD 的使用
INSERT 型觸發器	NEW 表示將要或者已經新增的數據
UPDATE 型觸發器	OLD 表示修改之前的數據，NEW 表示將要或已經修改后的數據
DELETE 型觸發器	OLD 表示將要或者已經刪除的數據

使用方法：NEW.columnName（columnName 為相應數據表某一字段名）

代碼示例：

-- 用戶表
create table user(uid int primary key ,username varchar(50) not null ,password varchar(50) not null
);-- 用戶信息操作日志
create table user_logs(id int primary key auto_increment ,time timestamp ,log_text varchar(255)
);-- 定義insert觸發器
create trigger trigger2 after inserton user for each rowinsert into user_logs values(null,now(),concat('有新用戶添加，信息為：',new.uid,new.username,new.password));-- 在user表中添加數據
insert into user values (1,'張三','123456'),(2,'李四','123456'),(3,'王五','123456'),(4,'趙六','123456');-- 定義update觸發器
create trigger trigger3 after updateon user for each rowinsert into user_logs values(null,now(),concat('有用戶修改，修改前的信息為：',old.uid,old.username,old.password));
create trigger trigger4 after updateon user for each rowinsert into user_logs values(null,now(),concat('有用戶修改，修改后的信息為：',new.uid,new.username,new.password));-- 修改user表中的數據
update user set password='234567' where uid=2;-- 定義delete觸發器
create trigger trigger5 after deleteon user for each rowinsert into user_logs values(null,now(),concat('有用戶刪除，刪除前的信息為：',old.uid,old.username,old.password));-- 刪除user表中的數據
delete from user where uid=3;

結果如下：

查看觸發器

格式如下：

show triggers;

代碼示例：

show triggers;

結果如下：

刪除觸發器

格式如下：

drop trigger [if exists] trigger_name;

代碼示例：

drop trigger if exists trigger2;
show triggers;

結果如下：

注意事項

MySQL 中觸發器不能對本表進行 insert、update、delete 操作，以免遞歸循環觸發

盡量少使用觸發器，假設觸發器觸發每次執行 1s，添加 500 條數據就要觸發 500 次觸發器，就會導致效率變低

觸發器是針對每一行的，對增刪改非常頻繁的表上不要使用觸發器，會非常消耗資源

存儲引擎

基本概念

概述：

• 數據庫存儲引擎是數據庫底層軟件組織，數據庫管理系統（DBMS）使用數據引擎進行創建、查詢、更新和刪除數據
• 不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能。現在許多不同的數據庫管理系統都支持多種不同的數據引擎。MySQL 的核心就是存儲引擎
• 用戶可以根據不同的需求為數據表選擇不同的存儲引擎
• 可以使用 SHOW ENGINES 命令可以查看 MySQL 的所有執行引擎，我們可以到默認的執行引擎是 InnoDB，支持事務，行級鎖定和外鍵

分類：

• MyISAM：MySQL 5.5 之前的默認數據庫引擎，最為常用。擁有較高的插入，查詢速度，但不支持事務
• InnoDB：事務型場景的首選引擎，支持 ACID 事務，支持行級鎖定，MySQL 5.5 成為默認數據庫引擎
• Memory：所有數據置于內存的存儲引擎，擁有極高的插入、更新和查詢效率，但會占用與數據量成正比的內存空間，且內容在 MySQL 重新啟動時會丟失
• Archive：非常適合存儲大量獨立的歷史記錄數據（因不經常被讀取），擁有高效的插入速度，但對查詢的支持相對較差
• Federated：將不同的 MySQL 服務器聯合起來，邏輯上組成一個完整的數據庫，非常適合分布式應用
• CSV：邏輯上由逗號分割數據的存儲引擎。它會在數據庫子目錄里為每個數據表創建一個 .csv 文件。這是一種普通文本文件，每個數據行占用一個文本行。CSV 存儲引擎不支持索引。
• BlackHole：黑洞引擎，寫入的任何數據都會消失，一般用于記錄 binlog 做復制的中繼。
• ERFORMANCE_SCHEMA：存儲引擎，該引擎主要用于收集數據庫服務器性能參數。
• Mrg_Myisam Merge：存儲引擎，是一組 MyIsam 的組合，即將 MyIsam 引擎的多個表聚合起來，其內部沒有數據，真正的數據仍在 MyIsam 引擎的表中，但可直接進行查詢、刪除、更新等操作。

常用引擎對比：

功能	MyISAM	MEMORY	InnoDB
存儲限制	256TB	RAM	64TB
支持事務	No	No	Yes
支持全文索引	Yes	No	No
支持 B 樹索引	Yes	Yes	Yes
支持哈希索引	No	Yes	No
支持集群索引	No	No	Yes
支持數據索引	Yes	Yes	Yes
支持數據壓縮	Yes	No	No
空間使用率	低	N/A	高
支持外鍵	No	No	Yes

基本操作

查詢當前數據庫支持的存儲引擎

代碼示例：

show engines;

結果如下：

查看當前的默認存儲引擎

代碼示例：

show variables like '%storage_engine%';

結果如下：

查看某個表用的存儲引擎

格式如下：

show create table table_name;

代碼示例：

create table student(id int ,name varchar(20)
);
show create table student;

結果如下：

創建表時指定存儲引擎

格式如下：

create table table_name(...) engine = engine_name;

代碼示例：

create table teacher(id int ,name varchar(20)
) engine = MyISAM;
show create table teacher;

結果如下：

修改表的存儲引擎

格式如下：

alter table table_name engine = engine_name;

代碼示例：

alter table teacher engine = InnoDB;
show create table teacher;

結果如下：