玩转MySQL：详析存储过程与触发器

引言

前面的MySQL系列章节中，一直在反复讲述MySQL一些偏理论、底层的知识，很少有涉及到实用技巧的分享，而在本章中则会阐述MySQL一个特别实用的功能，即MySQL的存储过程和触发器。

在项目的业务开发中，每条SQL语句不会太过复杂，通常就由几行SQL组成，但往往在一些复杂的业务需求下，SQL操作不会那么简单，有时写着写着，可能会编写出一条由几百行、甚至上千行SQL组成的语句，这种大SQL执行的效率通常会异常的缓慢，因此需要从各方面去尽可能的优化它，而存储过程则是专门为这类SQL而创造的，下面咱们一起来聊一聊它。

MySQL起初并不支持存储过程，而是到了MySQL5.0版本才支持存储过程的编写与执行，在MySQL中存储过程主要分为两类，一类是普通的存储过程，另一类则是触发器类型的存储过程，但如若你海不了解啥是触发器，就随我一点点往下看，好戏，开场啦！

一、初识MySQL的存储过程

Stored Procedure存储过程是数据库系统中一个十分重要的功能，使用存储过程可以大幅度缩短大SQL的响应时间，同时也可以提高数据库编程的灵活性，但一般的开发者很少去主动编写存储过程，通常都会由专门的数据库工程师去负责撰写，但大多数中小型企业并不会将岗位划分的太过细致，因此作为一个合格的后端开发，对于这块内容也需要有一定程度上的掌握。

先来简单的聊一聊啥是存储过程吧，存储过程是一组为了完成特定功能的SQL语句集合，使用存储过程的目的在于：将常用且复杂的SQL语句预先写好，然后用一个指定名称存储起来，这个过程经过MySQL编译解析、执行优化后存储在数据库中，因此称为存储过程。当以后需要使用这个过程时，只需调用根据名称调用即可。

其实存储过程和Java中的方法、其他语言中的函数十分类似，也就是先将一堆代码抽象成一个函数，当之后需要使用时，不需要再重写一遍代码，而是直接根据名称调用相应的函数/方法即可。

对比常规的SQL语句来说，普通SQL在执行时需要先经过编译、分析、优化等过程，最后再执行，而存储过程则不需要，一般存储过程都是预先已经编译过的，这就好比咱们在讲《JVM-执行引擎》聊到过的JIT即时编译器一样，为了提升一些常用代码的执行效率，JIT会将热点代码编译成本地机器码，以此省略解释器翻译执行的步骤，从而做到提升性能的目的。

但使用存储过程有利有弊，具备的优点如下：

复用性：存储过程被创建后，可以在程序中被反复调用，不必重新编写该存储过程的SQL语句，同时库表结构发生更改时，只需要修改数据库中的存储过程，无需修改业务代码，也就意味着不会影响到调用它的应用程序源代码。

灵活性：普通的SQL语句很难像写代码那么自由，而存储过程可以用流程控制语句编写，也支持在其中定义变量，有很强的灵活性，可以完成复杂的条件查询和较繁琐的运算。

省资源：普通的SQL一般都会存储在客户端，如Java中的dao/mapper层，每次执行SQL需要通过网络将SQL语句发送给数据库执行，而存储过程是保存在MySQL中的，因此当客户端调用存储过程时，只需要通过网络传送存储过程的调用语句和参数，无需将一条大SQL通过网络传输，从而可降低网络负载。

高性能：存储过程执行多次后，会将SQL语句编译成机器码驻留在线程缓冲区，在以后的调用中，只需要从缓冲区中执行机器码即可，无需再次编译执行，从而提高了系统的效率和性能。

安全性：对于不同的存储过程，可根据权限设置执行的用户，因此对于一些特殊的SQL，例如清空表这类操作，可以设定root、admin用户才可执行。同时由于存储过程编写好之后，对于客户端而言是黑盒的，因此减小了SQL被暴露的风险。

但还是那句话，凡事有利必有弊，存储过程也会带来一些之前不存在的问题：

CPU开销大：如果一个存储过程中涉及大量逻辑运算工作，会导致MySQL所在的服务器CPU飙升，因而会影响正常业务的执行，有可能导致MySQL在线上出现抖动，毕竟MySQL在设计时更注重的是数据存储和检索，对于计算性的任务并不擅长。

内存占用高：为了尽可能地提升执行效率，因此当一个数据库连接反复调用某个存储过程后，MySQL会直接将该存储过程的机器码放入到连接的线程私有区中，当MySQL中的大量连接都在频繁调用存储过程时，这必然会导致内存占用率同样飙升。

维护性差：一方面是过于复杂的存储过程，普通的后端开发人员很难看懂，毕竟存储过程类似于一门新的语言，不同语言之间跨度较大。另一方面是很少有数据库的存储过程支持Debug调试，MySQL的存储过程就不支持，这也就意味着Bug出现时，无法像应用程序那样正常调试排查，必须得采取“人肉排查”模式，即一步步拆解存储过程并排查。

基于上述原因，咱们在不必要使用存储过程的情况下，就尽量减少存储过程的编写，除非特定的业务需求导致不得不用时，再将注意力转向这块。不过话虽这么说，但少量的存储过程并不会造成太大影响，除非你没事在MySQL中写几十、上百个存储过程，否则基本上不会导致“不良反应”出现。

对存储过程有了基本认知后，接着来聊一聊MySQL中该如何定义、调用及管理存储过程。

二、存储过程的定义、调用与管理

前面简单提过一嘴，存储过程类似于一门新的语言，在其中存在专门的语法规则，因此想要撰写一个高效的存储过程之前，我们得先掌握存储过程中的一些基本语法，例如创建、变量、流程控制、循环等基础语法。

但好在大家都并非刚学编程的小白，因此这些在其他语言中都存在的语法，其实我们只需要搞清楚在MySQL中的关键字即可，所以上手速度还是非常快的，那么 Les't Go~

2.1、存储过程的语法

先来看看存储过程的定义语法，如下：

DELIMITER $ -- 创建的语法：指定名称、入参、出参 CREATE PROCEDURE 存储过程名称(返回类型 参数名1 参数类型1, ....) [ ...这里在后面讲... ] -- 表示开始编写存储过程体 BEGIN -- 具体组成存储过程的SQL语句.... -- 表示到这里为止，存储过程结束 END $ DELIMITER ; 复制代码

实际上这个语法和其他语言定义函数/方法的过程类似，例如Java方法的定义：

访问修饰符 返回类型 方法名(参数类型 参数名称, ....){ // 方法体... } 复制代码

存储过程的BEGIN、END就类似于Java方法的{}，用来区分起始和结束的边界。OK~，所有语言的函数/方法定义时，一般都会分为四类，如下：

• ①无参无返回。

• ②有参无返回。

• ③无参有返回。

• ④有参有返回。

而SQL的存储过程也不例外，同样也支持这四种定义，主要依赖于IN、OUT、INOUT三个关键字来区分：

• 定义存储过程时，没有入参也没有出参，代表无参无返回类型。

• 定义存储过程时，仅定义了带有IN类型的参数，表示有参无返回类型。

• 定义存储过程时，仅定义了带有OUT类型的参数，表示无参有返回类型。

• 定义存储过程时，同时定义了带有IN、OUT类型的参数，或定义了带有INOUT类型的参数，表示有参有返回类型。

在上述给出的语法体中，最开始有一个DELIMITER $是什么意思呢？

其实这表示指定结束标识，在MySQL中默认是以;分号作为一条语句的结束标识，因此当存储过程的过程体中，如果包含了SQL语句，SQL语句以;结束时，MySQL会认为存储过程的定义也结束了，过程体就会和;结束符冲突，所以一般咱们要重新定义结束符，例如DELIMITER $，表示以$作为结束标识，只有当MySQL识别到$符时，才会认为结束了。

但记得在结束之后，要再次把结束符改回;，即DELIMITER ;。

还有一条[...这里在后面讲...]是啥意思呢？这是指定存储过程的约束条件，取值范围有很多，如下：

• ①LANGUAGE SQL

• ②[NOT] DETERMINISTIC

• ③{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

• ④SQL SECURITY { DEFINER | INVOKER }

• ⑤COMMENT '....'

是不是看起来很头大？确实，我写起来也头大，但接着往下看，分别解释一下吧。

• ①说明存储过程中的过程体是否由SQL语句组成。

• ②说明存储过程的返回值是否为固定的，没有[NOT]表示为固定的，默认为非固定的。

• ③说明过程体使用SQL语句的限制： CONTAINS SQL：表示当前存储过程包含SQL，但不包含读写数据的SQL语句。 NO SQL：表示当前存储过程中不包含任何SQL语句。 READS SQL DATA：表示当前存储过程中包含读数据的SQL语句。 MODIFIES SQL DATA：表示当前存储过程中包含写数据的SQL语句。

• ④说明哪些用户可以调用当前创建的存储过程： DEFINER：表示只有定义当前存储过程的用户才能调用。 INVOKER：表示任何具备访问权限的用户都能调用。

• ⑤注释信息，可以用来描述当前创建的存储过程。

上述的五条分别和之前的五种取值范围一一对应，估计大家现在直接来看会有些懵逼，其实这些大概了解即可，无需过多关注，一般在写存储过程的时候不会加上这些条件，通常都是使用默认的。

2.1.1、存储过程的定义

上面刚刚了解了存储过程的基础语法，但多少有点犯迷糊，因此接下来简单地先写几个存储过程简单感受一下：

-- 查询用户表中的所有信息 select * from `zz_users`; +---------+-----------+----------+----------+---------------------+ | user_id | user_name | user_sex | password | register_time | +------