2. 首页 > MySQL教程 > 正文

MySQL教程FG121-MySQL存储过程创建与使用

教程整理：风哥教程  |  更新时间：2026-01-18  |  教程分类：MySQL教程  |  文档学习：19

本文档风哥主要介绍MySQL存储过程创建与使用相关知识，包括存储过程的概念、优点、基本语法、创建方法、调用方式、管理操作等内容，风哥教程参考MySQL官方文档Stored Objects章节，适合DBA和开发人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 MySQL存储过程的概念

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。存储过程是数据库编程的重要组成部分，可以将复杂的业务逻辑封装在数据库层面。

MySQL存储过程的特点：

  • 预编译执行：存储过程在创建时已经编译，执行效率高
  • 封装性：将复杂的业务逻辑封装在数据库层面
  • 可重用性：一次创建，多次调用
  • 安全性：可以通过存储过程控制数据访问权限
  • 减少网络流量：减少客户端与服务器之间的数据传输

1.2 MySQL存储过程的优点

使用存储过程有以下几个主要优点：

性能优势：存储过程在创建时编译，后续调用直接执行，避免了SQL语句的重复解析和编译过程。

安全性：可以通过存储过程限制用户对基础表的直接访问，只暴露必要的操作接口。

维护性：业务逻辑集中在数据库层面，修改时只需要修改存储过程，不需要修改应用程序。

减少网络开销：复杂的操作只需要发送一个调用语句，而不是多条SQL语句。

1.3 MySQL存储过程基本语法

MySQL存储过程的基本语法结构如下：

CREATE PROCEDURE procedure_name([IN|OUT|INOUT] parameter_name data_type[, …])
BEGIN
— SQL statements
— Logic code
END;

语法说明：

  • CREATE PROCEDURE：创建存储过程的关键字
  • procedure_name：存储过程名称，建议使用sp_前缀
  • IN：输入参数，调用者传入值
  • OUT：输出参数，存储过程返回值
  • INOUT：既可以输入也可以输出
  • BEGIN…END：存储过程体，包含SQL语句

Part02-生产环境规划与建议

2.1 存储过程命名规范

在生产环境中，建议遵循以下命名规范：

— 命名规范示例
— 格式：sp_[模块]_[功能]_[动作]
— 例如：
sp_user_insert — 用户模块插入
sp_user_update — 用户模块更新
sp_user_delete — 用户模块删除
sp_user_get_by_id — 用户模块按ID查询
sp_order_calculate — 订单模块计算
sp_report_daily — 报表模块日报

2.2 存储过程安全考虑

存储过程安全需要考虑以下几点：

  • 使用DEFINER和SQL SECURITY控制执行权限
  • 避免在存储过程中拼接SQL字符串，防止SQL注入
  • 对输入参数进行验证和过滤
  • 限制存储过程的访问权限

2.3 存储过程性能优化

存储过程性能优化建议：

  • 合理使用索引，确保存储过程中的查询高效
  • 避免在循环中执行SQL语句
  • 使用事务控制数据一致性
  • 定期分析和优化存储过程

Part03-生产环境项目实施方案

3.1 创建存储过程

下面演示如何创建一个简单的存储过程：

— 创建测试数据库和表
mysql> CREATE DATABASE IF NOT EXISTS fgedu_test;
Query OK, 1 row affected (0.01 sec)

mysql> USE fgedu_test;
Database changed

mysql> CREATE TABLE employees (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> department VARCHAR(50),
-> salary DECIMAL(10,2),
-> hire_date DATE
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO employees (name, department, salary, hire_date) VALUES
-> (‘张三’, ‘技术部’, 15000.00, ‘2023-01-15’),
-> (‘李四’, ‘销售部’, 12000.00, ‘2023-03-20’),
-> (‘王五’, ‘技术部’, 18000.00, ‘2022-06-10’),
-> (‘赵六’, ‘人事部’, 10000.00, ‘2023-07-01’);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

— 创建简单存储过程：查询所有员工
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_get_all_employees()
-> BEGIN
-> SELECT * FROM employees ORDER BY id;
-> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

3.2 调用存储过程

使用CALL语句调用存储过程：

— 调用存储过程
mysql> CALL sp_get_all_employees();
+—-+——+————+———-+————+
| id | name | department | salary | hire_date |
+—-+——+————+———-+————+
| 1 | 张三 | 技术部 | 15000.00 | 2023-01-15 |
| 2 | 李四 | 销售部 | 12000.00 | 2023-03-20 |
| 3 | 王五 | 技术部 | 18000.00 | 2022-06-10 |
| 4 | 赵六 | 人事部 | 10000.00 | 2023-07-01 |
+—-+——+————+———-+————+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

— 创建带参数的存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_get_employee_by_dept(IN dept_name VARCHAR(50))
-> BEGIN
-> SELECT * FROM employees
-> WHERE department = dept_name
-> ORDER BY salary DESC;
-> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

— 调用带参数的存储过程
mysql> CALL sp_get_employee_by_dept(‘技术部’);
+—-+——+————+———-+————+
| id | name | department | salary | hire_date |
+—-+——+————+———-+————+
| 3 | 王五 | 技术部 | 18000.00 | 2022-06-10 |
| 1 | 张三 | 技术部 | 15000.00 | 2023-01-15 |
+—-+——+————+———-+————+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3.3 管理存储过程

存储过程的管理操作包括查看、修改和删除：

— 查看存储过程列表
mysql> SHOW PROCEDURE STATUS WHERE Db = ‘fgedu_test’;
+————+————————-+———–+—————-+———————+———————-+—————+———+———————-+———————-+——————–+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+————+————————-+———–+—————-+———————+———————-+—————+———+———————-+———————-+——————–+
| fgedu_test | sp_get_all_employees | PROCEDURE | root@localhost | 2026-04-01 10:00:00 | 2026-04-01 10:00:00 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| fgedu_test | sp_get_employee_by_dept | PROCEDURE | root@localhost | 2026-04-01 10:01:00 | 2026-04-01 10:01:00 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+————+————————-+———–+—————-+———————+———————-+—————+———+———————-+———————-+——————–+
2 rows in set (0.00 sec)

— 查看存储过程定义
mysql> SHOW CREATE PROCEDURE sp_get_employee_by_dept\G
*************************** 1. row ***************************
Procedure: sp_get_employee_by_dept
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_employee_by_dept`(IN dept_name VARCHAR(50))
BEGIN
SELECT * FROM employees
WHERE department = dept_name
ORDER BY salary DESC;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

— 删除存储过程
mysql> DROP PROCEDURE IF EXISTS sp_get_all_employees;
Query OK, 0 rows affected (0.01 sec)

Part04-生产案例与实战讲解

4.1 业务报表统计存储过程

创建一个用于统计部门薪资的存储过程：

— 创建部门薪资统计存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_department_salary_stats(OUT total_count INT, OUT total_salary DECIMAL(15,2))
-> BEGIN
-> — 统计员工总数
-> SELECT COUNT(*) INTO total_count FROM employees;
->
-> — 统计薪资总额
-> SELECT COALESCE(SUM(salary), 0) INTO total_salary FROM employees;
->
-> — 显示各部门统计
-> SELECT
-> department AS 部门,
-> COUNT(*) AS 人数,
-> CONCAT(FORMAT(AVG(salary), 2)) AS 平均薪资,
-> CONCAT(FORMAT(SUM(salary), 2)) AS 薪资总额
-> FROM employees
-> GROUP BY department
-> ORDER BY SUM(salary) DESC;
-> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

— 调用存储过程
mysql> CALL sp_department_salary_stats(@count, @total);
+——–+——+————–+————–+
| 部门 | 人数 | 平均薪资 | 薪资总额 |
+——–+——+————–+————–+
| 技术部 | 2 | 16,500.00 | 33,000.00 |
| 销售部 | 1 | 12,000.00 | 12,000.00 |
| 人事部 | 1 | 10,000.00 | 10,000.00 |
+——–+——+————–+————–+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

— 查看输出参数
mysql> SELECT @count AS 员工总数, @total AS 薪资总额;
+————–+————-+
| 员工总数 | 薪资总额 |
+————–+————-+
| 4 | 55000.00 |
+————–+————-+
1 row in set (0.00 sec)

4.2 数据批量处理存储过程

创建一个批量插入数据的存储过程：

— 创建批量插入存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_batch_insert_employees(IN num_records INT)
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> DECLARE dept VARCHAR(50);
->
-> WHILE i <= num_records DO -> — 根据序号分配部门
-> SET dept = CASE
-> WHEN i % 3 = 0 THEN ‘技术部’
-> WHEN i % 3 = 1 THEN ‘销售部’
-> ELSE ‘人事部’
-> END;
->
-> INSERT INTO employees (name, department, salary, hire_date)
-> VALUES (
-> CONCAT(‘员工’, LPAD(i, 3, ‘0’)),
-> dept,
-> 10000 + (i * 100),
-> DATE_ADD(‘2023-01-01’, INTERVAL FLOOR(RAND() * 365) DAY)
-> );
->
-> SET i = i + 1;
-> END WHILE;
->
-> SELECT CONCAT(‘成功插入 ‘, num_records, ‘ 条记录’) AS result;
-> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

— 调用存储过程批量插入10条记录
mysql> CALL sp_batch_insert_employees(10);
+————————-+
| result |
+————————-+
| 成功插入 10 条记录 |
+————————-+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

— 查看结果
mysql> SELECT COUNT(*) AS total_records FROM employees;
+—————+
| total_records |
+—————+
| 14 |
+—————+
1 row in set (0.00 sec)

4.3 复杂业务逻辑存储过程

创建一个包含事务处理的复杂存储过程：

— 创建薪资调整存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_adjust_salary(
-> IN emp_id INT,
-> IN adjust_rate DECIMAL(5,2),
-> OUT result_msg VARCHAR(100)
-> )
-> BEGIN
-> DECLARE old_salary DECIMAL(10,2);
-> DECLARE new_salary DECIMAL(10,2);
-> DECLARE emp_name VARCHAR(50);
->
-> — 声明异常处理
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION
-> BEGIN
-> ROLLBACK;
-> SET result_msg = ‘操作失败，已回滚’;
-> END;
->
-> START TRANSACTION;
->
-> — 获取当前薪资
-> SELECT name, salary INTO emp_name, old_salary
-> FROM employees WHERE id = emp_id FOR UPDATE;
->
-> IF old_salary IS NULL THEN
-> SET result_msg = CONCAT(‘员工ID ‘, emp_id, ‘ 不存在’);
-> ROLLBACK;
-> ELSE
-> — 计算新薪资
-> SET new_salary = old_salary * (1 + adjust_rate/100);
->
-> — 更新薪资
-> UPDATE employees SET salary = new_salary WHERE id = emp_id;
->
-> SET result_msg = CONCAT(‘员工 ‘, emp_name, ‘ 薪资从 ‘, old_salary,
-> ‘ 调整为 ‘, new_salary, ‘，调整幅度 ‘, adjust_rate, ‘%’);
->
-> COMMIT;
-> END IF;
-> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

— 调用存储过程调整薪资
mysql> CALL sp_adjust_salary(1, 10.00, @msg);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @msg AS 调整结果;
+————————————————————–+
| 调整结果 |
+————————————————————–+
| 员工 张三 薪资从 15000.00 调整为 16500.00，调整幅度 10.00% |
+————————————————————–+
1 row in set (0.00 sec)

Part05-风哥经验总结与分享

5.1 存储过程最佳实践

根据多年MySQL数据库管理经验，总结以下存储过程最佳实践：

  • 命名规范：使用统一的前缀（如sp_）和清晰的命名规则
  • 参数验证：在存储过程开始处验证输入参数的有效性
  • 错误处理：使用DECLARE HANDLER处理异常情况
  • 事务控制：涉及数据修改的操作使用事务确保一致性
  • 注释文档：为存储过程添加清晰的注释说明

5.2 常见问题与解决方案

常见问题：

  • 问题1：存储过程编译错误 – 检查语法，特别是DELIMITER的使用
  • 问题2：权限不足 – 确保用户有CREATE ROUTINE权限
  • 问题3：参数类型不匹配 – 确保调用时参数类型与定义一致
  • 问题4：性能问题 – 使用EXPLAIN分析存储过程中的查询

5.3 调试与维护工具

MySQL存储过程调试工具和方法：

— 使用SELECT调试变量
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_debug_example(IN param1 INT)
-> BEGIN
-> DECLARE var1 INT;
-> SET var1 = param1 * 2;
->
-> — 调试输出
-> SELECT param1 AS input_param, var1 AS calculated_value;
->
-> — 实际业务逻辑
-> SELECT * FROM employees WHERE id = var1;
-> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

— 查看存储过程依赖
mysql> SELECT * FROM mysql.proc WHERE db = ‘fgedu_test’ AND type = ‘PROCEDURE’\G

风哥提示：存储过程是把双刃剑，合理使用可以提高性能和安全性，但过度使用会增加数据库负担和维护成本。建议将复杂的数据处理逻辑放在存储过程中，而简单的CRUD操作使用应用程序实现。from MySQL:www.itpux.com
GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息