MySQL存储过程

MySQL 存储过程详解：预编译 SQL 的高效应用

存储过程（Stored Procedure）是一组预先编译好的 SQL 语句集合，存储在数据库中，可通过名称直接调用。它能简化复杂操作、提高代码重用性，并减少数据库交互次数，是提升数据库性能的重要工具。

存储过程的核心优势

代码重用：一次定义，多次调用，避免重复编写相同 SQL。
减少网络交互：将多步 SQL 合并为一个存储过程，减少与数据库的连接次数。
提高安全性：可通过权限控制存储过程的调用，限制直接访问表的权限。
预编译优化：创建时编译一次，后续调用无需重新编译，提升执行效率。

存储过程的基本语法

创建存储过程

-- 语法
DELIMITER 自定义结束符  -- 因存储过程体含分号，需临时修改SQL结束符（如$）
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
  -- 存储过程体（SQL语句集合）
END 自定义结束符
DELIMITER ;  -- 恢复默认结束符（;）

参数列表格式：

参数由 “参数模式 + 参数名 + 数据类型” 组成，支持三种模式：

IN：输入参数（调用时需传入值）。
OUT：输出参数（作为返回值，需用用户变量接收）。
INOUT：既作为输入，也作为输出。

调用存储过程

CALL 存储过程名(参数列表);  -- 参数列表需与定义时的顺序和模式匹配

删除存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名;  -- 一次只能删除一个存储过程

查看存储过程信息

-- 查看存储过程的创建语句
SHOW CREATE PROCEDURE 存储过程名;

-- 查看当前数据库中所有存储过程
SELECT name FROM mysql.proc WHERE db = '数据库名' AND type = 'PROCEDURE';

存储过程示例详解

示例 1：无参数的存储过程

创建一个查询所有学生信息的存储过程：

DELIMITER $
CREATE PROCEDURE getAllStudents()
BEGIN
  SELECT * FROM student;  -- 存储过程体（单条SQL可省略BEGIN...END）
END $
DELIMITER ;

-- 调用
CALL getAllStudents();

示例 2：带 IN 参数的存储过程

根据学生 ID 查询其姓名和所属班级：

DELIMITER $
CREATE PROCEDURE getStudentInfo(IN stu_id INT)  -- 输入参数：学生ID
BEGIN
  SELECT s.name AS 学生姓名, c.name AS 班级名称
  FROM student s
  JOIN class c ON s.class_id = c.id
  WHERE s.id = stu_id;
END $
DELIMITER ;

-- 调用（查询ID=1的学生）
CALL getStudentInfo(1);

示例 3：带 OUT 参数的存储过程

查询学生 ID 对应的姓名，并通过 OUT 参数返回：

DELIMITER $
CREATE PROCEDURE getStudentName(
  IN stu_id INT,       -- 输入参数：学生ID
  OUT stu_name VARCHAR(50)  -- 输出参数：学生姓名
)
BEGIN
  SELECT name INTO stu_name  -- 将查询结果赋值给OUT参数
  FROM student
  WHERE id = stu_id;
END $
DELIMITER ;

-- 调用（用用户变量接收返回值）
SET @name = '';  -- 定义用户变量
CALL getStudentName(1, @name);  -- 传入参数并接收返回值
SELECT @name;  -- 查看结果

示例 4：带 INOUT 参数的存储过程

传入一个数字，返回其平方值（既作为输入也作为输出）：

DELIMITER $
CREATE PROCEDURE squareNum(INOUT num INT)
BEGIN
  SET num = num * num;  -- 修改输入参数的值（同时作为输出）
END $
DELIMITER ;

-- 调用
SET @n = 5;  -- 定义用户变量并赋值
CALL squareNum(@n);  -- 传入并接收结果
SELECT @n;  -- 输出：25

示例 5：复杂逻辑（含条件判断和循环）

创建一个存储过程，批量插入指定数量的测试数据：

DELIMITER $
CREATE PROCEDURE batchInsertTestData(IN count INT)
BEGIN
  DECLARE i INT DEFAULT 1;  -- 局部变量：循环计数器
  WHILE i <= count DO  -- 循环插入
    INSERT INTO test_data (content) VALUES (CONCAT('测试数据', i));
    SET i = i + 1;
  END WHILE;
END $
DELIMITER ;

-- 调用（插入10条测试数据）
CALL batchInsertTestData(10);

存储过程与函数的区别

特性	存储过程（PROCEDURE）	函数（FUNCTION）
返回值	可通过多个 OUT/INOUT 参数返回多个值	只能返回一个值（通过 RETURN 语句）
调用方式	使用 CALL 语句独立调用	可作为表达式的一部分（如在 SELECT 中）
适用场景	复杂业务逻辑（多步 SQL、事务等）	简单计算或数据转换（如自定义聚合）
权限要求	需 EXECUTE 权限	需 EXECUTE 权限
事务支持	可包含事务控制（COMMIT/ROLLBACK）	不能包含事务控制

使用注意事项

结束符处理：创建存储过程时必须用 DELIMITER 修改结束符（避免与存储过程体内的分号冲突），结束后需恢复为 ;。
变量作用域：
- 局部变量（DECLARE 声明）仅在存储过程内有效。
- 输出参数需用用户变量（@变量名）接收。
性能优化：
- 避免在存储过程中使用过于复杂的逻辑（如多层嵌套循环），以免影响性能。
- 大事务需拆分，避免长时间锁定表。
调试困难：MySQL 自带工具不支持单步调试，需通过 SELECT 输出中间结果或日志表辅助调试。
版本兼容性：不同 MySQL 版本的存储过程语法可能有差异，迁移时需注意兼容性。

适用场景

复杂业务逻辑：如订单创建（涉及库存扣减、日志记录、通知触发等多步操作）。
批量操作：如批量数据导入、定期数据清理（结合事件调度器使用）。
权限控制：限制用户直接操作表，仅允许通过存储过程访问数据