一、索引

1.1、索引概述

MySQL官方对索引的定义为：

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

image-20210306190926182

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

索引是数据库中用来提高性能的最常用的工具。

1.2、索引优势劣势

1、优势

  • 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本
  • 通过索引对数据结构进行排序，降低数据排序的成本，降低CPU的消耗

以下是 《高性能MySQL》 中的补充

  • 索引大大减少了服务器需要扫描的数据量。
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机 I/O 变为顺序 I/O

2、劣势

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

1.3、索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

  • BTREE 索引（重点介绍）

最常见的索引类型，大部分索引都支持 B 树索引。

  • HASH 索引

只有Memory引擎支持 ， 使用场景简单 。

  • R-tree 索引（空间索引）

空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。

  • Full-text（全文索引）

全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、nnoDB、Memory三种存储引擎对各种索引类型的支持
索引InnoDB引擎MyISAM引擎Memory引擎
BTREE索引支持支持支持
HASH 索引不支持不支持支持
R-tree 索引不支持支持不支持
Full-text5.6版本之后支持支持不支持

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

1、BTREE 结构

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

  • 树中每个节点最多包含 m 个孩子。
  • 除根节点和叶子节点外，每个节点至少有 [ceil(m / 2)] 个孩子节点。（ceil：向上取整）
  • 若根节点不是叶子节点，则至少有两个孩子。
  • 所有的叶子节点都在同一层。
  • 每个非叶子节点由n个key（存储元素）与n+1个指针组成，其中 [ceil(m/2)-1] <= n <= m-1

这里以 5叉BTree为例，由 m = 5 可推出 [ceil(m/2)-1] <= n <= m-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 数据为例

演变过程如下

  • 插入前四个字母 C N G A

image-20210306161147934

  • 插入 H ，此时 n>4，中间元素 G 字母会向上分裂到新的节点，同时G两边的元素会分裂成两块

image-20210306162051558

  • 插入E，K，Q不需要分裂

image-20210306162122287

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

image-20210306162145688

  • 插入F，W，L，T不需要分裂

image-20210306162216615

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

image-20210306162245821

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

image-20210306162311645

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

image-20210306162345542

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

2、B+TREE 结构

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

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

  • B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。 B+Tree的所有数据都保存在叶子节点中。

  • 所有的非叶子节点都可以看作是key的索引部分。

image-20210306162838426

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

3、MySQL中的B+Tree

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

MySQL中的 B+Tree 索引结构示意图:

image-20210306163507780

1.4、索引分类

1、单值索引

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

2、唯一索引

索引列的值必须唯一，但允许有空值。

3、复合索引

即一个索引包含多个列。

4、主键索引

一种特殊的唯一索引，不允许有空值。

一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。

5、普通索引

又称辅助索引、二级索引。

1.5、索引语法

索引可以在创建表时同时创建，也可以随时添加新的索引。

1、准备环境

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
use index_study;
CREATE TABLE `city`
(
    `city_id`    int(11)     NOT NULL AUTO_INCREMENT,
    `city_name`  varchar(50) NOT NULL,
    `country_id` int(11)     NOT NULL,
    PRIMARY KEY (`city_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
CREATE TABLE `country`
(
    `country_id`   int(11)      NOT NULL AUTO_INCREMENT,
    `country_name` varchar(100) NOT NULL,

    PRIMARY KEY (`country_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

insert into `city` (`city_id`, `city_name`, `country_id`)
values (1, '西安', 1);
insert into `city` (`city_id`, `city_name`, `country_id`)
values (2, 'NewYork', 2);
insert into `city` (`city_id`, `city_name`, `country_id`)
values (3, '北京', 1);
insert into `city` (`city_id`, `city_name`, `country_id`)
values (4, '上海', 1);
insert into `country` (`country_id`, `country_name`)
values (1, 'China');
insert into `country` (`country_id`, `country_name`)
values (2, 'America');
insert into `country` (`country_id`, `country_name`)
values (3, 'Japan');
insert into `country` (`country_id`, `country_name`)
values (4, 'UK');

2、创建索引

语法：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

[USING index_type]

on tbl_name(index_col_name,…)

index_col_name : column_name[(length)] [ASC | DESC]

  • [UNIQUE | FULLTEXT | SPATIAL]

UNIQUE：唯一索引

FULLTEXT：全文索引

SPATIAL：空间索引

  • [index_type]

可选，如果不指定默认使用BTree索引

  • tbl_name(index_col_name,…)

为哪张表的那些字段创建索引。

  • 为city表的 city_name 字段创建索引
1
CREATE INDEX idx_city_name ON `city` (city_name);

2、查看索引

  • 语法如下

show index from 要查看的表;

  • 查看city表中的索引
1
show index from `city`;

执行 sql语句，结果如下，可以看到city表下有两个索引。

image-20210306171001423

3、删除索引

  • 语法如下

drop index 索引名 on 表名;

  • 删除我们刚才在city表中为 city_name 创建的索引
1
drop index idx_city_name on `city`;
  • 再次运行查看索引指令，查看结果
1
show index from `city`;

此时可以发现只剩下 PRIMARY 索引

image-20210306171441899

4、使用ALTER命令来创建索引

  • 使用 ALTER 命令创建一个主键索引
1
alter table `tb_name` add primary key(字段列表);

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL

  • 使用 ALTER 命令创建一个唯一索引
1
alter table `tb_name` add unique 索引名(字段列表);

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）

  • 使用 ALTER 命令创建一个普通索引
1
alter table `tb_name` add index 索引名(字段列表);

添加普通索引， 索引值可以出现多次。

  • 使用 ALTER 命令创建一个全文索引
1
alter table tb_name	add	fulltext 索引名(字段列表);

该语句指定了索引为FULLTEXT， 用于全文索引

5、如何查看一个索引是不是唯一索引

查看该表的索引

1
2
3
use index_study;
create index idx_city_name on `city`(city_name);
show index from `city`;

执行结果

image-20210306172658202

1.6、索引设计原则

​ 索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

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

  • 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合（针对查询字段设计索引）

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

  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低

  • DML（如insert、update、delete）操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

  • （针对复合索引）利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

如果创建复合索引idx，这个索引作用于 NAME、EMAIL和STATUS上，那么：

相当于对NAME创建了索引。

对NAME、EMAIL创建了索引

对NAME、EMAIL和STATUS创建了索引。

只要在查询时包含了第一个字段NAME，那么查询就会走索引。

image-20210306173547319

1、哪些情况下需要创建索引

  • 主键自动建立唯一索引

  • 频繁作为查询条件的字段应该创建索引(where 后面的语句)

  • 查询中与其它表关联的字段，外键关系建立索引

  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

  • 查询中统计或者分组字段

  • 对于单键和组合索引，在高并发条件下倾向于创建组合索引

2、哪些情况下不需要创建索引

  • 数据量不大的表
  • 经常增删改的表

提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

  • 数据重复且分布平均的表字段

因此应该只为最经常查询和最经常排序的数据列建立索引。

注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

如：性别和国籍

img

1.7、关于MySQL中BTree索引的一些补充

我们使用术语 B-Tree ，是因为 MySQL 在CREATE TABLE 和其他语句时也是用该关键字。

不过，底层的存储引擎也可能使用不同的存储结构

例如， NOB 集群存储引擎内部时机上使用了 T-Tree 结构存储这种索引，即使其名字为 BTree ；

InnoDB 则使用的是 B+Tree 。

1、创建一张数据表

1
2
3
4
5
6
7
CREATE table People (
    `last_name` varchar(50) not null,
    `first_name` varchar(50) not null,
    `birthday` date not null,
    `gender` enum('m','f') not null,
    key(last_name,first_name,birthday) 
);

对于表中的每一行数据，索引中包含了 last_name 、first_name 和 birthday 列的值，下图显示了索引是如何组织数据的存储的。

image-20210308104834889

2、索引有效的查询

  • 全值匹配

全值匹配指的是和索引中的所有列（上表是last_name,first_name,birthday），例如前面提到的索引可以用于查找 姓名为 李小明、出生于2000-10-31的人。

  • 匹配最左前缀

前面提到的索引可以用于查找所有姓为 刘 的人，即使用索引的第一列。

  • 匹配列前缀

也可以只匹配某一列的开头部分，如前面提到的索引可以用于可以用于查找所有以 J 开头的姓的人。这里也使用了索引的第一列。

  • 匹配范围值

例如前面提到的索引可用于查找姓在 Allen 和 Barrymore 之间的人。这里使用了索引的第一列。

  • 精确匹配某一列而范围匹配另一列

查找条件与索引第一列 last_name 全匹配，与索引第二列 last_name 范围匹配

3、B-Tree索引的限制

  • 如果不是按照索引的最左列开始查找,则无法使用索引。

例如上面例子中的索引无法用于查找名字为Bill人,也无法查找某个特定生日的人,因为这两列都不是最左数据列。类似地,也无法查找姓氏以某个字母结尾的人。

  • 不能跳过索引中的列。

也就是说,前面所述的索引无法用于查找姓为 Smith并且在某个特定日期出生的人。如果不指定名( first name),则 MySQL只能使用索引的第一列

  • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找。

例如有查询 WHERE Last name=’ Smith’ AND first name LIKE ‘%J%’ and birthday=’1976-12-23’,这个查询只能使用索引的前两列,因为这里LIKE是一个范围条件(但是服务器可以把其余列用于其他目的)。如果范围查询列值的数量有限,那么可以通过使用多个等于条件来代替范围条件.

二、视图

2.1、视图概述

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项。

  • 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

  • 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

  • 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

2.2、创建或者修改视图

1、创建视图的语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

  • 创建一个视图，展示城市和国家的相关信息
1
2
3
4
5
create view view_country ASSELECT c1.*,
        c2.country_name
FROM city AS c1
JOIN country AS c2
    ON c1.country_id = c2.country_id;
  • 视图是一张虚表，我们可以使用操作表的语句来操作视图
  • 查询表中所有数据
1
select * from view_country;

结果

image-20210306194217169

2、通过视图更新基表数据

如果视图包含下述结构中的任何一种，那么它就是不可更新的：

  • 聚合函数；
  • DISTINCT关键字；
  • GROUP BY子句；
  • ORDER BY子句；
  • HAVING子句；
  • UNION运算符；
  • 位于选择列表中的子查询；
  • FROM子句中包含多个表；
  • SELECT语句中引用了不可更新视图；
  • WHERE子句中的子查询，引用FROM子句中的表；
  • ALGORITHM 选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）。

不建议更新视图。

3、修改视图的语法

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

  • 修改上面创建的视图，改为查询city表中的city_id 和 city_name 字段
1
2
3
4
ALTER VIEW view_country
AS
SELECT city_id, city_name
FROM city;
  • 查询修改后的视图
1
select * from view_country;

image-20210306195645056

4、查看视图

从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。

image-20210306200016218

同样，在使用 SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。

image-20210306200047809

如果需要查询某个视图的定义，可以使用 SHOW CREATE VIEW 命令进行查看 ：

image-20210306200114777

  • 查看创建视图时使用的SQL语句
1
show create view view_country;

查看结果，在结果的Create View那一列中可以看到执行的sql语句：

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW view_country AS select city.city_id AS city_id,city.city_name AS city_name from city

5、删除视图

语法：

DROP VIEW [IF EXISTS] view_name [, view_name] …[RESTRICT | CASCADE]

  • 删除视图 view_country
1
drop view if exists view_country;

此时执行show tables，发现视图已经被删除

image-20210306201859079

三、存储过程和函数

3.1、存储过程和函数概述

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

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

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

  • 存储过程 ： 是一个没有返回值的函数 ；

3.2、创建存储过程

语法如下：

CREATE PROCEDURE procedure_name ([proc_parameter[,…]])

begin

– SQL语句

end ;

  • 示例

这里需要先指定分隔符，将分隔符定义为 “$”

1
2
3
4
5
delimiter $
create procedure myprodure ()
begin
    select '芜湖起飞';
end$

3.3、调用存储过程

语法如下：

call 存储过程名() 定义的分隔符

  • 调用上面定义的存储过程myprodure
1
2
delimiter $
call myprodure()$

执行结果

image-20210306205245227

3.4、查询存储过程

– 查询db_name数据库中的所有的存储过程

select name from mysql.proc where db=’db_name’;

– 查询存储过程的状态信息

show procedure status;

– 查询某个存储过程的定义

show create procedure test.pro_test1 \G;

  • 查看存储过程的状态
1
show procedure status;

image-20210306221934288

  • 查看myprocedure存储过程的定义
1
show create procedure myprodure;

image-20210306222219461

1
2
3
4
CREATE DEFINER=`root`@`localhost` PROCEDURE `myprodure`()
begin
    select '芜湖起飞';
end
  • 和视图一样，可以使用数据库可视化工具查询存储过程，这里使用的数据库可视化工具是 DataGrip

image-20210306222533214

3.5、删除存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名字 ；

  • 删除上面创建的存储过程
1
drop procedure if exists index_study.myprodure;

image-20210306224444718

3.6、存储过程传递参数

1、语法结构

create procedure procedure_name([in/out/inout] 参数名 参数类型

…

IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认

OUT: 该参数作为输出，也就是该参数可以作为返回值

INOUT: 既可以作为输入参数，也可以作为输出参数

2、示例

  • 根据传入的身高变量，获取当前身高的所属的身材类型
1
2
3
4
5
6
7
8
9
10
11
delimiter $
create procedure myprocedure(in height int,out `desc` varchar(100))
begin
    if height >= 180 then
        set `desc` = '身材高挑';
    elseif height >= 170 and height < 180 then
        set `desc` = '身材标准';
    else
        set `desc` = '身材一般';
    end if;
end$

调用存储过程

1
2
call myprocedure(175,@desc);
select @`desc`;

查看结果

image-20210306232047574

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

@@global.sort_buffer_size : 这种在变量前加上 “@@” 符号, 叫做 系统变量

3.7、存储函数

1、语法结构

CREATE FUNCTION function_name([param type … ])

RETURNS type

BEGIN

…

END;

2、示例

  • 定义一个函数，获取满足条件（city）的总记录数
1
2
3
4
5
6
7
create function fun1(countryId int)
returns int
begin
    declare cnum int;
    select count(*) into cnum from city where country_id = countryId;
    return cnum;
end$

由于函数有返回值，所以可以使用select 输出结果

1
select fun1(1)$

结果为：

image-20210306234131066

四、触发器

4.1、介绍

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

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

4.2、创建触发器

1、语法结构：

create trigger trigger_name

before/after insert/update/delete

on tbl_name

[ for each row ] – 行级触发器

begin

​ 要执行的语句

end;

2、示例

需求：通过触发器记录 emp 表的数据变更日志 emp_logs，包括增加，修改和删除

3、准备emp脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table emp
(
    id     int(11)     not null auto_increment,
    name   varchar(50) not null comment '姓名',
    age    int(11) comment '年龄',
    salary int(11) comment '薪水',
    primary key (`id`)
) engine = innodb
  default charset = utf8;


insert into emp(id, name, age, salary)
values (null, '金毛狮王', 55, 3800),
       (null, '白眉鹰王', 60, 4000),
       (null, '青翼蝠王', 38, 2800),
       (null, '紫衫龙王', 42, 1800);

4、创建一张日志表

1
2
3
4
5
6
7
8
9
10
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;

5、创建 insert 触发器

1
2
3
4
5
6
7
8
9
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$
  • 测试触发器，执行一条 insert 语句
1
insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);
  • 现在查看 emp_logs 表，发现有了一条记录

image-20210307003100615

6、创建 update 触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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$
  • 测试触发器，更新一条数据
1
update emp set age = 39 where id = 3;
  • 查看 emp_logs 表

image-20210307004443553

emp_logs表中operate_params记录如下

image-20210307004554610

7、创建删除触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
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$
  • 测试触发器，删除一条数据
1
delete from emp where id = 3;
  • 查看 emp_logs 表

image-20210307005623544

4.3、查看触发器

可以通过执行 SHOW TRIGGERS 命令来查看触发器的状态、语法等信息。

1、语法结构

1
show triggers;

2、示例

查看上面创建的触发器

image-20210315215150402

在Statement中封装了定义语句，这里以插入触发器为例

1
2
3
4
5
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

4.4、删除触发器

1、语法

drop trigger [数据库名.]触发器名

如果不指定数据库名，那么默认删除当前表的触发器。

2、示例

删除emp表中的insert触发器

1
drop trigger index_study.emp_logs_insert_trigger;

此时再次使用 show triggers 查看触发器

image-20210315215134287

笔记主要参考来源如下：