游乐游手机版
首页/数据库/文章详情

MySQL存储例程之存储过程与函数基础入门教程

时间：2026-07-12 07:02
MySQL从5 0版本开始引入存储过程和函数，将复杂SQL逻辑打包成可复用单元，提高重用性、减少网络传输并增强安全性。函数有返回值，存储过程无返回值。参数类型包括IN、OUT和INOUT，通过CALL语句调用。

MySQL从5.0版本起便引入了存储过程与函数，这一特性在数据库发展进程中堪称一次重要突破。

MySQL存储例程(过程与函数)

简单来说，存储过程和函数将复杂的SQL逻辑打包为一个可复用的单元。这样一来，应用程序无需关心底层SQL细节，只需像调用普通函数或执行命令那样调用即可。

这里有一个基本区分：函数必须有返回值，而存储过程则没有。当然，这只是最直观的说法，后续将展开更多细节。

1. 存储过程概述

1.1 理解

含义：存储过程这个名称或许听起来有些复杂，但其核心思想很简单——就是将一组预先编译好的SQL语句打包在一起（英文名为Stored Procedure，直译为“存好的过程”）。

执行过程：存储过程预先存储在MySQL服务器上，当需要执行时，客户端只需发出调用命令，服务器便会依次运行事先存好的所有SQL语句。

好处：它为何值得关注？

  • 简化操作：显著提升SQL语句的重用性，减轻开发人员的工作负担。
  • 减少失误：代码只需编写一次，无需反复拼接SQL，出错概率自然降低。
  • 减少网络传输：客户端无需将大量SQL语句通过网络发送给服务器，仅需传递一个调用命令。
  • 提高安全性：业务逻辑封装在存储过程中，SQL语句不会暴露于网络，数据查询安全性更高。

与视图、函数的对比：

  • 相同点：它们都能使开发更清晰、更安全，并减少网络传输量。这些优势均通过封装实现。
  • 不同点：视图本质上是一个虚拟表，通常不直接操作底层数据表；而存储过程是程序化的SQL，可直接操作底层数据表，能够实现较为复杂的处理逻辑。
  • 存储过程与函数：最直观的区别在于——函数必须有返回值，而存储过程没有。

1.2 分类

存储过程的参数类型有三种：IN、OUT和INOUT。据此可将其分为以下几类：

  • 没有参数：无参数无返回
  • 仅带IN类型：有参数无返回
  • 仅带OUT类型：无参数有返回
  • 同时带有IN和OUT：有参数有返回
  • 带INOUT：有参数有返回（参数既作为输入也作为输出）

注意：IN、OUT、INOUT可以同时出现在一个存储过程中，并且可以包含多个参数。

2. 创建存储过程

2.1 语法分析

语法如下：

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
    存储过程体
END

# 调用
CALL 存储过程名();

编写存储过程时，有几个关键点需特别注意：

  1. 参数前的关键字：
    • IN：输入参数。存储过程只读取该参数的值。若未定义参数种类，默认即为IN。
    • OUT：输出参数。存储过程执行完毕后，调用方可以获取参数中返回的值。
    • INOUT：既是输入也是输出，传入时携带值，执行后可能发生变化。
  2. 形参类型可以是MySQL数据库中的任意数据类型。
  3. characteristics是对存储过程的约束条件，主要包括：
    • LANGUAGE SQL：表明存储过程体由SQL语句组成，当前系统仅支持SQL。
    • [NOT] DETERMINISTIC：指明结果是否确定。若确定，相同输入将得到相同输出；若不确定，相同输入可能产生不同输出；默认值是不确定。
    • {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}：说明子程序使用SQL语句的限制，默认值为CONTAINS SQL。
    • SQL SECURITY {DEFINER | INVOKER}：执行权限。DEFINER表示仅创建者可执行，INVOKER表示具有访问权限的用户均可执行，默认值为DEFINER。
    • COMMENT 'string'：注释信息，用于描述存储过程。
  4. 存储过程体中可以包含多条SQL语句，但如果只有一条，可以省略BEGIN和END。
  5. 需要设置新的结束标记：MySQL默认以分号结束语句，但存储过程体中也包含分号，会导致冲突。因此需使用DELIMITER修改结束符，编写完成后再恢复。例如：
    • DELIMITER //将结束符改为//。
    • 存储过程定义完毕后，使用DELIMITER ;恢复默认结束符。
    • 注意：若使用Navicat等工具，工具会自动处理，无需手工设置。

2.2 代码举例

2.2.1 无参数（无参无返回）

存储过程体为普通查询语句，通过CALL调用即可。例如：

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
    SELECT * FROM emps;
END $
DELIMITER ;

# 调用
CALL select_all_data();

2.2.2 带OUT（无参有返回）

SELECT的结果通过INTO赋值给变量。调用时传入@变量名，再使用SELECT查看变量值。例如：

DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
    SELECT MIN(salary) INTO ms FROM emps;
END //
DELIMITER ;

# 调用
CALL show_min_salary(@ms);
SELECT @ms;

2.2.3 带IN（有参无返回）

传入一个参数作为筛选条件。调用时可直接传值，也可传入@变量名。例如：

DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
    SELECT salary FROM emps WHERE ename = empname;
END //
DELIMITER ;

# 调用方式1
CALL show_someone_salary('Abel');

# 调用方式2
SET @empname = 'Abel';
CALL show_someone_salary(@empname);

2.2.4 带IN和OUT（有参有返回）

将前两种方式组合起来——输入员工姓名，输出员工薪资。例如：

DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20), OUT empsalary DOUBLE)
BEGIN
    SELECT salary INTO empsalary FROM emps WHERE ename = empname;
END //
DELIMITER ;

2.2.5 带INOUT（有参数且返回）

参数既作为输入也作为输出，执行后参数的值会被修改。例如：

DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGIN
    SELECT ename INTO empname FROM emps WHERE eid = (SELECT MID FROM emps WHERE ename=empname);
END //
DELIMITER ;

3. 调用存储过程

3.1 调用格式

存储过程必须通过CALL语句调用。若要调用其他数据库中的存储过程，需指定数据库名称，例如CALL dbname.procname。

不同参数模式的调用方式有所区别：

  • in模式参数：CALL sp1('值');
  • out模式参数：SET @name; CALL sp1(@name); SELECT @name;
  • inout模式参数：SET @name=值; CALL sp1(@name); SELECT @name;

3.2 代码举例

以一个实用示例说明：实现累加运算，计算1+2+...+n的值。

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
    DECLARE i INT;
    DECLARE sum INT;
    SET i = 1;
    SET sum = 0;
    WHILE i <= n DO
        SET sum = sum + i;
        SET i = i + 1;
    END WHILE;
    SELECT sum;
END //
DELIMITER ;

CALL add_num(50);

3.3 如何调试

MySQL的存储过程没有像Java或C++那样的专用集成调试环境。如何调试？最简单的办法是使用打印法——通过SELECT语句输出中间结果，验证SQL语句的正确性。调试成功后，将SELECT语句向后移动，继续调试下一段。也可以将存储过程中的SQL语句复制出来，逐段单独测试。

必须承认，调试过程确实相当繁琐。这意味着维护成本较高，因此在生产实践中，通常不推荐大量使用存储过程。

4. 存储函数的使用

MySQL支持自定义函数，定义完成后，调用方式与使用内置函数几乎相同。

4.1 语法分析

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
    函数体  # 函数体中必须包含 RETURN 语句
END

说明：

  • 函数中参数默认为IN，不能指定为OUT或INOUT。
  • RETURNS type必须指定返回类型，函数体里必须包含RETURN value语句。
  • characteristics与存储过程类似。
  • 函数体也可以用BEGIN...END，当只有一条语句时可以省略。

4.2 调用存储函数

与调用系统函数相同：SELECT 函数名(实参列表)

4.3 代码举例

例如，创建一个函数查询某人的email：

DELIMITER //
CREATE FUNCTION email_by_name() RETURNS VARCHAR(25)
DETERMINISTIC CONTAINS SQL
BEGIN
    RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;

SELECT email_by_name();

需要注意以下几点：

  • 声明返回值类型时使用RETURNS（带S），但函数体内的RETURN不带S。
  • RETURN语句中不能出现分号。
  • 函数参数前不需要写IN、OUT等关键字。
  • 若创建时报错“you might want to use the less safe log_bin_trust_function_creators variable”，有两种处理方式：
    1. 添加必要的函数特性，例如DETERMINISTIC和CONTAINS SQL。
    2. 执行SET GLOBAL log_bin_trust_function_creators = 1;。

4.4 对比存储函数和存储过程

关键字 调用语法 返回值 应用场景
存储过程 PROCEDURE CALL 存储过程() 理解为有0个或多个 一般用于更新
存储函数 FUNCTION SELECT 函数() 只能是一个 一般用于查询结果为一个值并返回时

存储过程没有传统意义上的返回值，而是通过OUT参数“修改”变量的值。此外，存储函数可以放在查询语句中使用，而存储过程不可以。反过来，存储过程的功能更强大，能够执行创建表、删除表、事务操作等——这些是存储函数无法做到的。

5. 存储过程和函数的查看、修改、删除

5.1 查看

创建完成后，如何查看已经写好的存储过程？MySQL提供了几种方法：

  • SHOW CREATE：查看创建信息，例如SHOW CREATE FUNCTION test_db.CountProc \G
  • SHOW STATUS：查看状态信息，例如SHOW PROCEDURE STATUS LIKE 'pattern'，可以列出所有存储过程或函数的信息。
  • information_schema.Routines表：直接查询系统表，获取最底层信息。SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='存储过程或函数的名'。若存储过程和函数名称相同，需添加AND ROUTINE_TYPE = 'PROCEDURE'或'FUNCTION'来区分。

5.2 修改

修改存储过程或函数，并非更改功能或逻辑，而是修改相关特性。语法为：

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

可修改的特性包括：

  • CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
  • SQL SECURITY {DEFINER | INVOKER}
  • COMMENT 'string'

例如：

ALTER PROCEDURE CountProc MODIFIES SQL DATA SQL SECURITY INVOKER ;

5.3 删除

删除存储过程或函数，使用DROP语句：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

添加IF EXISTS可以防止因对象不存在而报错。

6. 关于存储过程使用的争议

存储过程究竟是否应该使用？这个话题在业界一直存在争议。有些公司强制要求大型项目必须采用存储过程，而另一些公司则在开发手册中明确禁止。为何差异如此之大？

6.1 优点

  • 一次编译多次使用：存储过程仅在创建时编译，后续调用无需再次编译，执行效率更高。
  • 减少开发工作量：将代码封装为模块，复杂问题可拆解为小模块，模块之间能够复用，代码结构更清晰。
  • 安全性强：可设置使用权限，类似于视图，能够控制特定用户才能执行。
  • 减少网络传输量：每次使用只需调用存储过程，无需传输大量SQL语句。
  • 良好的封装性：原本需要多次连接数据库才能完成的操作，现在一次调用即可完成。

6.2 缺点

尽管存在这些优点，但替代方案也众多。例如阿里巴巴的开发规范中明确写明：

【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

具体来说，存储过程的缺点包括：

  • 可移植性差：MySQL、Oracle、SQL Server的存储过程不能通用，更换数据库就需要重写。
  • 调试困难：仅少数DBMS支持存储过程调试。复杂的存储过程开发和维护都不容易。虽有第三方工具，但通常需要付费。
  • 版本管理困难：数据表索引变更可能导致存储过程失效。软件版本迭代时，存储过程难以管理，因为缺乏版本控制。
  • 不适合高并发场景：高并发情况下，数据库压力本身就是瓶颈。在进行分库分表、对可扩展性要求较高的场景中，存储过程反而会成为负担。

小结：存储过程是一把双刃剑——既便利，也存在局限。不同公司持不同态度，但无论如何，作为开发人员，掌握存储过程依旧是必备技能之一。毕竟，无论最终是否使用，理解它的工作原理以及能解决什么问题，都是基本功。

来源：https://www.jb51.net/database/3670685qb.htm
MySQL函数
上一篇SQL中GROUP BY与临时表组合优化技巧高效实战详解指南 下一篇Navicat 16协作功能共享导入导出任务配置教程
本站内容用于信息整理与展示，如有侵权或内容问题请及时联系处理。

相关推荐

补充同频道和同主题内容，方便继续浏览更多相关内容。

同类最新

继续查看同栏目最近更新的文章。

更多
SQL Server多用户并发INSERT幻读解决方案
数据库 · 2026-07-12

SQL Server多用户并发INSERT幻读解决方案

SQLServer中幻读源于“先查后插”业务的间隙漏洞，SERIALIZABLE隔离级别不能覆盖多语句操作且依赖索引。真正解决需将“读-判-写”绑定为原子动作，如使用MERGE语句、唯一索引加错误捕获或UPDLOCK加HOLDLOCK手动锁范围，并确保查询走索引及显式开启事务。

 Django项目利用安全库规避SQL注入的方法
数据库 · 2026-07-12

Django项目利用安全库规避SQL注入的方法

DjangoORM仅对值参数化，字段名等结构参数需白名单校验。filter()动态字段名、raw()和extra()参数化、RawSQL中LIKE手动转义及ORDERBY等结构字段均为注入风险点，需严格规避用户直接控制。

 SQL分组统计状态变更次数的方法
数据库 · 2026-07-12

SQL分组统计状态变更次数的方法

通过LAG()窗口函数对比相邻行状态变化并计数，是统计分组内状态改变次数的核心方法。需严格按业务逻辑排序、正确分区，并处理NULL边界。旧版MySQL可考虑导出数据用脚本处理。性能优化需建立复合索引，避免函数影响。状态定义需明确连续相同值过滤等陷阱。

 MySQL数据排序的原理与实现步骤
数据库 · 2026-07-12

MySQL数据排序的原理与实现步骤

MySQL排序采用文件排序或索引排序，数据量大时使用合并排序。内存排序由sort_buffer_size控制，超限则转磁盘临时表。索引排序效率高，覆盖索引可避免回表。通过EXPLAIN可分析是否使用索引、临时表或文件排序，据此优化性能。

 如何定位MySQL撤销日志页锁定导致的检查点延迟
数据库 · 2026-07-12

如何定位MySQL撤销日志页锁定导致的检查点延迟

UndoLog页被锁定本身不直接导致CheckPoint延迟，根因是长事务阻塞purge线程导致undo记录无法清理。通过Historylistlength超万且增长、PURGEPROCESSED停滞、事务ID与清理ID差距扩大可判断。定位需查最老活跃事务及其undo段与purge线程状态。