深入理解MySQL Ⅱ -- SQL优化、视图、存储过程与触发器

SQL优化

插入数据

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

• 批量插入数据
  
• 手动控制事务
  • 因为默认每有一句DML就会提交一次事务，这样频繁的提交也会影响性能
    
• 主键顺序插入，性能要高于乱序插入。
  

大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p    //linux环境下
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

主键顺序插入性能高于乱序插入

主键优化

在前面我们提到过主键顺序插入的性能是要高于乱序插入的。 那么接下来就来就介绍一下具体的原因，然后再分析一下主键又该如何设计。

首先我们了解几个概念：

数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。

行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不了，将会存储到下一个页中，页与页之间会通过指针连接。

页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2~N行数据(也就是说至少两行数据，如果一行数据过大，会行溢出)，根据主键排列。

我们用图来演示一下这个过程：

下面橙色方框代表的是页结构，不要和B+树中的节点弄混了，并且页分裂与B+树的分裂也是两个东西，不要混淆。

主键顺序插入效果

• 从磁盘中申请页， 主键顺序插入
  
• 第一个页没有满，继续往第一页插入
  
• 当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接
  
• 当第二页写满了，再往第三页写入
  

主键乱序插入效果

• 加入1#,2#页都已经写满了，存放了如图所示的数据
  

• 此时再插入id为50的记录，我们来看看会发生什么现象，会再次开启一个页，写入新的页中吗？

• 不会。因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后。
  

• 但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。
  

• 但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。
  

• 移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针。
  

上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作。

页合并

我们还是通过图示来理解：

• 目前表中已有数据的索引结构(叶子节点)如下
  

• 当我们对已有数据进行删除时，具体的效果如下：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
  

• 当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
  

• 删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页
  

这个里面所发生的合并页的这个现象，就称之为 “页合并”。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

那么我们怎么优化主键呢？

• 满足业务需求的情况下，尽量降低主键的长度。
  • 因为对于一张表来说，聚集索引只有一个但是二级索引可以有多个，而二级索引的叶子里面就是我们的主键，如果主键长、二级索引多，那么会大量的占据磁盘空间，并且在搜索的时候浪费大量的磁盘IO
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
  • 否则排序或者检索的时候会非常的麻烦，影响性能
• 业务操作时，避免对主键的修改。
  • 主键修改会改变索引结构，影响性能

order by优化

MySQL的排序，有两种方式：

• Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
• Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

我们举一个例子：

首先执行一个排序(age, phone没有联合索引，也没有单列索引)

explain select id,age,phone from tb_user order by age ;

由于 age, phone 都没有索引，所以此时排序时，出现Using filesort， 排序性能较低。

我们创建索引，再跟据age进行升序排列

-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);
explain select id,age,phone from tb_user order by age;

此处创建age,phone的联合索引，避免回表查询

建立索引之后，再次进行排序查询，就由原来的Using filesort， 变为了 Using index，性能就是比较高的了。

注意；
排序时,也需要满足最左前缀法则,否则也会出现 filesort。
我们执行：explain select id,age,phone from tb_user order by phone , age;
结果：

因为在创建索引的时候， age是第一个字段，phone是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现 Usingfilesort。

还要注意：创建索引后，根据age, phone进行降序排序；

explain select id,age,phone from tb_user order by age desc , phone desc ;

也会出现 Using index， 但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。 在MySQL8版本中，支持降序索引，我们也可以创建降序索引。

例如现在根据 age 升序排序，phone 倒序排序，我们就要创建如下索引；

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

结果：

图示：

总结

order by优化原则:

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
• 尽量使用覆盖索引，避免回表查询
• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
• 如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）

group by优化

分组操作，我们主要来看看索引对于分组操作的影响。

首先在没有索引的情况下，执行如下SQL，查询执行计划：

explain select profession , count(*) from tb_user group by profession ;

然后，我们再针对于 profession ， age， status(按顺序) 创建一个联合索引，再执行前面相同的SQL查看执行计划；

再执行如下的分组查询SQL，查看执行计划：

我们发现，如果仅仅根据age分组，就会出现 Using temporary ；而如果是 根据profession,age两个字段同时分组，则不会出现 Using temporary。原因是因为对于分组操作，在联合索引中，也是符合最左前缀法则的。

总结

group by优化通过以下两点；

• 在分组操作时，可以通过索引来提高效率。
• 分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比：

通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路:：一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count优化

select count(*) from tb_user ;

在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。
• InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行，也就是数据库每添加一条数据，redis进行记录,但是如果是带条件的count又比较麻烦了)。

在这里我们从用法上对count进行一个优化：

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

用法：

• count（*）
• count（主键）
• count（字段）
• count（数字）

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)

update优化

核心思想就是：避免行锁升级为表锁

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

如以下两条语句：

• update student set no = '123' where id = 1;，这句由于id有主键索引，所以只会锁这一行；
• update student set no = '123' where name = 'test';，这句由于name没有索引，只能去全表遍历一个个比对，所以会把整张表都锁住进行数据更新，解决方法是给name字段添加索引

视图

视图概述

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表(也叫做基表)，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

语法

创建

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

• 如果想要替换某一个视图可以用OR REPLACE
• select语句查询的表也就是我们的基表
• CASCADED 和 LOCAL 用于检查选项后面会说到

查询

查看创建视图语句：SHOW CREATE VIEW 视图名称;
查看视图数据：SELECT * FROM 视图名称 ...... ;

修改

方式一：CREATE OR REPLACE VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

删除

DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

另外我们发现可以通过视图对基表里面的数据进行在插入、修改、删除等操作。但是如果我们插入、修改、删除的数据不符合我们创建视图时的条件，那么这些数据即使发生了改变，我们在视图中也看不到反馈。

所以我们想在定义视图时，如果指定了条件(这个条件也就是我们在查询基表的时候的where等语句)，然后我们在插入、修改、删除数据时，是否可以做到必须满足条件才能操作，否则不能够操作呢？ 答案是可以的，这就需要借助于视图的检查选项了。

检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。

MySQL允许基于一个视图创建另一个视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL，默认值为 CASCADED 。

如果没有开检查选项就不会进行检查。不同版本是不同含义的，要看版本。

• CASCADED
  • 级联
  • 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。
    
• LOCAL
  • 本地
  • 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1。
    

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

• 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
• DISTINCT
• GROUP BY
• HAVING
• UNION 或者 UNION ALL

例如；

create view stu_v_count as select count(*) from student;

上述的视图中，就只有一个单行单列的数据，如果我们对这个视图进行更新或插入，将会报错。

insert into stu_v_count values(10);

视图的作用

• 简单
  • 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
• 安全
  • 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
• 数据独立
  • 视图可帮助用户屏蔽真实表结构变化带来的影响。 （屏蔽了基表的变化对业务的影响）

使用示例：

①为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。

create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user;
select * from tb_user_view;

②查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图

create view tb_stu_course_view as select s.name student_name , s.no student_no ,
c.name course_name from student s, student_course sc , course c where s.id =
sc.studentid and sc.courseid = c.id;
select * from tb_stu_course_view;

存储过程

存储过程概述

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

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

特点；

• 封装，复用
  • 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
• 可以接收参数，也可以返回数据
  • 在存储过程中，可以传递参数，也可以接收返回值。
• 减少网络交互，效率提升
  • 如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

基本语法

创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
	-- SQL语句
END ;

调用

CALL 名称 ([ 参数 ]);

查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名'; -- 查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

删除

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

注意：

在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter 指定SQL语句的结束符。默认是 分号作为结束符,也就是说如果不单独指定的话，分号结束，sql代码块会被分为几个部分执行，
delimiter $ ，则 $ 符作为结束符。

例子；

-- 存储过程基本语法
-- 创建
create procedure p1()
begin
	select count(*) from student;
end;

-- 调用
call p1();

-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
show create procedure p1;

-- 删除
drop procedure if exists p1;

变量

在MySQL中变量分为三种类型:

• 系统变量
• 用户定义变量
• 局部变量

接下来我们一个个说说：

系统变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

• 全局变量(GLOBAL): 全局变量针对于所有的会话。
• 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。

• 查看系统变量
  

• 设置系统变量
  

用户定义变量

用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以。其作用域为当前连接。

• 赋值：

  • 方式一：赋值时，可以使用 = ，也可以使用 := 。
    
  • 方式二：
    

• 使用
  

注意：
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

例子：

-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
-- 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;
select @abc;   //null

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块。

• 声明
  • 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。
    
• 赋值
  

例子：

-- 声明局部变量 - declare
-- 赋值
create procedure p2()
begin
	declare stu_count int default 0;
	select count(*) into stu_count from student;
	select stu_count;
end;

call p2();

参数

参数的类型，主要分为以下三种：IN、OUT、INOUT。 具体的含义如下：

用法：

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
	-- SQL语句
END ;

例子：

create procedure p4(in score int, out result varchar(10))
begin
	if score >= 85 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
end;

-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);

select @result;

流程控制

if

if 用于做条件判断，具体的语法结构为：

IF 条件1 THEN
	.....
ELSEIF 条件2 THEN 	-- 可选
	.....
ELSE 				-- 可选
	.....
END IF;

在if条件判断的结构中，ELSE IF 结构可以有多个，也可以没有。 ELSE结构可以有，也可以没有。

例子：

根据定义的分数score变量，判定当前分数对应的分数等级。

• score >= 85分，等级为优秀。
• score >= 60分 且 score < 85分，等级为及格。
• score < 60分，等级为不及格。

create procedure p3()
begin
	declare score int default 58;
	declare result varchar(10);
	if score >= 85 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
	select result;
end;

call p3();

case

用于流程控制

语法1：

-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，
执行statement_list2， 否则就执行 statement_list
CASE case_value
	WHEN when_value1 THEN statement_list1
	[ WHEN when_value2 THEN statement_list2] ...
	[ ELSE statement_list ]
END CASE;

语法2；

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
立时，执行statement_list2， 否则就执行 statement_list
CASE
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition2 THEN statement_list2] ...
	[ELSE statement_list]
END CASE;

例子；

create procedure p6(in month int)
begin
	declare result varchar(10);
	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 := '第四季度';
		else
			set result := '非法参数';
	end case ;

	select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);
end;

call p6(16);

注意：如果判定条件有多个，多个条件之间，可以使用 and 或 or 进行连接。

while

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
	SQL逻辑...
END WHILE;

repeat

repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环 。具体语法为：

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
	SQL逻辑...
	UNTIL 条件
END REPEAT;

loop

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合以下两个语句使用：

• LEAVE ：配合循环使用，退出循环
• ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP
	SQL逻辑...
END LOOP [end_label];

LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环

上述语法中出现的 begin_label，end_label，label 指的都是我们所自定义的标记。

游标

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

语法：

• 声明游标
  

• 打开游标
  

• 获取游标记录
  

• 关闭游标
  

有些类似于java里面的迭代器

案例；
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

思路；

– 逻辑:

• 声明游标, 存储查询结果集
• 准备: 创建表结构
• 开启游标
• 获取游标中的记录
• 插入数据到新表中
• 关闭游标

create procedure p11(in uage int)
begin
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for select name,profession from tb_user where age <=uage;

	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
		id int primary key auto_increment,
		name varchar(100),
		profession varchar(100)
	);
	
	open u_cursor;
	while true do
		fetch u_cursor into uname,upro;
		insert into tb_user_pro values (null, uname, upro);
	end while;
	close u_cursor;
end;

call p11(30);

上述的存储过程，最终我们在调用的过程中，会报错，之所以报错是因为上面的while循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。

但是此时，tb_user_pro表结构及其数据都已经插入成功了，我们可以直接刷新表结构，检查表结构中的数据。

上述的功能，虽然我们实现了，但是逻辑并不完善，而且程序执行完毕，获取不到数据，数据库还报错。 接下来，我们就需要来完成这个存储过程，并且解决这个问题。

要想解决这个问题，就需要通过MySQL中提供的 条件处理程序 Handler 来解决。

条件处理程序

可以理解为异常处理

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE handler_action HANDLER FOR condition_value [, condition_value]
... statement ;

handler_action 的取值：
	CONTINUE: 继续执行当前程序
	EXIT: 终止执行当前程序
	
condition_value 的取值：
	SQLSTATE sqlstate_value: 状态码，如 02000
	
	SQLWARNING: 所有以01开头的SQLSTATE代码的简写
	NOT FOUND: 所有以02开头的SQLSTATE代码的简写
	SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

我们完善上一个案例的代码：

create procedure p1l(in uage int)

	begin

		declare uname varchar(100); 

		decLare upro varchar(100);

		declare u_cursor cursor for select name,profession from tb_user where age <= uage; 

		当 条件处理程序的处理的状态码为02000的时候，就会退出。
		declare exit handler for SQLSTATE '02000'close u_cursor;

		drop table if exists tb_user_pro; 

		create table if not exists tb_user_pro(

		id int primary key auto_increment, 

		name varchar(100), 

		profession varchar(100)

		);

		open u_cursor; 

		while true do 

		fetch u_cursor into uname,Upro; 

		insert into tb_user_pro values(null,uname,Upro); 

		end while;

		close u_cursor; 

	end;

具体的错误状态码，可以参考官方文档：
https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
	-- SQL语句
RETURN ...;
END ;

存储函数必须有返回值，而存储过程可以没有返回值

characteristic指定传入参数的特性：

• DETERMINISTIC：相同的输入参数总是产生相同的结果
• NO SQL ：不包含 SQL 语句
• READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

案例；

计算从1累加到n的值，n为传入的参数值。

create function fun1(n int)
returns int deterministic
begin
	declare total int default 0;
	
	while n>0 do
		set total := total + n;
		set n := n - 1;
	end while;
	
	return total;
end;

select fun1(50);

在mysql8.0版本中binlog默认是开启的，一旦开启了，mysql就要求在定义存储过程时，需要指定characteristic特性，否则就会报如下错误：

触发器

触发器概述

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

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

触发器可以理解为Vue中的监听属性

比如通过insert/update/delete对表造成了5行改动；
行级触发会触发5次，而语句级触发只会触发1次

触发器分为以下三种：

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

基本语法

创建

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt ;
END;

查看

SHOW TRIGGERS ;

删除

DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库 。