mysql-01索引-视图-存储函数和过程-触发器

1. 索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据
库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数
据结构上实现高级查找算法，这种数据结构就是索引。

==sql优化最主要的就是索引==

图1中没有建立索引,如我们想查询数据3,从上到下依次搜索,会进行全表扫描(全表扫描),才能查询到.

图2中建立索引第一个数是34,第二个数77会跟根节点34比较,大于34放右边右子树.小于34放在左边左子树. 这些数也就相当于地址的引用,指向数据.

建立索引,在数据库中就是数据结构,说白了也就是一张表(在mysql5.5以上版本中叫做B+树数据结构),记录的是一个引用.

1.2 索引优势劣势

优势
1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
2） 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为
更新表时，==MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所
带来的键值变化后的索引信息==。

1.3 索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，

也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。 (InnoDB引擎主要支持的是BTREE索引)
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM 、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

1.4 BTREE 结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

• 树中每个节点最多包含 m个孩子。(如这是二叉树据,他每个节点最多就包含2个分支,即包含两个孩子)
• 除根节点与叶子节点外，每个节点至少有 [ceil(m/2)]个孩子。(m/2向上取整)
• 若根节点不是叶子节点，则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由 n个key与n+1个指针组成(如下分类4个key,下面指的就是5个指针,指针用于指向数据块)，其中[ceil(m/2)-1] <= n <= m-1

以5叉BTree为例,5叉即每个节点最多包含5个孩子，

key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。

ceil(5/2)-1<=n<=5-1即2 <= n <=4 。当n>4时超出这个范围时，中间节点分裂到父节点，两边节点分裂.

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。

演变过程如下：

1). 插入前4个字母 C N G A (这个C N G A就是所谓的key )

n个key和n+1个指针组成.

2). 插入H，n>4(即已有ACGHN大于4个了)，中间元素G字母向上分裂到新的节点(即分裂到父节点,其他节点分裂到两侧)

3). 插入E，K，Q不需要分裂

4). 插入M，中间元素M字母向上分裂到父节点G

5). 插入F，W，L，T不需要分裂

6). 插入Z，中间元素T向上分裂到父节点中

7). 插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂

8). 最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂

BTREE树 和 二叉树 相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

1.5 B+TREE 结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。

2). B+Tree的叶子节点(如下最下面的一排,没有子节点的叫做叶子结点)保存所有的key信息(这些key信息包含以上根节点和子节点的key信息,这些key只是起到索引的作用)，依key大小顺序排列。

3). 所有的非叶子节点(不存储)都可以看作是key的索引部分。

小结: B+Tree只有叶子节点保存数据

1. 叶子节点保存数据,其他的非叶子节点都可以看做是key的索引部分不存数据,==如查找数据走根节点到非叶子节点到叶子结点,有叶子结点查找到关联的数据信息.查询什么数据都需要走到叶子结点,查询的稳定性更高.==

2. 叶子节点和非叶子节点之间有key来进行引用的,查询效率更高,更加稳定

3. 叶子节点之间会有一个连接,可以跨节点查询,这样非常适合范围查询 (理解: 比如查A到F,从左查到右侧,如果叶子节点有连接,排序的过程,我们可以直接走叶子节点查询)

由于 B+Tree只有叶子节点保存数据，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指
针，就形成了带有顺序指针的B+Tree，提高区间访问的性能(便于区间搜索)。

1.6 索引

1.6.1 索引分类

1） 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引

比如User表里面有name,password. 我们建立的索引只包含name一个列,就是单值索引.

2） 唯一索引 ：索引列的值必须唯一，但允许有空值.

比如说主键id,id是唯一的,在这个列上建立索引就是唯一索引.

3） 复合索引 ：即一个索引包含多个列

比如User表里面有name,password. 我们建立的索引包含name,password等多个列,就是复合索引.

1.6.2 创建索引

索引类型: 一般情况下不需要写,特殊需求再写
UNIQUE: 唯一的
FULLTEXT: 全文
SPATIAL: 空间
语法类型: [UNIQUE|FULLTEXT|SPATIAL]可以不用指定
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name(索引名称)
[USING index_type]  //索引类型:如不指定使用B+树,BTREE
ON tbl_name(index_col_name,...)
index_col_name : column_name[(length)][ASC | DESC]
复制代码

创建普通索引

idx_city_name创建的索引名,     为city表中的city_name字段创建索引
create index idx_city_name on city(city_name)
复制代码

创建复合索引 :

NAME,email,STATUS多个字段作为索引,索引遵循最左原则
CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
复制代码

1.6.3 查看索引

SHOW INDEX FROM city;  // 默认为主键创建索引,另外一个即创建的索引
如下key_name即索引
复制代码

1.6.4 删除索引

DROP INDEX 索引名称 ON 表名;

DROP INDEX index_name ON city; 
复制代码

1.6.5 修改索引

1). alter table tb_name add primary key(column_list);
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL

2). alter table tb_name add unique index_name(column_list);
这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）

3). alter table tb_name add index index_name(column_list);
添加普通索引， 索引值可以出现多次。

4). alter table tb_name add fulltext index_name(column_list);
该语句指定了索引为FULLTEXT， 用于全文索引
复制代码

1.7 索引设计原则

1. 对查询频次较高，且数据量比较大的表建立索引

2. 索引字段的选择，最佳候选列应当从 where子句的条件中提取，如果where子句中的组合比较多，那么应当挑

   选最常用、过滤效果最好的列的组合。(经常使用的条件查询)

3. 使用唯一索引，区分度越高，使用索引的效率越高。

4. 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O效率，也可以提升总体的访问效

   率。字段总长度越短越好.

5. 利用最左前缀， N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了

   组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。(组合索引遵循最左匹配原则)

最左匹配原则:sql语句where条件的顺序必须要和创建的索引的顺序一致.如果查询不是最左第一个索引开头匹配的话,索引就会失效,查询的语句就不走索引.

创建组合索引 :
CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
就相当于
对name 创建索引 ;
对name , email 创建了索引 ;
对name , email, status 创建了索引 ;
复制代码

2. 视图

视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

**注：**数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

视图相对于普通的表的优势主要包括以下几项。
简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤
好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但
是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表
修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

2.1 创建视图

为查询结果创建视图,视图名view_name
CREATE VIEW view_name AS
    SELECT column_name(s) FROM table_name WHERE condition;
//从视图中查询数据
SELECT * FROM view_name;
复制代码

2.2 更新视图

方式一; 如果修改的视图存在， 将使用修改语句修改视图，如果不存在，那么将会创建一个视图
CREATE OR REPLACE VIEW view_name AS
	SELECT column_name(s) FROM table_name WHERE condition;
	
方式二;
ALTER VIEW <视图名> AS <SELECT语句>
复制代码

2.3 撤销视图

DROP VIEW 视图名
DROP VIEW view_name
复制代码

3. 存储过程

存储过程和函数是 ==事先经过编译并存储在数据库中的一段 SQL 语句的集合==，调用存储过程和函数可以简化应用开
发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。
函数 ： 是一个有返回值的过程 ；
过程 ： 是一个没有返回值的函数 ；(存储过程没有返回值,但是有out也能给我们返回结果,所以存储函数能做的事情,存储过程也能做)

3.1 存储过程

3.1.1 创建存储过程

语法
CREATE PROCEDURE procedure_name()
begin
	-- SQL语句
end ;

示例;  delimiter声明分隔符为$,使用此分隔符才表示该段命令已经结束可以执行了
delimiter $
create procedure pro_test1()
begin
	select 'Hello Mysql' ;
end$
delimiter ;
复制代码

**==delimiter==**声明sql语句的分隔符

该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该
命令。

理解;如下语句,我直接执行(包含着create procedure语句时候是由语法报错,所以存储过程里面要借用下delimiter声明下分隔符为其他符号,来表示命令结束可以执行)
create procedure pro_test1()
begin
	select 'Hello Mysql' ;
复制代码

3.1.2 调用存储过程

call procedure_name();  // procedure_name是创建的存储过程的名字
复制代码

3.1.3 查看存储过程

调用一次存储过程就会执行一次存储过程中存储的sql语句

-- 方式1; 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
-- 方式2: 查询存储过程的状态信息
show procedure status;
-- 方式3: 查询某个存储过程的定义(包含存储过程创建语句), pro_test1是存储过程的名称
show create procedure test.pro_test1;
复制代码

3.1.4 删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name ；//sp_name存储过程的名称
复制代码

3.2 语法

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构 ， 来完成比较复杂的功能。

3.2.1 DECLARE定义变量

通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。

var_name变量名可以声明多个, DEFAULT value赋值
DECLARE var_name[,...] type [DEFAULT value]
复制代码

示例

delimiter $
create procedure pro_test2()
begin
    declare num int default 5;      -- 声明变量num,类型int,默认值为5
    select num+ 10;
end$
delimiter ;
复制代码

3.2.2 SET直接赋值

**直接赋值使用 SET，可以赋常量或者赋表达式，**具体语法如下：

可以直接赋值多个常量或者表达式
SET var_name = expr [, var_name = expr] ...
复制代码

示例

DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
    DECLARE NAME VARCHAR(20);
    SET NAME = 'MYSQL';
    SELECT NAME ;
END$
DELIMITER ;
复制代码

也可以通过select … into 方式进行赋值操作 :

DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
declare countnum int;
select count(*) into countnum from city;  -- into指定赋值的变量, 即查询city表的总数赋值给变量countnum
select countnum;
END$
DELIMITER ;
复制代码

3.2.3 if 条件判断

if search_condition then statement_list    -- 如search_condition条件成立,then然后statement_list这段语句
    [elseif search_condition then statement_list] ... -- 如满足执行此语句
    [else statement_list]							  -- 否则执行此语句
end if;										-- 结束符和if呼应起来
复制代码

示例

delimiter $
create procedure pro_test6()
begin
declare height int default 175;  -- 定义变量height,类型int, 默认值175
declare description varchar(50); -- 定义变量description,类型varchar(50)
    if height >= 180 then        -- 如果height>=80, 然后赋值变量description description = '身材高挑';
    	set description = '身材高挑';
    elseif height >= 170 and height < 180 then
    	set description = '标准身材';
    else
    	set description = '一般身材';
    end if;
select description ;
end$
delimiter ;
复制代码

3.2.4 传递参数

在创建存储过程的时候传递参数

语法格式:

create procedure procedure_name([in/out/inout] 参数名 参数类型)
 
IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认为输入参数.
OUT: 该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数
复制代码

3.2.4.1 IN : 该参数作为输入

示例: 根据定义的身高变量，判定当前身高的所属的身材类型

-- 创建存储过程
delimiter $
create procedure pro_test5(in height int) -- 创建存储过程;in:输入参数,height参数名,int参数类型
begin
	declare description varchar(50) default ''; -- 声明变量description为varchar类型,默认为''
	if height >= 180 then
		set description='身材高挑';
	elseif height >= 170 and height < 180 then
		set description='标准身材';
	else
		set description='一般身材';
	end if;
	select concat('身高 ', height , '对应的身材类型为:',description);
end$
delimiter ;

-- 调用存储过程: 传入值
call pro_test_in(200);
复制代码

3.2.4.2 OUT: 该参数作为输出

输出参数:就相当于执行改存储过程的返回值

根据传入的身高变量，获取当前身高的所属的身材类型(即返回值)

-- 创建存储过程
delimiter $
create procedure pro_test_out(in height int , out description varchar(100)) -- in输入参数,out输出参数
begin
	if height >= 180 then
		set description='身材高挑';
	elseif height >= 170 and height < 180 then
		set description='标准身材';
	else
		set description='一般身材';
	end if;
end$

delimiter ;

-- 调用存储过程: 传入值180 , 使用变量description接收返回值
call pro_test_out(180,@description);
SELECT @description;  -- 查看改返回值变量
复制代码

==@description== : 这种变量要在变量名称前面加上*“@”符号*，叫做用户会话变量，代表当前整个会话过程他都是有作用
的，这个类似于全局变量一样。
@@global.sort_buffer_size : 这种在变量前加上 “@@” 符号, 叫做 系统变量.

3.2.5 case结构

这个类似于sql的case when函数

方式一 :
CASE case_value
    WHEN when_value THEN statement_list  -- 
    [WHEN when_value THEN statement_list] 
    [ELSE statement_list]
END CASE;

方式二 :
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] 
    [ELSE statement_list]
END CASE;
复制代码

示例: 给定一个月份, 然后计算出所在的季度

delimiter $ 
create procedure procedure_case_test1(month int) -- 默认为in:表示改参数为输入
begin
	declare result varchar(20); -- 定义变量result为varchar类型长度20
	case 
			when month >= 1 and month <=3 then
			set result = '第一季度';
			when month >= 4 and month <=6 then
			set result = '第二季度';
			when month >= 7 and month <=9 then
			set result = '第三季度';
			when month >= 10 and month <=12 then
			set result = '第四季度';
			end case;
	select result;
	end $
delimiter;
	
call	procedure_case_test1(2); -- 调用函数输入值2
复制代码

3.2.6 while循环

满足条件一直循环

语法

while search_condition do   -- 当条件成立一直执行,不成立的时候跳出循环
	statement_list
end while;
复制代码

示例; 计算从1加到n的值

delimiter $
create procedure pro_test8(n int) -- 输入参数n
begin
    declare total int default 0; -- 定义变量total,int类型,默认值0
    declare num int default 1;   -- 定义变量num,int类型,默认值1
    while num<=n do				-- 当num<=n时
        set total = total + num; -- 赋值操作
        set num = num + 1;
    end while;
    select total;
end$
delimiter ;
复制代码

3.2.7 repeat循环

==有条件的循环控制语句, 当满足条件的时候退出循环==。while 是满足条件才执行，repeat 是满足条件就退出循环。
语法结构 : 循环条件后面不需要加分号

REPEAT
    statement_list  ;       -- 满足条件就执行这个sql语句
    UNTIL search_condition -- 直到满足此条件退出循环,要不然一直循环下去
END REPEAT;
复制代码

示例

delimiter $
create procedure pro_test10(n int)
begin
    declare total int default 0;
    repeat
        set total = total + n;
        set n = n - 1;
    until n=0       -- 直到n=0时候,退出循环
    end repeat;
    select total ;
end$
delimiter ;
复制代码

3.2.8 loop循环

==LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现==(通过leave语句完成当前循环退出条件的定义)

语法

[begin_label:] LOOP         -- 当前loop循环的别名
	statement_list			-- 声明循环体
END LOOP [end_label]		-- 结束循环
复制代码

会发现以上没有循环的退出条件如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。

3.2.9 leave语句 (通常和LOOP循环一起使用)

用来从标注的流程构造中退出，通常和 BEGIN … END 或者循环一起使用。下面是一个使用 LOOP 和 LEAVE 的简
单例子 , 退出循环：

delimiter $
CREATE PROCEDURE pro_test11(n int)
BEGIN
    declare total int default 0;
    ins: LOOP    -- 声明loop循环的别名ins
        IF n <= 0 then -- 定义leave退出条件
        leave ins;  -- 退出循环ins
        END IF;
    set total = total + n;
    set n = n - 1;
    END LOOP ins; -- 结束循环
    select total;
END$
delimiter ;
复制代码

3.2.10 游标/光标

==游标是用来存储查询结果集的数据类型== , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用
包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

声明光标：

DECLARE cursor_name游标变量 CURSOR FOR select_statement查询语句 ;
复制代码

OPEN 光标：

OPEN cursor_name ;
复制代码

FETCH 光标：获取游标中的一行数据,,每抓取一次,游标往后走一次

 FETCH cursor_name INTO var_name [, var_name] ...
复制代码

CLOSE 光标：

CLOSE cursor_name ;
复制代码

示例

delimiter $
create procedure pro_test11()
begin
	declare e_id int(11);
	declare e_name varchar(50);
	declare e_age int(11);
	declare e_salary int(11);
	declare emp_result cursor for select * from emp; -- 创建游标; 存储查询的结果集
	open emp_result;   -- 打开游标
	fetch emp_result into e_id,e_name,e_age,e_salary; -- 抓取游标数据: 赋值给4个变量
	select e_id,e_name,e_age,e_salary;     -- 调用存储过程; 查询4个变量的值
	fetch emp_result into e_id,e_name,e_age,e_salary; 
	select e_id,e_name,e_age,e_salary;   
close emp_result;
end$

call pro_test11(); -- 调用存储过程
复制代码

以上事例优化,有多少条数据我们就要写多少条抓取,可以使用循环优化

DELIMITER $
create procedure pro_test12()
begin
    DECLARE id int(11);
    DECLARE name varchar(50);
    DECLARE age int(11);
    DECLARE salary int(11);
    DECLARE has_data int default 1;
    DECLARE emp_result CURSOR FOR select * from emp;
    DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;  -- 当抓取不到数据的时候,赋值边界变量has_data=0
    open emp_result; -- 打开游标
    repeat  -- 循环获取游标数据
        fetch emp_result into id , name , age , salary; -- 抓取不到数据的时候
        select id , name , age , salary;
    until has_data = 0  -- 退出条件
    end repeat;
    close emp_result; -- 关闭游标
end$
DELIMITER ;
复制代码

==DECLARE EXIT HANDLER FOR NOT FOUND; — 当抓取不到数据的时候,触发这一块的句柄==这是mysql提供的一种句柄机制.

需要紧挨着创建游标语句

4. 存储函数

函数 ： 是一个有返回值的过程 ；

语法

CREATE FUNCTION function_name([param type ...]) -- 参数类型列表param type: 参数名  参数类型
RETURNS type -- 返回类型
BEGIN
...
END;
复制代码

示例

定义存储函数, 请求满足条件的总记录数 ;

delimiter $
create function count_city(countryId int)
returns int -- 返回值类型
begin
	declare cnum int ; -- 声明变量存放返回值
    select count(*) into cnum from city where  id = countryId; -- city表查询的总数赋值给变量cnum
	return cnum;	-- 返回cnum的值
end$
delimiter ;
复制代码

调用函数

select count_city(1);  -- 调用函数,传入参数1
select count_city(2);
复制代码

5, 触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集
合(类似于事件)。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
使用别名 OLD 和 NEW (这两个变量也叫做行记录变量)来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在mysql触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW 和 OLD的使用
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

5.1 创建触发器

语法结构 :

create trigger trigger_name   -- 创建触发器
before/after insert/update/delete  -- before/after在之前或之后触发,insert插入或者update更新等操作触发此触发器
on tbl_name   -- 触发器和表有关的,  on 后面跟的表名(即我们要监测的表)
[ for each row ] -- 行级触发器
begin
trigger_stmt ;   -- 触发器具体的内容
end;
复制代码

示例: 通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;

首先创建一张日志表 :

create table emp_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 型触发器，完成插入数据时的日志记录 :

INSERT 型触发器, 行记录变量是new(new引用的插入的这行数据) ,通过new.id 拿到emp表的id,

DELIMITER $
create trigger emp_logs_insert_trigger
after insert   -- 在插入操作之后触发此触发器
on emp 
for each row
begin         -- 触发器内容
    insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
    values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,',
    age:',new.age,', salary:',new.salary,')'));
end $
DELIMITER ;
复制代码

创建 update 型触发器，完成更新数据时的日志记录 :

DELIMITER $
create trigger emp_logs_update_trigger
after update
on emp
for each row
begin
    insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
    values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,',
    age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,',
    age:',new.age,', salary:',new.salary,')'));
end $
DELIMITER ;
复制代码

创建delete 行的触发器 , 完成删除数据时的日志记录 :

DELIMITER $
create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,',
age:',old.age,', salary:',old.salary,')'));
end $
DELIMITER ;
复制代码

测试：

insert into emp(id,name,age,salary) values(null, '光明左使',30,3500);
insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);
update emp set age = 39 where id = 3;
delete from emp where id = 5;
复制代码

5.2 删除触发器

drop trigger [schema_name.]trigger_name
如果没有指定 schema_name，默认为当前数据库 。
复制代码

改前(id:’,old.id,’, name:’,old.name,’,
age:’,old.age,’, salary:’,old.salary,’) , 修改后(id’,new.id, ‘name:’,new.name,’,
age:’,new.age,’, salary:’,new.salary,’)’));
end $
DELIMITER ;

创建delete 行的触发器 , 完成删除数据时的日志记录 :

```sql
DELIMITER $
create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,',
age:',old.age,', salary:',old.salary,')'));
end $
DELIMITER ;
复制代码

测试：

insert into emp(id,name,age,salary) values(null, '光明左使',30,3500);
insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);
update emp set age = 39 where id = 3;
delete from emp where id = 5;
复制代码

5.2 删除触发器

drop trigger [schema_name.]trigger_name
如果没有指定 schema_name，默认为当前数据库 。
复制代码