MySQL-存储过程/函数/触发器

什么是存储过程

简单点说就是普通的SQL语句加上一些其他编程语言进行逻辑判断的一些成分, 比如循环, 函数, 这些, 但本质上是一种SQL语句的集合, 或者说叫做, 过程化SQL语言

---

存储过程可称为过程化SQL语言，是在普通SQL语句的基础上增加了编程语言的特点，把数据操作语句(DML)和查询语句(DQL)组织在过程化代码中，通过逻辑判断、循环等操作实现复杂计算的程序语言。换句话说，存储过程其实就是数据库内置的一种编程语言，这种编程语言也有自己的变量、if语句、循环语句等。在一个存储过程中可以将多条SQL语句以逻辑代码的方式将其串联起来，执行这个存储过程就是将这些SQL语句按照一定的逻辑去执行，所以一个存储过程也可以看做是一组为了完成特定功能的SQL 语句集。每一个存储过程都是一个数据库对象，就像table和view一样，存储在数据库当中，一次编译永久有效。并且每一个存储过程都有自己的名字。

---

主要作用 : 客户端程序通过存储过程的名字来调用存储过程。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

存储过程的优缺点

优点：速度快。 降低了应用服务器和数据库服务器之间网络通讯的开销。尤其在数据量庞大的情况下效果显著。

缺点：移植性差。编写难度大。维护性差。 - 每一个数据库都有自己的存储过程的语法规则，这种语法规则不是通用的。一旦使用了存储过程，则数据库产品很难更换，例如：编写了mysql的存储过程，这段代码只能在mysql中运行，无法在oracle数据库中运行。 - 对于数据库存储过程这种语法来说，没有专业的IDE工具（集成开发环境），所以编码速度较低。自然维护的成本也会较高。

在实际开发中，存储过程还是很少使用的。只有在系统遇到了性能瓶颈，在进行优化的时候，对于大数量的应用来说，可以考虑使用一些。

存储过程的基本使用

存储过程的创建

下面展示的创建存储过程p1的流程

/* 下面展示一下创建一个最简单的存储过程 */
# 如果存在这个存储过程就删除
drop procedure if exists p1;
# 创建一个存储过程
create procedure p1(#这里将来可以写参数)
begin # 执行逻辑在这里写
end; 

存储过程的调用

调用上面创建的存储过程p1

# 调用一下存储过程
call p1();

存储过程的删除

删除上面创建的存储过程p1

# 删除一下存储过程
drop procedure if exists p1;

存储过程的查看

在navicat中我们的存储过程都归入到了fx(函数内部)

查看创建存储过程的语句
类似于查看建表语句

# 展示一下建表的SQL(用dept举例)
show create table dept;
# 展示一下创建存储过程的SQL
show create procedure p1;

第一列的Procedure是存储过程的名字, 第三列的Create Procedure是创建存储过程的语句

还有一种方法是通过系统表information_schema.ROUTINES查看存储过程的详细信息
首先我们展示一下这个表的结构(特别复杂, 不是所有的数据都是我们需要的, 我们只需要以下几种)

• SPECIFIC_NAME：存储过程的具体名称，包括该存储过程的名字，参数列表(类似方法签名)。
• ROUTINE_SCHEMA：存储过程所在的数据库名称。
• ROUTINE_NAME：存储过程的名称(只是一个名字)。
• ROUTINE_TYPE：PROCEDURE表示是一个存储过程，FUNCTION表示是一个函数。
• ROUTINE_DEFINITION：存储过程的定义语句。
• CREATED：存储过程的创建时间。
• LAST_ALTERED：存储过程的最后修改时间。
• DATA_TYPE：存储过程的返回值类型、参数类型等。

下面我们模拟一下查询以下p1这个存储过程的上面的几个数据(忽略了routine_definition)

delimiter命令

简单点说这个命令的作用就是修改MySQL中的定界符 ;, MySQL中默认的是使用分号 ; 作为SQL语句的结尾, 但是如果在dos命令行窗口中创建存储过程的话, 中途操作使用分号会导致失败, 所以需要修改定界符(Navicat中不需要)
下面简单展示一下即可

# 把定界符修改为 //
delimiter //

可以观察到此时只有输入 // SQL才可以正常结束执行

MySQL中的变量

MySQL中的变量包括系统变量, 用户变量, 局部变量

系统变量

MySQL 系统变量是指在 MySQL 服务器运行时控制其行为的参数。这些变量可以被设置为特定的值来改变服务器的默认设置，以满足不同的需求。

---

MySQL 系统变量可以具有全局（global）或会话（session）作用域。

• 全局作用域 : 对所有连接和所有数据库都适用
• 会话作用域 : 只对当前连接和当前数据库适用

---

查看系统变量

# 基础语法
-- 1. 查出所有的系统变量
show [global|session] variables;
-- 2. 模糊的知道某一个系统变量的名称(模糊查询)
show [global|session] variables like '[模糊的内容]';
-- 3. 查询一个确切的系统变量的名称
select @@[global|session].[变量名称];

注意, 当没有global/session的时候, 默认的就是session
下面展示简单展示一下全局系统变量, 测试一下模糊查询当前的字符集是什么

/* 查看系统变量 */
# 1. 查询所有的全局系统变量
show global variables;
# 2. 模糊查询一下当前的系统字符集
show global variables like '%character%';
# 3. 确切的查询一下character_set_client(客户端的字符集)
select @@global.character_set_client;

下面展示简单展示一下会话系统变量, 测试一下模糊查询当前的字符集是什么

/* 查看系统变量 */
# 1. 查询所有的会话系统变量
show session variables;
# 2. 模糊查询一下当前的会话字符集
show session variables like '%character%';
# 3. 确切的查询一下character_set_client(客户端的字符集)
select @@session.character_set_client;

---

更改系统变量
基础语法

set [global|session] 变量名 = 值;set @@[global|session] 变量名 = 值;

比如我们之前就用过这个方法修改过隔离级别, 下面我们演示一下

/* 更改系统变量 */
# 1. 模糊查询一下隔离级别
show session variables like '%transaction%';
# 2. 把当前会话的隔离级别改为 read committed;
set session transaction_isolation = 'read-committed';

执行结果

---

无论是全局设置还是会话设置, 当mysql服务重启之后, 当前的配置都会失效, 但是我们可以通过修改my.ini这个配置文件来达到永久修改的目的（my.ini是MySQL数据库默认的系统级配置文件，默认是不存在的，需要新建，并参考一些资料进行配置。）windows系统是my.inilinux系统是my.cnfmy.ini文件通常放在mysql安装的根目录下
关于my.ini
我们之前简单的说过这个问题, 其实这就是配置文件

用户变量

用户自定义的变量, 只在当前会话有效, 所有的用户变量都以@开头, 而且所有的用户变量都不需要声明可以直接赋值使用, 如果不给用户变量赋值直接查询的话会查出来Null

/* 演示一下用户变量 */
# 给用户变量赋值
set @temp_val := 100;
set @temp_val1 := 'ddd';
select sal into @temp_val2 from emp where ename = 'smith';
# 查询一下用户变量
select @temp_val, @temp_val1, @temp_val2;

注意：mysql中变量不需要声明。直接赋值就行。如果没有声明变量，直接读取该变量，返回null

局部变量

在存储的过程中可以使用局部变量, 使用declare进行声明, 在begin和end之间有效, 注意, 在进行传参的时候, 传入的参数, 本质上也是一种局部变量

声明局部变量

# default后面是默认值
declare 变量名 数据类型 [default ...];

这里注意一下, 关于declare的位置目前来说我们还不确定具体的情况, 所以所有的declare都在存储过程的最前面声明

---

变量的赋值

set 变量名 := 值;
select 字段名 into 变量 from 表名...;

下面简单演示一下局部变量的使用流程

/* 测试一下局部变量的使用 */
drop procedure if exists p1;create procedure p1()
begin# 声明局部变量declare i int default 1;declare j varchar(10);declare m decimal(10, 2) default 0.00;declare n datetime;# 给局部变量赋值set i := 10;set j := 'ttt';set m := 1.23;set n := '2024-10-23 20:52:22';# 查询一下局部变量select i, j, m, n;
end; call p1();

参数

存储过程的参数包括三种形式

• in : 入参(未指定时, 默认是in)
• out : 出参(类似返回值)
• inout : 既是入参又是出参(类似Java中的引用)
  定义在存储过程的()里面
  注意一点的是, out, 或者是inout传入/接收的就是用户变量

create procedure p2(out sum int)
begin...
end;call p2(@sum);

if语句

没什么可说的, 是个编程语言就有, 算非常基础的语法点了

# 基础语法格式
if 条件 then ...
else if 条件 then ...
else if 条件 then ...
else if 条件 then ...
else if 条件 then ...
else ...
end if;

案例：员工月薪sal，超过10000的属于“高收入”，6000到10000的属于“中收入”，少于6000的属于“低收入”。
下面的存储过程可以完成这一需求

# 删除一下这个存储过程
drop procedure if exists p2;# 创建存储过程
create procedure p2(in sal int, out grade varchar(20))
begin if sal > 10000 thenset grade := '高收入';elseif sal between 6000 and 10000 then set grade := '中收入';else set grade := '低收入';end if;
end;# 调用存储过程(查一下 'smith' 的工资)
select sal into @sal from emp where ename = 'smith';
call p2(@sal, @grade);# 展示工资水平
select @grade;

case语句

有点类似switch-case语句
基础语法

# 语法1
case 值when 值1 then ...when 值2 then ...when 值3 then ...when 值4 then ...else ...
end case;# 语法2
case when 条件1 then ...when 条件2 then ...when 条件3 then ...when 条件4 then ...else ...
end case;

取一个最经典的例子, 根据不同月份, 输出季节
3 4 5月份春季。6 7 8月份夏季。9 10 11月份秋季。12 1 2 冬季。其他非法

/* 测试一下case语句 */
# 删除一下存储过程
drop procedure if exists p3;# 创建一下存储过程
create procedure p3(in month int, out season varchar(10))
begin case monthwhen 3 then set season := '春季';when 4 then set season := '春季';when 5 then set season := '春季';when 6 then set season := '夏季';when 7 then set season := '夏季';when 8 then set season := '夏季';when 9 then set season := '秋季';when 10 then set season := '秋季';when 11 then set season := '秋季';when 12 then set season := '冬季';when 1 then set season := '冬季';when 2 then set season := '冬季';else set season := '无效输入';end case;
end; # 调用存储过程, 展示结果
call p3(12, @season);
select @season;/* 测试一下case语句 */
# 删除一下存储过程
drop procedure if exists p3;# 创建一下存储过程
create procedure p3(in month int, out season varchar(10))
begin case when month in (3,4,5) then set season := '春季';when month in (6,7,8) then set season := '夏季';when month in (9,10,11) then set season := '秋季';when month in (12,1,2) then set season := '冬季';else set season := '无效输入';end case;
end; # 调用存储过程, 展示结果
call p3(12, @season);
select @season;

while循环

基础语法

while 循环条件 do循环体;
end while

求1 ~ n 范围内的偶数和

/* 测试一下while循环 */
# 删除一下存储过程
drop procedure if exists p4;# 创建一下存储过程
create procedure p4(in n int, out sum int)
begin declare i int default 1;set sum := 0;while i <= n doif i % 2 = 0 then set sum := sum + i;end if;set i := i + 1;end while;
end; # 调用一下存储过程
call p4(100, @sum);
select @sum;

repeat循环

基础语法

repeat循环体;until 条件
end repeat;

注意：条件成立时结束循环。

案例：传入一个数字n，计算1~n中所有偶数的和。

create procedure mypro(in n int, out sum int)
begin set sum := 0;repeat if n % 2 = 0 then set sum := sum + n;end if;set n := n - 1;until n <= 0end repeat;
end;call mypro(10, @sum);
select @sum;

loop循环

# 基础语法
循环名称 : loop循环体leave 循环名称;iterate 循环名称;
end loop;

这个循环没有限制的话就是一个十循环, 所以要加上leave语句(相当于break语句跳出循环), iterate语句(相当于continue)进行调控

案例 : 打印 1, 2, 4, 5

/* 测试一下loop循环 */
drop procedure if exists p5;create procedure p5()
begindeclare i int default 0;myloop : loop set i := i + 1;	if i = 3 theniterate myloop; end if;if i = 6 thenleave myloop;end if;	select i;	end loop;
end; call p5();

游标cursor

游标(cursor) 可以理解为一个指向结果集中某条记录的指针, 允许程序逐一访问结果集中的每条记录, 并对其进行逐行的操作和处理.
使用游标时，需要在存储过程或函数中定义一个游标变量，并通过 DECLARE 语句进行声明和初始化。然后，使用 OPEN 语句打开游标，使用 FETCH 语句逐行获取游标指向的记录，并进行处理。最后，使用 CLOSE 语句关闭游标，释放相关资源。游标可以大大地提高数据库查询的灵活性和效率。

---

使用游标的步骤

• 声明游标 : DECLARE 游标名称 CURSOR FOR DQL查询语句
• 打开游标 : OPEN 游标名称
• 操作游标 : FETCH 游标名称 INTO [变量1,变量2…]
• 关闭游标 : CLOSE 游标名称

---

需求 : 从dept表中查询部门编号和部门名称, 创建一张新表new_dept, 把查询到的结果插入到新表当中
实现功能代码如下

/* 展示一下游标cursor的使用 */
# 删除存储过程
drop procedure if exists p6;# 创建存储过程
create procedure p6()
begin-- 声明一下局部变量declare no int;declare name varchar(20);-- 声明游标declare d_cursor cursor for select deptno, dname from dept;-- 创建一张新表create table new_dept(dno int primary key, dname varchar(20));-- 打开游标open d_cursor;-- 操作游标while true dofetch d_cursor into no, name;insert into new_dept(dno, dname) values (no, name);end while;-- 关闭游标close d_cursor;
end; # 调用存储过程
call p6();# 展示执行结果
select * from new_dept;

这里需要注意的是 : 关于declare声明一定要在所有的程序之前完成(目前的已知理解)
执行结果是正确的, 但是需要注意的是, 这里有一个异常信息


可以看到, 虽然操作成功了, 但是出来了一个异常信息, 因为我们while循环的条件是true, fetch的时候会出现元素不足的情况, 如何处理需要我们下面说的异常处理…

注意：声明局部变量和声明游标有顺序要求，局部变量的声明需要在游标声明之前完成。

捕获异常并处理

类似于Java中的异常处理机制, SQL中也有类似的方法, 就是将异常进行contiue(类似try-catch捕获), exit(类似throws上抛)

语法格式 :

DECLARE handler_name HANDLER FOR condition_value action_statement;

这里的DECLARE是声明的意思, handler_name代表你的处理态度(continue/exit), HANDLER FOR就是一个关键字, condition_value的填写看我们下面的解释, action_statement是你的处理方式

• handler_name 表示异常处理程序的名称，重要取值包括：
  1. CONTINUE：发生异常后，程序不会终止，会正常执行后续的过程。(捕捉)
  2. EXIT：发生异常后，终止存储过程的执行。（上抛）
• condition_value 是指捕获的异常，重要取值包括：
  1. SQLSTATE sqlstate_value，例如：SQLSTATE ‘02000’
  2. SQLWARNING，代表所有01开头的SQLSTATE
  3. NOT FOUND，代表所有02开头的SQLSTATE
  4. SQLEXCEPTION，代表除了01和02开头的所有SQLSTATE
• action_statement 是指异常发生时执行的语句，例如：CLOSE cursor_name

注意 : 关于conditon_value具体应该填那个, 建议上网查一查, 因为这个并不是严格对应的, 比如上面的1329号异常就可以用NOT FOUND 或者是 SQLSTATE '02000'都是可以的

下面我们处理一下之前出现的那个异常

/* 展示一下游标cursor的使用 */
# 删除存储过程
drop procedure if exists p6;# 创建存储过程
create procedure p6()
begin-- 声明一下局部变量declare no int;declare name varchar(20);-- 声明游标declare d_cursor cursor for select deptno, dname from dept;-- 处理一下之前出现的那个异常1329declare exit handler for NOT FOUND close d_cursor;# 下面这种处理方式也是可以的declare exit handler for SQLSTATE '02000' close d_cursor;-- 创建一张新表drop table if exists new_dept;create table new_dept(dno int primary key, dname varchar(20));-- 打开游标open d_cursor;-- 操作游标while true dofetch d_cursor into no, name;insert into new_dept(dno, dname) values (no, name);end while;-- 关闭游标close d_cursor;
end; # 调用存储过程
call p6();# 展示执行结果
select * from new_dept;

这样就不会出现异常了

存储函数

存储函数 : 带返回值的存储过程, 参数只允许是in(但不能显示的写in), 没有out, 也没有inout
基础语法

CREATE FUNCTION 函数名称(参数列表) RETURNS 返回值类型 [函数特征]
BEGIN-- 函数体RETURN ...;
END;

关于函数的特征, 其实就是一种MySQL的一种优化策略, 通常有下面三种

• deterministic : 用这个特征标记的函数为确定性函数, 确定性函数指的就是对同一个入参, 只会对应相同的返回结果, 也就是一对一, 不会出现同一个函数传入相同的参数结果不一样的情况, 所以MySQL底层就可以根据这一特点对该函数进行优化, 对于一个传入的参数的返回结果, MySQL底层会进行缓存, 当下次再次传入这个参数的时候, 就直接访问之前缓存的结果, 从而提高性能
• no sql : 用该特征标记的函数执行过程中不会查询数据库, 如果确定没有查询语句建议使用, 告诉MySQL优化器不需要考虑使用查询缓存和优化器缓存来优化这个函数, 这样就可以避免不必要的查询消耗产生, 从而提高性能
• reads sql data : 这个就跟上面的no sql恰好相反, 用该特征标记该函数会进行查询操作，告诉 MySQL 优化器这个函数需要查询数据库的数据，可以使用查询缓存来缓存结果，从而提高查询性能；同时 MySQL 还会针对该函数的查询进行优化器缓存处理。
• 一个函数具有多个特征的时候一起写就可以了

下面用这个存储函数来测试一个最简单的例子, 计算 1~n 之间的所有偶数和(特征用deteministic和no sql)

/* 测试一下存储函数 */
# 删除存储函数
drop function if exists sum_odd;# 创建存储函数
create function sum_odd(n int) returns int deterministic no sql
begindeclare sum int default 0;while n > 0 doif n % 2 = 0 then set sum := sum + n;end if;set n := n - 1;end while;return sum;
end;# 调用函数的时候不用call关键字
set @res := sum_odd(100);# 查询返回结果
select @res;

触发器

触发器概述

MySQL中触发器是一种数据库对象, 它是和表相关联的特殊的程序, 它可以在特定的数据操作, 例如插入(INSERT), 更新(UPDATE), 删除(DELETE) 这些DML语句操作时触发并自动执行. MySQL中的触发器机制可以使数据库开发人员能够在数据的不同状态之间维护一致性和完整性, 并且可以为特定的数据库表自动执行操作

---

触发器的作用主要有以下几个方面：

1. 强制实施业务规则：触发器可以帮助确保数据表中的业务规则得到强制执行，例如检查插入或更新的数据是否符合某些规则。
2. 数据审计：触发器可以声明在执行数据修改时自动记日志或审计数据变化的操作，使数据对数据库管理员和 SQL 审计人员更易于追踪和审计。
3. 执行特定业务操作：触发器可以自动执行特定的业务操作，例如计算数据行的总数、计算平均值或总和等。

---

MySQL 触发器分为两种类型: BEFORE 和 AFTER。BEFORE 触发器在执行 INSERT、UPDATE、DELETE 语句之前执行，而 AFTER 触发器在执行 INSERT、UPDATE、DELETE 语句之后执行。

创建触发器的语法如下

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW
BEGIN-- 触发器执行逻辑
END;

• trigger_name : 触发器名称
• BEFORE/AFTER : 触发器的类型, 可以是before或者是after
• INSERT/UPDATE/DELETE : 触发器监控的DML调用类型
• table_name : 触发器绑定的表名
• FOR EACH ROW : 表示触发器在每行受到DML影响之后都会执行

---

需要注意的是，触发器是一种高级的数据库功能，只有在必要的情况下才应该使用，例如在需要实施强制性业务规则时。过多的触发器和复杂的触发器逻辑可能会影响查询性能和扩展性。

---

NEW & OLD关键字

在 MySQL 触发器中，NEW 和 OLD 是两个特殊的关键字，用于引用在触发器中受到修改的行的新值和旧值。具体而言：

• NEW：在触发 INSERT 或 UPDATE 操作期间，NEW 用于引用将要插入或更新到表中的新行的值。
• OLD：在触发 UPDATE 或 DELETE 操作期间，OLD 用于引用更新或删除之前在表中的旧行的值。

通俗的讲，NEW 是指触发器执行的操作所要插入或更新到当前行中的新数据；而 OLD 则是指当前行在触发器执行前原本的数据。

在MySQL 触发器中，NEW 和 OLD 使用方法是相似的。在触发器中，可以像引用表的其他列一样引用 NEW 和 OLD。例如，可以使用 OLD.column_name 从旧行中引用列值，也可以使用 NEW.column_name 从新行中引用列值。

示例：

假设有一个名为 my_table 的表，其中包含一个名为 quantity 的列。当在该表上执行 UPDATE 操作时，以下触发器会将旧值 OLD.quantity 累加到新值 NEW.quantity 中：

CREATE TRIGGER my_trigger
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
SET NEW.quantity = NEW.quantity + OLD.quantity;
END;

在此触发器中，OLD.quantity 引用原始行的 quantity 值（旧值），而 NEW.quantity 引用更新行的 quantity 值（新值）。在触发器执行期间，数据行的 quantity 值将设置为旧值加上新值

---

触发器的使用

简单测试一个update的触发器, 当对某一个列进行修改的时候, 我们添加一个日志记录

/* 测试一下触发器 */
drop table if EXISTS person;-- 人员信息表
create table person(id int primary key auto_increment,name varchar(255),sal int
);-- update_person 人员更新日志表
drop table if EXISTS update_person_log;
create table update_person_log(id int,old_name varchar(255),new_name varchar(255),old_sal int,new_sal int,update_time datetime,update_info varchar(255),foreign key (id) references person(id) 
);-- 插入几条测试数据
insert into person(name, sal) values ('a', 100), ('b', 200), ('c', 300);-- 添加一个触发器对象
create trigger test_update_per after update on person for each row 
begininsert into update_person_log values (old.id, old.name, new.name, old.sal, new.sal, now(), concat('名字从', old.name, '更新为', new.name, '; 薪资从', old.sal, '更新为', new.sal));
end; -- 更新一条数据看看
update person set name = 'aa', sal = 150 where name = 'a';-- 查看一下日志表
select * from update_person_log;

执行结果如下