MYSQL-存储过程（Stored Procedure）

运维小路

发布于 2026-01-26 11:53:09

1620

文章被收录于专栏：运维小路运维小路

作者介绍：简历上没有一个精通的运维工程师，下面的思维导图也是预计更新的内容和当前进度(不定时更新)。

数据库是一个系统（应用）最重要的资产之一，所以我们的数据库将从以下几个数据库来进行介绍。

MySQL（本章节）

PostgreSQL

MongoDB

Redis

Etcd

上个小节我们介绍了视图（预设SQL）,今天我们介绍另外一个数据库对象：存储过程（Stored Procedure）。

1. 存储过程概述

存储过程（Stored Procedure） 是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程名称并给出参数来调用执行。

2. 存储过程特点

优点：

性能提升：预编译执行，减少解析时间
减少网络流量：只需传递调用命令而非多条SQL
代码复用：一次编写，多次调用
安全性：可限制用户对底层表的直接访问
事务控制：支持复杂的事务处理

缺点：

调试困难：调试工具相对有限
版本管理：版本控制不如应用程序代码方便
移植性差：不同数据库语法差异大

3. 存储过程基本语法

3.1 创建并查询数据

DELIMITER //

CREATE PROCEDURE sp_get_by_id(IN p_id INT)
BEGIN
    SELECT * FROM test_data WHERE id = p_id;
END //

DELIMITER ;

-- 调用
CALL sp_get_by_id(1);

3.2 使用存储过程插入数据

DELIMITER //

CREATE PROCEDURE sp_insert_data(
    IN p_value VARCHAR(255),
    IN p_random_number INT
)
BEGIN
    INSERT INTO test_data (value, random_number) 
    VALUES (p_value, p_random_number);

    SELECT * FROM test_data WHERE id = LAST_INSERT_ID();
END //

DELIMITER ;

-- 调用
mysql> CALL sp_insert_data('New test data', 456);
+---------+---------------+---------------------+---------------+
| id      | value         | created_at          | random_number |
+---------+---------------+---------------------+---------------+
| 8480634 | New test data | 2025-10-14 23:31:19 |           456 |
+---------+---------------+---------------------+---------------+

3.3 使用存储过程更新数据

DELIMITER //

CREATE PROCEDURE sp_update_data(
    IN p_id INT,
    IN p_value VARCHAR(255),
    IN p_random_number INT
)
BEGIN
    UPDATE test_data 
    SET value = p_value, random_number = p_random_number 
    WHERE id = p_id;

    -- 返回更新后的数据
    SELECT * FROM test_data WHERE id = p_id;
END //

DELIMITER ;

-- 调用
CALL sp_update_data(1, 'Updated value', 999);

3.4 使用存储过程删除数据

DELIMITER //

CREATE PROCEDURE sp_delete_data(IN p_id INT)
BEGIN
    -- 先保存要删除的数据用于返回
    SELECT * FROM test_data WHERE id = p_id;

    -- 执行删除
    DELETE FROM test_data WHERE id = p_id;

    -- 返回删除的记录数
    SELECT ROW_COUNT() AS deleted_rows;
END //

DELIMITER ;

-- 调用
CALL sp_delete_data(5);

4.存储过程管理

4.1 查看所有存储过程

SHOW PROCEDURE STATUS WHERE Db = DATABASE();

4.2 查看存储过程定义

SHOW CREATE PROCEDURE sp_get_by_id;

4.3 删除存储过程

DROP PROCEDURE IF EXISTS sp_get_by_id;

5.视图和存储过程的区别

特性	视图 (View)	存储过程 (Stored Procedure)
调用方式	SELECT * FROM view_name	CALL procedure_name()
参数支持	不支持	支持IN/OUT/INOUT参数
功能范围	只能包含SELECT查询	可包含任何SQL语句+流程控制
返回结果	虚拟表结构	可返回多个结果集
使用场景	数据展示、简化查询	复杂业务逻辑、事务处理