MySQL（进阶）存储过程和触发器

在日常开发中，我们习惯把业务逻辑写在 Java 等后端代码里，但很多高频、重复、数据密集型的操作如果全部放在应用层执行，会产生大量网络 IO、重复 SQL、性能损耗，也不利于事务一致性与数据安全。

存储过程与触发器是 MySQL 非常重要的两大高级特性，它们把逻辑直接下沉到数据库层执行：

• 存储过程：封装多条 SQL，实现复杂业务逻辑，一次编译多次执行
• 触发器：无需调用，自动在表发生增删改时触发，完成数据联动、日志记录、约束校验

很多同学学到这里会觉得语法多、变量杂、逻辑绕，但其实它们是后端开发、校招面试、项目优化里高频考点 + 实用技能。掌握它们，你能写出更高效、更健壮的数据层逻辑。

本篇将从零开始，系统讲解存储过程、变量、流程控制、游标、存储函数、触发器，带你从会用到能写，最终能独立完成业务级存储过程与触发器。

---

一、开篇介绍

为什么要学习存储过程与触发器

在 Java 后端开发中，绝大多数业务逻辑都会写在应用服务代码中。但数据库本身也具备编程能力，也就是存储过程与触发器。二者将业务逻辑下沉到数据库内部执行，在特定场景下有着不可替代的价值，同时也是后端面试、数据库优化、数据治理的高频知识点。

1. 优势层面：存储过程的核心价值

1. 减少网络传输开销，提升执行性能 多条连续 SQL 如果在 Java 代码中循环执行，会频繁和数据库建立网络通信，产生大量 IO 损耗。存储过程会预先编译并保存在数据库服务端，客户端只需要发送一次调用指令，数据库内部批量执行多条 SQL，大幅降低网络交互次数，适合大批量数据处理。

2. SQL 逻辑复用，统一数据层口径 多条关联 SQL 可以封装成一个存储过程，多个应用、多个服务可以共用同一套数据逻辑。避免不同开发人员写出逻辑不一致的 SQL，保证数据计算规则统一。

3. 简化权限管控，保障数据安全 可以只给开发人员开放存储过程的调用权限，而不直接开放数据表的增删改权限。开发人员只能执行预先定义好的数据操作，无法直接篡改原始数据表，提升数据安全性。

4. 保证多语句事务的原子性 复杂事务逻辑写在存储过程里，所有 SQL 都在数据库内部串行执行，不会因为应用服务断连导致事务中途断裂，更容易保证多步数据操作的一致性。

2. 触发器不可替代的应用场景

触发器是一种自动执行的数据库逻辑，不需要代码主动调用，只要数据表发生 INSERT、UPDATE、DELETE 操作，就会自动触发预设逻辑。

• 自动记录数据变更日志：在数据被修改、删除时，自动把旧数据、新数据、操作时间写入操作日志表，无需在每一处业务代码中重复编写日志逻辑；
• 实现跨表数据联动更新：修改主表数据时，自动同步更新关联附表，保证主从数据联动一致；
• 做数据前置校验：拦截不符合规则的数据写入，在数据库层面拦截脏数据，弥补代码层校验遗漏的问题。

3. 求职与技术储备层面

1. 校招、后端面试必考内容 MySQL 高级编程、游标、异常处理、触发器是后端开发面试常考的数据库进阶考点，尤其在数据服务、金融、电商业务岗位中频繁出现。
2. 读懂遗留项目代码 很多传统项目、老系统大量使用存储过程完成复杂统计、对账、批量结算业务，掌握存储过程才能维护存量项目。
3. 完善技术栈，拓宽技术方案选择 Java AI 应用、数据统计、批量数据清洗场景中，很多批量查询逻辑放在存储过程中执行效率更高，学会之后可以灵活选择实现方案，不会只会把所有逻辑堆在 SpringBoot 代码里。

4. 客观理性看待：二者并非万能

我们也要清楚它们的短板：

• 存储过程写在数据库中，调试困难，无法使用 IDE 断点调试；
• 业务逻辑和数据库强绑定，数据库迁移、版本迭代会变得非常麻烦，不利于微服务架构的业务拆分；
• 大量使用存储过程会把业务压力转移给数据库，容易造成数据库 CPU 压力过高，不利于集群扩容。

所以在现代微服务开发中，不会把核心业务全部写进存储过程。但在批量统计、数据对账、日志自动记录、数据强一致性管控这类场景，存储过程和触发器依然是最优方案。

---

本章学习目标总览

• 了解存储过程的特点和优缺点
• 掌握创建、调⽤、查看和删除存储过程的语法
• 掌握MySQL中的变量类型
• 掌握系统变量、⽤⼾⾃定义变量和局部变量的使⽤⽅法
• 掌握SQL编程中涉及到的语法规则，包括：
• 条件判断
• 参数分类
• 选择分⽀
• 循环
• 掌握游标的定义与使⽤
• 掌握条件处理程序的定义与使⽤
• 掌握存储函数的定义与使⽤
• 可以根据实际业务编写存储过程
• 掌握触发器的使⽤场景与分类
• 掌握触发器的创建、查看和删除⽅法
• 可以根据实际业务定义触发器

---

二、存储过程基础

1.储存过程是什么

2.特点

3.优缺点

优点

 缺点

4.语法

创建

-- 修改SOL结束的标识符：//

DELIMITER //

-- 创建储存过程

CREATE PROCEDUER 存储过程名 （参数列表）

-- 开始标识

BEGIN

--结束标识
END //

-- 修改SQL语句结束标识符为 ;

DELIMITER ;

代码讲解：为什么使用 DELIMITER 修改SOL结束的标识符？

因为MYSQL中通常是使用 ；（分号）为分割符，存储过程、函数、触发器内部的代码块里会包含很多条 SQL，每条 SQL 末尾都有 ; （分号），如果不使用 DELIMITER 将SOL结束的标识改变 那么SQL会遇到第一个；（分号）会误以为整条 CREATE 语句已经结束，直接执行创建语句，导致语法报错，创建失败。

我们打开mysql终端执行这段命令

-- 创建存储过程 
CREATE PROCEDURE t1()

BEGIN

SELECT name ,chinese FROM exam;

SELECT name,math FROM exam;

END;

就会发现sql终端只执行了第一条命令 ，SELECT name,math FROM exam;并没有执行 导致了语法报错，创建失败。

注意：我们使用客户端（比如 Navicat/DBeaver）会做特殊兼容处理：

• 工具会自动识别 CREATE PROCEDURE ... BEGIN ... END; 是存储过程定义，内部自动临时切换分隔符，屏蔽了语法报错；会使命令执行成功
• 但这是客户端软件的 “补丁”，原生 mysql 命令行、程序代码（Java/Python）执行这段不带 DELIMITER 的代码一定会报 1064 语法错误，不能依赖工具兼容，规范写法必须加 DELIMITER。

调用

-- 调⽤存储过程

CALL 存储过程名 (参数列表);

查看

-- 查看指定数据库中创建的存储过程

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名';

-- 查看存储过程的定义

SHOW PROCEDURE STATUS WHERE NAME= '存储过程名';

删除

DROP PROCEDURE [IF EXISTS] 存储过程名;

练习 ：计算所有exam表中学生的总分

-- 创建存储过程 
DELIMITER //
 CREATE PROCEDURE t1()

  BEGIN

    SELECT name ,chinese + math + english as total FROM exam;

  END//

DELIMITER ;

-- 调用 t1

CALL t1();

三 变量

系统变量

它是 MySQL 数据库自带的内置配置参数，相当于数据库的各类开关、阈值、规则设定，用来控制数据库整体运行逻辑、性能、权限、字符、事务、日志、连接等全部底层行为。 所有数据库的默认运行规则，都是由系统变量决定的。

1. 全局变量 （GLOBAL）

作用范围：整个 MySQL 数据库服务，对所有客户端连接生效。 生效特点：修改后，已经建立好的旧连接不会立刻生效，只有新打开的数据库连接才会读取新配置；仅临时修改的话，数据库重启后全部恢复默认值。 权限要求：只有管理员账号才能修改。

2. 会话变量 （SESSION）

作用范围：仅当前你正在操作的这一个数据库窗口 / 连接，别的客户端不受任何影响。 生效特点：修改后马上生效；一旦关闭当前连接、断开数据库，所有修改直接消失，不会留存。 权限要求：普通登录用户大部分都能自行调整。

3.两种变量的对比

维度	GLOBAL 全局变量	SESSION 会话变量
生效范围	整个 MySQL 服务所有连接	仅当前执行的客户端连接
生效时机	修改后新连接生效，已存在连接不变	修改立刻生效，仅当前窗口
生命周期	内存中直到重启，持久化配置永久	断开连接直接消失
修改语法	SET GLOBAL xxx=val;	SET [SESSION] xxx=val;
权限要求	需要 SUPER 管理员权限	普通用户可修改大部分会话变量

-- 查看所有系统变量

SHOW [GLOBAL|SESSION] VARIABLES;

-- 查看指定的系统变量

SHOW [GLOBAL|SESSION] VARIABLES LIKE 'xxx';

-- 查看指定的系统变量，可以通过LKIE进⾏模糊查询

SHOW [GLOBAL|SESSION] VARIABLES like '%xxx%';

-- 使⽤SELECT查看指定系统变量

SELECT @@[GLOBAL|SESSION].系统变量名;

-- ----------------------------------------------------

-- ⽰例：查看以auto开头的全局系统变量

SHOW GLOBAL VARIABLES LIKE 'auto%';

-- ⽰例：查看以char开头的会话系统变量

SHOW SESSION VARIABLES LIKE 'char%';

-- ⽰例：查看事务⾃动提交全局系统变量

SELECT @@GLOBAL.autocommit;

---

如果没有指定GLOBAL|SESSION，默认设置会话(SESSION)全局变量

会话关闭后，设置的会话(SESSION)变量失效；新建的会话读取全局系统变量的值做为初始值。

MySQL重启后，设置的全局(GLOBAL)变量失效，如果想使全局系统变量永久⽣效，需要修改选项⽂件

⽤户自定义变量

区别于数据库自带的系统变量，用户自定义变量是使用者自己临时创建、赋值使用的变量，仅属于当前数据库连接窗口，数据库本身不会预先定义。 作用是临时存放数值、文本、查询结果，简化重复计算、拼接数据、传递中间结果。

一、核心生效范围与生命周期

1. 仅当前会话有效：只在你当前打开的这个数据库连接窗口能用，其他同时登录的客户端完全看不到这个变量。
2. 自动销毁规则：关闭窗口、断开数据库连接后，变量连同里面存储的数据会直接清空，不会持久保存到数据库文件里，重启库也不会保留。
3. 无全局作用域：不存在所有人共用的用户自定义变量，全部是单连接私有。

二、会话用户变量（@开头）

最常用的自定义变量，无需提前声明定义，直接赋值就能使用。

• 不需要指定数据类型，赋值什么内容，变量就自动适配对应类型（数字、字符串、日期都可以）。
• 可以在普通查询、存储过程、简单逻辑里随意读写。
• 多个 SQL 语句之间可以传递数据，比如上一条查询算出的值，下一条语句直接拿来用。

三、赋值

-- ⽅式⼀

SET @var_name = expr [, @var_name]...;

-- ⽅式⼆ 【推荐】

SET @var_name := expr [, @var_name]...;

-- ⽅式三：在SELECT语句中

SELECT @var_name := expr [, @var_name]...;

-- ⽅式四：查询结果赋值给⾃定义变量

SELECT 列名 INTO @var_name FROM 表名 WHERE ...;

注意：由于SQL中⽐较⽐较相等也是⽤等号 (=)，所以在为变量赋值的时候推荐使⽤ ( := )

四、使用

-- ⽰例：定义⼀个age变量并赋值为18，并查看

SET @age := 18;

SELECT @age;

-- ⽰例：查询学⽣表中的总记录数并赋值给conut变量

SELECT count(*) INTO @count from student;

SELECT @count;

-- ⽰例：访问⼀个未赋值的变量，返回NULL

SELECT @var;

局部变量

只能写在存储过程、自定义函数的代码块内部，属于代码块私有变量。

1. 使用限制：只能放在 BEGIN 之后、业务逻辑最开头的位置统一声明，不能在语句中间临时创建。
2. 必须提前指定数据类型，定义时就要说明存数字还是字符串。
3. 作用范围极小：仅当前这段 BEGIN-END 代码块内生效，代码执行完毕变量立刻失效，出了存储过程完全无法访问。

一、声明

变量可以是任何有效的MySQL数据类型，如 INT 、 VARCHAR 、 DATETIME 等

DECLARE 变量名 变量类型 [DEFAULT 默认值] ...;

二、使用

-- 示例：在存储过程中定义局部变量记录学生表的总记录数
DELIMITER //
CREATE PROCEDURE P1()
 BEGIN
-- 定义局部变量，并指定默认值
    DECLARE student_count INT DEFAULT 0;
-- 把查询结果赋值给局部变量
    SELECT COUNT(*) INTO student_count FROM student;
-- 使⽤局部变量
    SELECT student_count;
 END//
DELIMITER ;

-- 调⽤存储过程

CALL p1();

三 、注意事项

• 变量名不区分⼤⼩写。
• 在存储过程和函数中，局部变量必须在使⽤前声明。
• ⽤户⾃定义变量在会话结束时失效，⽽局部变量在存储过程或函数结束时失效。

• 避免使⽤保留字作为变量名。

四 SQL编程

条件判断 - IF 语句

通过上图中sql和java中if-else的对比我们可以发现：

都是从上到下依次匹配条件，一旦某个条件成立，执行对应代码块后，直接跳出整个判断结构，不再执行后续分支。

其中

SQL 语法	Java 语法	作用
IF 条件 THEN	if(条件)	第一个判断分支，必写
ELSEIF 条件 THEN	else if(条件)	中间多条件分支，可写多个
ELSE	else	兜底分支，所有条件都不满足时执行
END IF;	末尾 }	判断结构结束标记

注意

sql中的逻辑运算符和java中的对比为

AND	&&
OR	||
NOT	！

练习：

• 示例：根据分数的值，判定当前分数对应的等级
• 分数 >= 90 分等级为优秀
• 分数 >= 80 且分数 < 90分等级为良好
• 分数 >= 60 分且分数 < 80分等级为及格
• 分数 < 60分等级为不及格

DELIMITER //
CREATE PROCEDURE P2()
 BEGIN
-- 设置默认分数
   DECLARE score INT DEFAULT 99;
--      设置默认结果
   DECLARE result VARCHAR(20);

-- 注意边界问题 主播这里没有处理边界
    IF  score >= 90 THEN
         SET result := '优秀';
        ELSEIF score >=80 AND score < 90 THEN
         SET result :='良好';
      ELSEIF score >=60 AND score < 80 THEN
         SET result :='及格';
    ELSE
       SET result :='不及格';
    END IF;
   
--     查询结果
   SELECT result;

 END //
DELIMITER ;

CALL p2();

由上sql编程代码所看我们发现
-- 设置默认分数
   DECLARE score INT DEFAULT 99;
这个数据是固定的 如果我们想更换数值 必须将p2的存储过程给删除 再重新编译，这样就显得麻烦了许多 于是我们需要把分数当⼀个参数传进存储过程就需要⽤参数接收 这样就可以方便快捷的定义数据 不用重新编译p2了 这个时候我们就要用到参数了

参数

1.分类 

参数类型	核心描述
IN	输入类型，调用存储过程时传入值，默认参数类型
OUT	输出类型，用于存储过程向外返回结果
INOUT	输入输出两用，既能传入初始值，执行后又可带出修改后结果

2、三种参数深度对比（补充区别、用法）

参数	传入方向	参数初始值	内部修改是否影响外部变量	典型使用场景
IN	外部 → 存储过程	调用时必须传实参	❌ 内部修改仅副本生效，外部原值不变	传入查询条件、新增数据等入参
OUT	存储过程 → 外部	调用时传空变量，过程内部自动初始化	✅ 内部赋值后，外部变量同步更新	返回统计结果、查询单条字段值
INOUT	双向互通	调用时需传入带初始值的变量	✅ 内部修改会同步覆盖外部变量	需要先传值、再带回计算结果

3.语法

-- 修改SQL语句结束标识符为 //

DELIMITER //

-- 创建存储过程

CREATE PROCEDURE 存储过程名 ([IN/OUT/INOUT 参数名  数据类型] [,...])

BEGIN

-- SQL 语句

END //

-- 修改SQL语句结束标识符为 ;

DELIMITER ;

于是我们就可以将上方的代码改成

DELIMITER //
CREATE PROCEDURE P3(IN score INT, OUT result VARCHAR(20))
 BEGIN

--  判断
    IF  score >= 90 THEN
         SET result := '优秀';
        ELSEIF score >=80 AND score < 90 THEN
         SET result :='良好';
      ELSEIF score >=60 AND score < 80 THEN
         SET result :='及格';
    ELSE
       SET result :='不及格';
    END IF;
   
 END //
DELIMITER ;

-- 调⽤存储过程

CALL p3(23, @result);
SELECT @result

CASE

语法⼀

与java中的switch语句进行对比

功能作用	SQL 简单 CASE（存储过程流程）	Java switch
待匹配变量	CASE case_value	switch(变量)
分支匹配条件	WHEN when_value THEN	case 常量值 :
分支执行代码	statement_list（THEN 后语句）	{} 内业务代码
兜底默认分支	ELSE	default :
结构结束标记	END CASE;	末尾 }
穿透控制	无穿透，无需中断关键字	无 break 会向下穿透，必须写break

语法二

SQL 搜索 CASE（语法二）与 IF-ELSE IF 完整对比

SQL 搜索 CASE（存储过程）	SQL IF 多分支语句	功能说明
CASE	IF 条件1 THEN	判断结构起始
WHEN search_condition THEN	ELSEIF 条件2 THEN	多分支条件判断，可写多条
ELSE statement_list	ELSE	兜底分支，所有条件不匹配时执行
END CASE	END IF;	判断结构结束标记

ELSE ⼦句，则会导致CASE语句错误。

练习

示例⼀：传⼊⼀个状态码，输出该状态码表⽰的含义

0：成功

10001：⽤⼾名或密码错误

10002：您没有对应的权限，请联系管理员

20001：你传⼊的参数有误

20002：没有找到相应的结果

DELIMITER //
CREATE PROCEDURE P4(IN str INT, OUT result VARCHAR(20))
  BEGIN
    CASE str
      WHEN 0 THEN
          SET result := '成功';
        WHEN 10001 THEN
          SET result := '用户名或密码错误';
        WHEN 10002 THEN
          SET result := '您没有对应的权限，请联系管理员';
        WHEN 20001 THEN
          SET result := '你传入的参数有误';
        WHEN 20002 THEN
          SET result := '没有找到相应的结果';
      ELSE
          SET result := '服务器错误，请联系管理员';
END CASE;

  END//
DELIMITER ;

CALL P4(10006,@result);
SELECT @result;

示例⼆：根据传⼊的⽉份，输出该⽉份属于哪个季度

1 ~ 3⽉为第⼀季度

4 ~ 6⽉为第⼀季度

7 ~ 9⽉为第⼀季度

10 ~ 12⽉为第⼀季度

DELIMITER //
CREATE PROCEDURE P5(IN months INT, OUT result VARCHAR(20))
  BEGIN
     CASE 
      WHEN months >=1 AND months <=3 THEN
          SET result := '第一季度';
        WHEN months >=4 AND months <=6 THEN
          SET result := '第二季度';
        WHEN months >=7 AND months <=9 THEN
          SET result := '第三季度';
        WHEN months >=10 AND months <=12 THEN
          SET result := '第四季度';
      ELSE
          SET result := '输入的月份有错误';
END CASE;

  END//
DELIMITER ;

CALL P5(8,@result);
SELECT @result;

语法一和语法二的使用场景

业务需求	推荐语法	理由
状态码、编号、性别等值枚举	语法一（简单 CASE）	代码简短，语义直观
分数、金额、年龄区间判断	语法二（搜索 CASE）	支持>=/<区间条件
多字段组合条件（AND/OR）	语法二（搜索 CASE）	语法一无法实现多条件
SELECT 查询中转换字段	语法二（搜索 CASE）	查询仅支持该写法
分支多、逻辑复杂分层	语法二（搜索 CASE）	WHEN 对齐，结构清晰
仅单一变量对比固定常量	语法一（简单 CASE）	书写更少

循环

WHILE 

语法：

关键字语法对应表

功能	SQL 存储过程 WHILE	Java while
循环起始关键字	WHILE	while
条件包裹	条件后紧跟 DO	条件用 () 包裹
循环体范围	DO 和 END WHILE; 之间所有语句	{} 大括号包裹代码块
循环结束标记	END WHILE;（必须闭合加分号）	末尾 }
循环中断	LEAVE 循环标签（break）、ITERATE（continue）	break;、continue;

练习

示例：传⼊⼀个数n，计算从1累加到n的值

DELIMITER //
CREATE PROCEDURE P6(IN total INT)
  BEGIN
      DECLARE count INT DEFAULT 0;
        
        WHILE total >0 DO
        SET count := count + total;
            SET total := total - 1;
    END WHILE;
        
    SELECT count;
  
  END//
DELIMITER ;

CALL P6(100);

REPEAT

关键字语法对应表

功能	SQL REPEAT 循环	Java do-while 循环
循环起始标识	REPEAT	do
循环体代码	REPEAT 与 UNTIL 之间语句	{} 大括号包裹代码块
终止判断条件	UNTIL 条件（条件成立则退出）	while(条件)（条件成立则继续循环）
循环结束标记	END REPEAT;	末尾 };
跳出 / 跳过本轮	LEAVE、ITERATE	break、continue

练习

⽰例：传⼊⼀个数n，计算从1累加到n的值

DELIMITER //
CREATE PROCEDURE P7(IN total INT)
  BEGIN
     DECLARE count INT DEFAULT 0;
     REPEAT
       SET count := count + total;
         SET total := total -1 ;
    UNTIL total <=0 
     END REPEAT;
    SELECT count;
  END//
DELIMITER ;

CALL P7(100);

LOOP

语法

[begin_label:] LOOP

statement_list

END LOOP [end_label]

练习

传⼊⼀个数n，计算从1累加到n的值

-- 创建存储过程

CREATE PROCEDURE p8(IN n INT)

  BEGIN

-- 定义⼀个变量，表⽰结果

   DECLARE total int DEFAULT 0;

-- 累加

    sum_label: LOOP

-- 判断是否退出

      IF n <= 0 THEN

      LEAVE sum_label;

      END IF;

-- 累加操作

    SET total := total + n;

    SET n := n - 1;

  END LOOP sum_label;

-- 查询结果

  SELECT total;

END;

-- 调⽤存储过程

CALL p8(100);

传⼊⼀个数n，累加从1累加到n之间偶数的值

-- 创建存储过程
CREATE PROCEDURE p10(IN n INT)
 BEGIN
-- 定义⼀个变量，表⽰结果
  DECLARE total int DEFAULT 0;
-- 累加
   sum_label: LOOP
-- 判断是否退出
    IF n <= 0 THEN
    LEAVE sum_label;
    END IF;
-- 判断是否偶数
    IF n % 2 = 1 THEN
    SET n := n - 1;
-- 跳出本次循环
    ITERATE sum_label;
    END IF;
-- 累加操作
   SET total := total + n;
   SET n := n - 1;
  END LOOP sum_label;
-- 查询结果
  SELECT total;
END;
-- 调⽤存储过程
CALL p10(100);

5.游标

游标是存储过程 / 函数中，用来逐行遍历 SELECT 查询结果集的数据库对象。

• MySQL中的游标是⼀种数据库对象，允许在存储过程和函数中对查询到的结果集进⾏逐⾏检索。
• 仅适用于存储程序（PROCEDURE / FUNCTION），普通 SQL 语句不能直接使用；
• MySQL 游标特性：只读、不可滚动、仅向前读取（只能从头读到尾，不能回退、跳转），不能进⾏更新操作。

适用场景

1. 需要逐行对每条记录做复杂逻辑判断、计算、更新；
2. 多行数据拆分、分批处理；
3. 嵌套循环处理关联查询结果。

游标优缺点

优点

1. 支持逐行精细化处理数据，复杂分支逻辑只能用游标实现；
2. 处理大批量数据时可分步操作，避免一次性加载全部结果。

缺点（性能短板，重点）

1. 性能极差：游标逐行读取，会大量消耗 IO，百万级数据严重卡顿；
2. 锁表风险：循环过程中行锁持有时间变长，并发场景容易阻塞；
3. 仅存储过程可用，无法在普通 SQL 使用；
4. 只读单向，不能回退、不能更新游标行。

使用规范与优化建议

1. 优先不用游标 能用聚合函数、子查询、JOIN、批量 UPDATE/DELETE 实现的逻辑，绝对不要用游标，集合操作远快于逐行循环。
2. 游标查询尽量缩小结果集 加 WHERE 条件过滤数据，避免全表遍历。
3. 循环内避免嵌套查询、频繁更新 循环中频繁 DML 会大幅拖慢执行速度。
4. 必须手动 CLOSE 游标，释放资源。
5. 大数据场景改用批量分页处理代替游标。

常见报错与坑

1. DECLARE syntax error 原因：变量、HANDLER、游标声明顺序错乱； 解决：先变量 → 再 HANDLER → 最后游标。

2. 最后一行数据丢失 原因：FETCH 后立刻判断 done，REPEAT/LOOP 处理逻辑位置错误。

3. 忘记修改 DELIMITER 存储过程内部有分号，MySQL 会提前截断语句，报语法错误。

4. 变量和 SELECT 字段数量不匹配 FETCH INTO 变量个数必须和游标 SELECT 字段完全一致。

语法

-- 声明游标

DECLARE 游标名 CURSOR FOR 查询语句;

-- 打开游标

OPEN 游标名;

-- 获取游标记录

FETCH 游标名 INTO 变量[, 变量] ...;

-- 关闭游标

CLOSE 游标名;

练习

示例：传⼊班级编号，查询学⽣表中属于该班级的学⽣信息，并将符合条件的学⽣信息写⼊到⼀张 新表中

错误：CALL p11(2)
> 1329 - No data - zero rows fetched, selected, or processed
> 时间: 0.031s
DELIMITER //
CREATE PROCEDURE P11(IN class_id INT)
 BEGIN
--  定义学生姓名变量
   DECLARE student_name VARCHAR(20);
--  定义班级姓名变量
   DECLARE class_name VARCHAR(20);
 -- 定义游标
   DECLARE s_cursor CURSOR FOR select s.`name` student_name, c.`name`
   class_name from student s, class c where s.class_id = c.id and s.class_id =
   class_id;

-- 创建新表
   DROP TABLE IF EXISTS t_student_class;
   CREATE TABLE IF NOT EXISTS t_student_class (
   id BIGINT PRIMARY KEY AUTO_INCREMENT,
   student_name VARCHAR(20),
   class_name VARCHAR(20)
   );
-- 开启游标
   OPEN s_cursor;
-- 遍历结果集
   WHILE TRUE DO
-- 获取游标记录
   FETCH s_cursor INTO student_name, class_name;
-- 写入新表
   INSERT INTO t_student_class VALUES (NULL, student_name, class_name);
   END WHILE;
-- 关闭游标
   CLOSE s_cursor;

 END//

DELIMITER ;

CALL p11(2);

条件处理程序

语法

DECLARE handler_action HANDLER

    FOR condition_value [, condition_value] ...

    statement

handler_action: {

    CONTINUE -- 继续执⾏当前程序

     | EXIT -- 终⽌执⾏当前程序

}

 CONTINUE  | EXIT 核心对比：

类型	作用	适用场景
CONTINUE	捕获异常，执行处理逻辑后继续向下运行	游标 NOT FOUND、忽略小错误继续批量处理
EXIT	捕获异常，执行处理逻辑后跳出当前 BEGIN END	严重错误，后续代码没必要执行

condition_value: {

     mysql_error_code -- MYSQL错误码

     | SQLSTATE [VALUE] sqlstate_value -- 状态码

     | SQLWARNING -- 所有以01开头的SQLSTATE代码

     | NOT FOUND -- 所有以02开头的SQLSTATE代码

     | SQLEXCEPTION -- 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码

}

因此我们可以将上面那个问题（由于while循环的退出条件是true，此时是⼀个死循环）的问题给解决掉 加⼊条件处理程序，解决游标越界问题

DELIMITER //
CREATE PROCEDURE P12(IN class_id INT)
 BEGIN
--  定义学生姓名变量
   DECLARE student_name VARCHAR(20);
--  定义班级姓名变量
   DECLARE class_name VARCHAR(20);
-- 定义条件处理程序的结束标识
   DECLARE is_done bool DEFAULT FALSE;
 -- 定义游标
   DECLARE s_cursor CURSOR FOR select s.`name` student_name, c.`name`
   class_name from student s, class c where s.class_id = c.id and s.class_id =
   class_id;
-- 定义条件处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done := TRUE;
        
-- 创建新表
   DROP TABLE IF EXISTS t_student_class;
   CREATE TABLE IF NOT EXISTS t_student_class (
   id BIGINT PRIMARY KEY AUTO_INCREMENT,
   student_name VARCHAR(20),
   class_name VARCHAR(20)
   );
-- 开启游标
   OPEN s_cursor;
     
-- 遍历结果集
   read_loop: LOOP
-- 获取游标记录
   FETCH s_cursor INTO student_name, class_name;
-- 退出循环
   IF is_done THEN
   LEAVE read_loop;
   END IF;
     
-- 写入新表
   INSERT INTO t_student_class VALUES (NULL, student_name, class_name);
     END LOOP read_loop;

-- 关闭游标
   CLOSE s_cursor;

 END//

DELIMITER ;

CALL p12(1);

作用域规则

1. HANDLER 只在同一个 BEGIN...END 内生效；
2. 内层块可以定义自己的 HANDLER，会覆盖外层；
3. 块结束后处理器自动失效。

常见踩坑点

1. 声明顺序写错：游标写在 HANDLER 前面，直接语法报错；
2. 游标用 EXIT HANDLER：FETCH 不到数据直接退出整个存储过程，循环逻辑失效；
3. 忘记定义 NOT FOUND 处理器：游标读到最后一行时直接抛出异常，存储过程中断；
4. HANDLER 内修改的变量只在当前块有效；
5. 同一个块不能定义两个完全相同条件的 HANDLER，会冲突。

存储函数

-- 传入个数n，计算从1累加到n的值

DELIMITER //

CREATE FUNCTION fun(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 //
 
 DELIMITER ;

-- 查询结果

select fun(100);

触发器

触发器是什么

操作	NEW（新数据）	OLD（旧数据）
INSERT	✅ 存在（新增的行）	❌ 不存在
UPDATE	✅ 修改后的值	✅ 修改前原值
DELETE	❌ 不存在	✅ 被删除的原行

语法

-- 创建

CREATE TRIGGER [IF NOT EXISTS] trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

BEGIN

trigger_stmt;

END;

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

-- 查看

SHOW TRIGGERS;

-- 删除, 如果没有指定schema_name，默认为当前数据库

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

• BEFORE
  • 可以修改 NEW 中的字段值
  • 可以抛出异常，阻止 INSERT/UPDATE/DELETE 执行
  • DELETE 无 NEW，无法修改数据
• AFTER
  • 数据操作已经执行完毕，不能修改 NEW/OLD
  • 无法回滚拦截操作，仅适合记录日志、同步数据
  • 多用于数据备份、日志记录

练习

-- 创建学⽣⽇志表

create table student_log (

id bigint primary key auto_increment,

1

2

3operation_type varchar(10) not null comment '操作类型：

insert/update/delete',

operation_time datetime not null comment '操作时间',

operation_id bigint not null comment '操作的记录ID',

operation_data varchar(500) comment '操作数据'

);

插⼊数据的触发器

-- 插⼊触发器
create trigger trg_student_insert
 after insert on student for each row
 begin
-- 插⼊新增数据⽇志
  insert into student_log (
   operation_type,
   operation_time,
   operation_id,
   operation_data)
  values (
   'insert',
    now(),
    new.id,
    concat(new.id, ',', new.name, ',', new.sno,',', new.age, ',',
    new.gender, ',', new.enroll_date, ',', new.class_id)
   );
 end;

-- 向学⽣表中插⼊记录
insert into student values (null, '曹操', '300001', 28, 1, '2024-09-01', 3);
-- 查看学⽣⽇志表
select id, operation_type as type, operation_time as time, operation_id as
op_id, operation_data as data from student_log;

更新数据的触发器

-- 更新触发器

create trigger trg_student_update

after update on student for each row

begin

-- 插⼊新增数据⽇志

insert into student_log (

operation_type,

operation_time,

operation_id,

operation_data)

values (

'update',

now(),

new.id,

concat(old.id, ',', old.name, ',', old.sno,',', old.age, ',',

old.gender, ',', old.enroll_date, ',', old.class_id,

'|', new.id, ',', new.name, ',', new.sno,',', new.age, ',',

new.gender, ',', new.enroll_date, ',', new.class_id)

);

end;

-- 更新学⽣表中记录

update student set age = 20, class_id = 2 where name = '曹操';

-- 查看学⽣⽇志表

select * from student_log;

-- 2 | update | 2024-09-19 11:47:21 | 13 | 13,曹操,300001,28,1,2024-09-

01,3|13,曹操,300001,20,1,2024-09-01,2

-- 更新多条记录

update student set class_id = 3 where id >= 7;

删除数据的触发器

-- 删除触发器

create trigger trg_student_delete

after delete on student for each row

begin

-- 插⼊新增数据⽇志

insert into student_log (

operation_type,

operation_time,

operation_id,

operation_data)

values (

'delete',

now(),

old.id,

concat(old.id, ',', old.name, ',', old.sno,',', old.age, ',',

old.gender, ',', old.enroll_date, ',', old.class_id)

);

end;

-- 删除学⽣表中记录

delete from student where name = '曹操';

-- 查看学⽣⽇志表

select * from student_log;

BEFORE INSERT 校验数据（拦截非法数据）

BEFORE 可以修改 NEW 的值，也可以抛出错误阻止操作 需求：分数不能大于 100，超过则禁止插入

DELIMITER //
CREATE TRIGGER tri_check_score
BEFORE INSERT ON student FOR EACH ROW
BEGIN
    IF NEW.score > 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '分数不能超过100';
    END IF;
END //
DELIMITER ;

-- 测试：会直接报错，插入失败
INSERT INTO student(name,score) VALUES ('小红',120);

触发器常用管理语句

删除触发器

DROP TRIGGER IF EXISTS tri_stu_insert;

查看当前库所有触发器

SHOW TRIGGERS;

查看触发器创建语句

SHOW CREATE TRIGGER 触发器名;

触发器优缺点

优点

1. 自动化：无需人工调用，数据变动自动执行；
2. 统一约束：统一校验数据、自动生成日志、主从表同步；
3. 简化业务代码，前端 / 业务层不用重复写相同逻辑。

缺点（重点）

1. 隐藏逻辑：开发人员看不到触发器，排错困难；
2. 性能损耗：增删改每行都会额外执行触发器代码，大数据量变慢；
3. 容易死循环：触发器内操作本表，会反复触发自身（递归触发）；
4. 调试困难：报错提示模糊，无法断点调试；
5. 事务影响：触发器和原始 DML 属于同一个事务，触发器报错会导致原操作全部回滚。

使用注意事项

1. 一张表，同一个时机 + 同一个事件只能创建一个触发器； 例：不能同时有两个 AFTER INSERT 触发器；
2. 触发器内不能使用 CALL 调用存储过程；
3. 触发器内不能使用 SELECT ... INTO 返回结果集给客户端；
4. 避免触发器中对同一张表执行增删改，防止无限递归触发；
5. 触发器不接收参数、没有返回值，完全依赖表操作触发；
6. 临时表不能创建触发器。

触发器、存储过程、存储函数对比总结

对象	调用方式	返回值	能否 DML	自动执行
触发器	无需调用，表变动触发	无	仅可操作其他表	✅ 自动
存储过程	CALL 手动调用	无 / OUT 输出	增删改查、事务、游标	❌
存储函数	SELECT 调用	单一返回值	仅查询，禁止 DML	❌