技术文档

Appearance

MySQL 存储过程详解：从零基础到实战的完整指南 ​

一、存储过程是什么：先讲个"点外卖"的故事 ​

1. 定义：打包好的 SQL 套餐 ​

想象你每天中午都点同一份外卖：打开 App → 搜索商家 → 选菜品 → 选口味 → 备注少辣 → 提交订单。六个步骤，每天重复。

后来 App 推出了"一键再来一单"功能：点一下，上面六个步骤自动全部执行。

存储过程就是数据库里的"一键再来一单"：把一组 SQL 语句预先写好、命名、保存在数据库里，以后只要调用这个名字，整组 SQL 就按顺序自动执行。

sql
-- 没有存储过程：每次都要写一堆 SQL
SELECT * FROM orders WHERE user_id = 1001;
UPDATE user SET order_count = order_count + 1 WHERE id = 1001;
INSERT INTO log ...;

-- 有了存储过程：一句话搞定
CALL place_order(1001);

2. 核心优势：为什么值得用 ​

优势一：性能提升——少跑冤枉路

普通 SQL 的执行路径是：应用服务器 → 网络传输 → MySQL 解析 SQL → 执行 → 返回结果。如果一个业务要执行 10 条 SQL，就要跑 10 趟网络。

存储过程把这 10 条 SQL 打包存在数据库里，应用只发一句 CALL，网络往返从 10 次变成 1 次。另外存储过程第一次执行后会缓存编译结果，后续调用省去重复解析的开销。

优势二：安全性增强——最小权限原则

你可以只给应用账号"执行某个存储过程"的权限，而不给任何表的直接访问权限。应用只能通过存储过程这个"窗口"操作数据，窗口里允许什么操作，全由你定义——想 SQL 注入都找不到门。

优势三：代码复用——一处定义，处处调用

"计算订单总金额"的逻辑如果散落在 Java、Python、PHP 三套系统里，规则一变要改三个地方。写成存储过程后，所有系统都调同一个过程，改一处全生效。

3. 适用场景与冷静提醒 ​

适合用：

  • 多步原子操作：转账（扣 A 的钱 + 加 B 的钱，必须同生共死）
  • 数据迁移、批量清洗、定时统计等数据库内部任务
  • 多系统共用的核心数据规则

不适合用（踩坑提示）：

  • 复杂业务逻辑别往里塞：存储过程调试困难、无法做单元测试、版本管理麻烦（没法用 Git 优雅管理）
  • 高并发互联网业务慎用：把业务压力全堆在数据库上，而数据库恰恰是最难扩容的环节
  • 现代应用的共识是：存储过程留给"数据库内部的数据处理任务"，业务逻辑尽量写在应用层

二、基础语法：从 Hello World 开始 ​

1. 创建存储过程：CREATE PROCEDURE ​

sql
-- 切换语句结束符：因为存储过程内部有分号，不切换的话 MySQL 会提前"断句"
DELIMITER $$

-- 创建存储过程，名字叫 hello_world
CREATE PROCEDURE hello_world()
BEGIN
    -- BEGIN 和 END 之间是过程体，所有 SQL 写在这里
    SELECT 'Hello, 存储过程!' AS greeting;
END$$

-- 恢复默认结束符
DELIMITER ;

关于 DELIMITER 的踩坑提示：这是初学者第一道坎。MySQL 默认遇到 ; 就认为语句结束，但存储过程内部全是分号。DELIMITER $$ 是告诉客户端"先别急着执行，看到 $$ 才算完"。注意这是客户端命令，不是 SQL，用代码（如 JDBC）执行创建语句时不需要它。

2. 调用存储过程：CALL ​

sql
CALL hello_world();
-- 输出：Hello, 存储过程!

3. 查看、修改、删除 ​

sql
-- 查看当前库有哪些存储过程
SHOW PROCEDURE STATUS WHERE Db = '你的库名';

-- 查看某个存储过程的完整定义
SHOW CREATE PROCEDURE hello_world;

-- 删除存储过程
DROP PROCEDURE IF EXISTS hello_world;

重要提示：MySQL 没有"修改存储过程"的语法。想改定义，只能 DROP 后重新 CREATE（或者用 CREATE OR REPLACE 思路先删再建）。

4. MySQL vs SQL Server 语法差异速查 ​

操作MySQLSQL Server
创建CREATE PROCEDURE p() BEGIN ... ENDCREATE PROCEDURE p AS BEGIN ... END
参数括号必须写括号，即使无参 p()无参可不写括号
调用CALL p();EXEC p; 或 EXECUTE p;
变量声明DECLARE v INT;DECLARE @v INT;（带 @ 前缀）
变量赋值SET v = 1;SET @v = 1; 或 SELECT @v = 1;

后文示例全部以 MySQL 为准。

三、变量体系：三种变量，三种活法 ​

存储过程里会用到三类变量，它们的"活动范围"完全不同，先用一张表建立整体认知：

变量类型长相作用范围生命周期
系统变量@@xxx整个数据库服务器/当前会话服务器级或会话级
用户定义变量@xxx当前连接（会话）内随处可用连接断开即消失
局部变量xxx仅限存储过程的 BEGIN...END 内过程执行结束即消失

1. 系统变量：数据库的"全局配置" ​

系统变量是 MySQL 自身的配置项，带 @@ 前缀，分两个级别：

  • 全局变量（GLOBAL）：对整个服务器生效，影响所有连接
  • 会话变量（SESSION）：只对当前连接生效，互不影响
sql
-- 查看全局变量：整个服务器的默认事务隔离级别
SHOW GLOBAL VARIABLES LIKE 'transaction_isolation';

-- 查看会话变量：我当前这个连接的隔离级别
SHOW SESSION VARIABLES LIKE 'transaction_isolation';

-- 修改会话级：只影响我自己（比如批量导入时临时关闭唯一性检查）
SET SESSION unique_checks = 0;

-- 修改全局级：影响之后建立的所有新连接（需要管理员权限）
SET GLOBAL max_connections = 500;

踩坑提示：SET GLOBAL 改的变量重启后失效（除非写入配置文件），且不影响已存在的连接——改完发现"没生效"，先看看你是不是还连着旧会话。

2. 用户定义变量：连接内的"临时便签" ​

用户变量带 @ 前缀，不用声明，直接赋值即用，在当前连接的任何地方（包括存储过程内外）都能访问：

sql
-- 赋值：用 := 或 SET =
SET @user_name = '张三';
SELECT @order_total := COUNT(*) FROM orders;

-- 使用：同一个连接里随时拿出来
SELECT @user_name, @order_total;

适用场景：存储过程与外部 SQL 之间传值、同一会话内的多语句协作。

踩坑提示：用户变量绑定的是连接而不是用户。用连接池时，下一个拿到这条连接的代码会看到你留下的 @变量——脏数据就是这么来的。用完建议主动清理：SET @user_name = NULL;

3. 局部变量：存储过程的"私人物品" ​

局部变量不带任何前缀，必须先声明后使用，只在 BEGIN...END 内部有效：

sql
DELIMITER $$

CREATE PROCEDURE demo_local_var()
BEGIN
    -- 声明：必须写在 BEGIN 之后的最前面，可以指定默认值
    DECLARE total INT DEFAULT 0;
    DECLARE avg_amount DECIMAL(10,2) DEFAULT 0.00;

    -- 赋值方式一：SET
    SET total = 100;

    -- 赋值方式二：SELECT ... INTO（把查询结果塞进变量）
    SELECT COUNT(*) INTO total FROM orders;

    SELECT total AS 订单总数;
END$$

DELIMITER ;

两条铁律（高频踩坑点）：

  1. DECLARE 必须出现在 BEGIN 之后、其他任何语句之前，写错位置直接报语法错误；
  2. 局部变量名不要和表字段同名！SELECT name INTO name FROM employee 这种写法会让 MySQL 分不清哪个是变量哪个是字段，行为不可预测。业界惯例是给局部变量加前缀，如 v_name、p_total。

四、流程控制：让 SQL 学会"思考" ​

1. IF 条件判断：多分支决策 ​

sql
DELIMITER $$

CREATE PROCEDURE judge_level(IN p_salary DECIMAL(10,2))
BEGIN
    DECLARE v_level VARCHAR(20);

    IF p_salary >= 30000 THEN
        SET v_level = 'P8 及以上';
    ELSEIF p_salary >= 20000 THEN
        SET v_level = 'P7';
    ELSEIF p_salary >= 10000 THEN
        SET v_level = 'P6';
    ELSE
        SET v_level = 'P5 及以下';
    END IF;   -- 注意：END IF 中间有空格，且以分号结尾

    SELECT v_level AS 职级;
END$$

DELIMITER ;

CALL judge_level(25000);   -- 输出：P7

分支逻辑的注意事项：IF 分支是从上往下顺序匹配的，命中即停。上面如果把 p_salary >= 10000 写在最前面，那 25000 会先命中 P6 就返回了，永远到不了 P7。范围条件务必从大到小（或从小到大）排列，避免大条件"罩住"小条件。

2. CASE 分支语句：两种写法 ​

CASE 有两种形态，长得像但适用场景不同：

写法一：简单 CASE（只能做"等值匹配"）

sql
-- 适合：判断某个值等于什么（类似编程语言的 switch）
CASE status
    WHEN '待付款' THEN SET v_msg = '请尽快支付';
    WHEN '待发货' THEN SET v_msg = '仓库处理中';
    WHEN '已完成' THEN SET v_msg = '感谢购买';
    ELSE SET v_msg = '未知状态';
END CASE;

写法二：搜索式 CASE（每个分支可以写任意条件）

sql
DELIMITER $$

CREATE PROCEDURE judge_order(IN p_amount DECIMAL(10,2), IN p_user_vip INT)
BEGIN
    DECLARE v_discount DECIMAL(3,2);

    -- 搜索式：每个 WHEN 后面是独立的判断条件
    CASE
        WHEN p_amount >= 1000 AND p_user_vip = 1 THEN
            SET v_discount = 0.70;   -- 大额且VIP：7折
        WHEN p_amount >= 1000 THEN
            SET v_discount = 0.85;
        WHEN p_user_vip = 1 THEN
            SET v_discount = 0.95;
        ELSE
            SET v_discount = 1.00;   -- 原价
    END CASE;

    SELECT v_discount AS 折扣;
END$$

DELIMITER ;

选型建议：

  • 判断条件是"某字段等于几个固定值" → 简单 CASE，更简洁；
  • 条件涉及范围、多字段组合 → 搜索式 CASE 或 IF；
  • CASE 更适合"根据一个值查表式地映射结果"，IF 更适合"流程式分支逻辑"。超过 4~5 个分支时 CASE 可读性更好。

3. 三种循环：WHILE、REPEAT、LOOP ​

WHILE：先检查，再执行（可能一次都不执行）

sql
DELIMITER $$

CREATE PROCEDURE batch_insert_while(IN p_count INT)
BEGIN
    DECLARE v_i INT DEFAULT 1;

    -- 循环前先把1+2+...+n的演示逻辑写这里：条件为真才进循环体
    WHILE v_i <= p_count DO
        INSERT INTO test_batch (num) VALUES (v_i);
        SET v_i = v_i + 1;   -- 千万别忘了自增，否则死循环！
    END WHILE;
END$$

DELIMITER ;

CALL batch_insert_while(5);   -- 插入 1~5 五行数据

REPEAT：先执行，再检查（至少执行一次）

sql
DELIMITER $$

CREATE PROCEDURE batch_insert_repeat(IN p_count INT)
BEGIN
    DECLARE v_i INT DEFAULT 1;

    REPEAT
        INSERT INTO test_batch (num) VALUES (v_i);
        SET v_i = v_i + 1;
    UNTIL v_i > p_count   -- 注意：UNTIL 后面是"退出条件"，为真就停
    END REPEAT;
END$$

DELIMITER ;

踩坑提示：REPEAT 的判断逻辑和直觉相反——UNTIL 条件 写的是"什么时候停"，不是"什么时候继续"。写反了就是死循环。

LOOP：无条件死循环，靠 LEAVE 手动跳出

sql
DELIMITER $$

CREATE PROCEDURE batch_insert_loop(IN p_count INT)
BEGIN
    DECLARE v_i INT DEFAULT 0;

    -- 给循环起个标签名，LEAVE/ITERATE 要用它指定跳哪个循环
    my_loop: LOOP
        SET v_i = v_i + 1;

        -- ITERATE：跳过本次循环剩余部分，直接进入下一轮（类似 continue）
        IF v_i % 2 = 0 THEN
            ITERATE my_loop;   -- 偶数跳过，只插入奇数
        END IF;

        INSERT INTO test_batch (num) VALUES (v_i);

        -- LEAVE：跳出整个循环（类似 break）
        IF v_i >= p_count THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;
END$$

DELIMITER ;

CALL batch_insert_loop(10);   -- 插入 1,3,5,7,9

三种循环选型对照：

循环执行时机至少执行一次？适用场景
WHILE先判断后执行否最常用，处理可能为空的集合
REPEAT先执行后判断是无论条件如何都要先跑一次的逻辑
LOOP无内置条件是需要复杂跳出/跳过逻辑的场景

五、参数：IN、OUT、INOUT 三兄弟 ​

存储过程与外界的"数据通道"就是参数。用"快递柜"来理解：

  • IN：只进不出——你往柜子里放东西，里面的流程随便用，但改了你也拿不回去（外部变量不受过程内修改影响）
  • OUT：只出不进——柜子是给你留结果的，过程结束时把结果放进去让你取（传入的值在过程内一开始是 NULL）
  • INOUT：能进能出——你放一个值进去，过程可以改它，改完你还能拿到新值

1. IN 参数：最常见的输入 ​

sql
DELIMITER $$

CREATE PROCEDURE get_user_orders(IN p_user_id BIGINT)
BEGIN
    -- p_user_id 在过程内直接使用
    SELECT * FROM orders WHERE user_id = p_user_id;
END$$

DELIMITER ;

CALL get_user_orders(1001);

2. OUT 参数：把结果带出来 ​

sql
DELIMITER $$

CREATE PROCEDURE count_user_orders(
    IN p_user_id BIGINT,
    OUT p_total INT          -- 声明为输出参数
)
BEGIN
    -- 把统计结果写进 OUT 参数
    SELECT COUNT(*) INTO p_total
    FROM orders WHERE user_id = p_user_id;
END$$

DELIMITER ;

-- 调用：OUT 参数位置必须传一个用户变量来"接"结果
CALL count_user_orders(1001, @total);

-- 取出结果
SELECT @total AS 订单总数;

注意：调用时 OUT 参数不能写字面量（不能写 CALL count_user_orders(1001, 0)），必须传 @变量，因为 MySQL 要有个"容器"把值放回去。

3. INOUT 参数：双向传递 ​

sql
DELIMITER $$

CREATE PROCEDURE accumulate(INOUT p_value INT)
BEGIN
    -- 读入旧值，累加后写回同一个参数
    SET p_value = p_value + 100;
END$$

DELIMITER ;

-- 外部准备一个初始值
SET @score = 50;

CALL accumulate(@score);
SELECT @score;   -- 输出：150（过程的修改被带了出来）

CALL accumulate(@score);
SELECT @score;   -- 输出：250（可以反复累加）

4. 三类参数速查 ​

参数类型传入值可用？过程内修改能带出？典型场景
IN✅❌（改了外面不知道）查询条件、业务入参
OUT❌（进来自动变 NULL）✅返回统计结果、生成的单号
INOUT✅✅累加器、需要原地修改的计数器

踩坑提示：如果想返回多行结果集，不要用一堆 OUT 参数——直接在过程体里写 SELECT 语句即可，结果集会直接返回给调用方（就像本文 get_user_orders 那样）。

六、游标（CURSOR）：一行一行地处理数据 ​

1. 为什么需要游标 ​

普通 SQL 是"集合思维"：一条 UPDATE 处理一万行。但有些业务必须"逐行思维"：比如给每个员工按其个人规则计算年终奖、给每个待续约客户发送定制提醒——每一行的处理逻辑都不一样。

游标就是查询结果集上的"书签/指针"：指向结果集中的某一行，你可以读出当前行、处理它、再把指针挪到下一行。

2. 生命周期四步走 ​

DECLARE（声明游标）→ OPEN（打开，执行查询）→ FETCH（逐行读取）→ CLOSE（关闭释放）

3. 实战示例：批量给员工打绩效标签 ​

sql
DELIMITER $$

CREATE PROCEDURE tag_employee_level()
BEGIN
    -- ===== 第一步：声明局部变量（必须先声明变量，再声明游标！）=====
    DECLARE v_id BIGINT;
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_done INT DEFAULT 0;   -- 循环结束标记

    -- ===== 第二步：声明游标（绑定查询语句）=====
    DECLARE cur_emp CURSOR FOR
        SELECT id, salary FROM employee WHERE status = '在职';

    -- ===== 第三步：声明"没数据了"的处理程序（游标的标准搭档）=====
    -- 含义：当 FETCH 读不到下一行时，把 v_done 置为 1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- ===== 第四步：打开游标（此时才真正执行查询）=====
    OPEN cur_emp;

    -- ===== 第五步：循环读取 =====
    emp_loop: LOOP
        -- FETCH：把当前行的两个字段分别读进两个变量，指针自动后移
        FETCH cur_emp INTO v_id, v_salary;

        -- 没有下一行了，跳出循环
        IF v_done = 1 THEN
            LEAVE emp_loop;
        END IF;

        -- 对当前行执行业务逻辑
        IF v_salary >= 20000 THEN
            UPDATE employee SET status = '在职-高职级' WHERE id = v_id;
        END IF;
    END LOOP emp_loop;

    -- ===== 第六步：关闭游标，释放资源 =====
    CLOSE cur_emp;
END$$

DELIMITER ;

CALL tag_employee_level();

4. 游标的铁律与性能提示 ​

声明顺序铁律（写错必报语法错误）：

局部变量 → 游标 → HANDLER，三者必须按此顺序声明

性能提示：

  • 游标是逐行处理，10 万行就是 10 万次循环体执行，能用一条 UPDATE/INSERT...SELECT 集合操作解决的，绝不用游标。游标是"不得以而为之"的手段。
  • 游标内不要做大事务：处理 10 万行就提交一次 vs 每 1000 行提交一次，锁持有时间天差地别。
  • 用完必须 CLOSE（虽然过程结束会自动关，但显式关闭是好习惯）。

七、条件处理程序（HANDLER）：给存储过程装上"安全气囊" ​

1. 为什么需要异常处理 ​

没有 HANDLER 的存储过程，遇到错误（主键冲突、数据不存在）会直接崩溃退出，已执行的 SQL 已经落库，没执行的半途而废——数据处于"半完成"的脏状态。

HANDLER 的作用：预先声明"遇到某类异常时该怎么办"，让过程优雅地处理错误而不是裸奔崩溃。

2. 语法结构 ​

sql
DECLARE 处理方式 HANDLER FOR 异常类型 处理动作;

处理方式二选一：

  • CONTINUE：处理完异常后继续执行后面的语句（小事化了）
  • EXIT：处理完异常后退出当前代码块（止损离场）

支持的异常类型：

异常类型触发时机典型场景
NOT FOUND查询没有命中数据 / 游标读到末尾游标结束标记（前面用过）
SQLWARNING产生了警告（如数据截断）宽松模式下记录警告
SQLEXCEPTION其他所有错误（主键冲突、死锁等）兜底捕获，事务回滚
具体错误码如 1062（主键冲突）精确捕获某类错误

3. 实战示例一：转账 + 事务回滚 ​

这是 HANDLER 最经典的应用：任何一步出错，全部回滚，钱不会凭空消失。

sql
DELIMITER $$

CREATE PROCEDURE transfer(
    IN p_from_id BIGINT,          -- 付款方
    IN p_to_id BIGINT,            -- 收款方
    IN p_amount DECIMAL(10,2)     -- 金额
)
BEGIN
    -- 出错标记
    DECLARE v_error INT DEFAULT 0;

    -- 声明异常处理：遇到任何 SQL 错误 → 回滚事务 → 打标记 → 退出
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;                 -- 撤销本次事务的所有修改
        SET v_error = 1;
    END;

    START TRANSACTION;

    -- 第一步：扣付款方的钱
    UPDATE account SET balance = balance - p_amount
    WHERE id = p_from_id AND balance >= p_amount;

    -- 检查是否真的扣到了（余额不足时影响行数为 0，手动抛错）
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '余额不足或账户不存在';
    END IF;

    -- 第二步：给收款方加钱
    UPDATE account SET balance = balance + p_amount
    WHERE id = p_to_id;

    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '收款账户不存在';
    END IF;

    -- 两步都成功才提交
    COMMIT;

    -- 输出结果
    IF v_error = 0 THEN
        SELECT '转账成功' AS result;
    ELSE
        SELECT '转账失败，已回滚' AS result;
    END IF;
END$$

DELIMITER ;

关键点解释：

  • SIGNAL SQLSTATE '45000'：手动抛出一个异常，主动触发 HANDLER。'45000' 是"用户自定义异常"的专用状态码。
  • 用了 EXIT HANDLER：出错就回滚并退出代码块，绝不会执行到后面的 COMMIT。

4. 实战示例二：异常日志记录 ​

出错了不能光回滚，还得留下案发现场，方便排查：

sql
-- 先建一张错误日志表
CREATE TABLE proc_error_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    proc_name VARCHAR(100),        -- 哪个过程出的错
    error_msg VARCHAR(500),        -- 错误详情
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
sql
DELIMITER $$

CREATE PROCEDURE safe_batch_update()
BEGIN
    DECLARE v_errno INT;
    DECLARE v_msg VARCHAR(500);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 捕获错误码和错误信息
        GET DIAGNOSTICS CONDITION 1
            v_errno = MYSQL_ERRNO,
            v_msg = MESSAGE_TEXT;

        ROLLBACK;

        -- 写入日志表（注意：日志写入要在 ROLLBACK 之后或用独立逻辑，
        -- 否则日志本身也可能被回滚掉）
        INSERT INTO proc_error_log (proc_name, error_msg)
        VALUES ('safe_batch_update', CONCAT('错误码:', v_errno, ' 详情:', v_msg));
    END;

    START TRANSACTION;
    -- ...你的批量更新逻辑...
    COMMIT;
END$$

DELIMITER ;

踩坑提示：GET DIAGNOSTICS 必须写在 HANDLER 内部的最前面，它能拿到刚发生的错误码和错误信息；另外注意日志插入与业务回滚的关系——如果日志表和业务在同一个事务里，回滚会把日志也一起抹掉，上面的写法（先 ROLLBACK 再插日志）是标准姿势。

八、全文总结：一张知识地图 ​

到这里，存储过程的完整知识体系已经搭建完毕：

  1. 概念：存储过程 = 存在数据库里的 SQL 套餐，换来性能、安全、复用，但别拿它装业务逻辑；
  2. 语法：DELIMITER 切换、CREATE PROCEDURE 建、CALL 调、DROP 删，没有"改"只有"删了重建"；
  3. 变量：@@ 系统变量管服务器配置，@ 用户变量管会话便签，无前缀局部变量管过程内部；
  4. 流程控制：IF 顺序匹配防大罩小，CASE 分等值与搜索两式，循环 WHILE 最常用、REPEAT 先跑后判、LOOP 配 LEAVE/ITERATE 玩花样；
  5. 参数：IN 只进、OUT 只出、INOUT 双向，OUT 调用时记得传 @变量 接结果；
  6. 游标：声明→打开→读取→关闭四步走，声明顺序是"变量→游标→HANDLER"，能用集合操作就别用游标；
  7. HANDLER：CONTINUE 继续、EXIT 退出，三类异常 NOT FOUND/SQLWARNING/SQLEXCEPTION，配合 SIGNAL 手动抛错 + 事务回滚 + 错误日志，构成生产级存储过程的标准骨架。

最后的忠告：存储过程是把双刃剑——它能让数据库操作又快又稳，也能让系统变得难以调试和维护。把它用在刀刃上（数据密集型的数据库内部任务），把业务逻辑留在应用层，你和后来接手的同事都会感谢这个决定。

网站备案号:津ICP备15001525号-2