MySQL——存储

一、什么是存储过程

存储过程（Stored Procedure） 是预编译并存储在数据库中的一段SQL代码集合，支持参数传递、流程控制和返回值。通过类似“方法调用”的方式执行，存储过程将复杂业务逻辑封装在数据库层，简化应用开发，提升数据操作的安全性和性能。

二、存储过程的优缺点：权衡与适用场景

优点

1. 简化复杂操作
   封装多步SQL逻辑（如事务处理），调用时仅需一行代码。

2. 提升性能

   • 预编译后存入缓存，重复调用时直接使用，减少解析时间。

   • 减少网络传输，避免长SQL频繁传输（尤其在微服务架构中）。

3. 增强安全性

   • 通过权限控制访问存储过程，无需直接暴露基表。

   • 防止SQL注入，参数化查询更安全。

4. 维护便捷
   业务逻辑变更时，仅需修改存储过程，无需调整应用代码。

缺点

1. 资源占用
   大量逻辑运算可能占用CPU和内存，影响数据库整体性能。

2. 调试困难
   MySQL原生工具对存储过程调试支持有限。

3. 数据库依赖
   存储过程语法与数据库绑定，迁移成本高（如从MySQL迁移至Oracle）。

三、创建与调用存储过程：从入门到实战

1. 基础语法

DELIMITER $$  -- 修改分隔符
CREATE PROCEDURE 存储过程名([IN|OUT|INOUT 参数名 数据类型])
BEGIN
    -- SQL逻辑
END $$
DELIMITER ;   -- 恢复分隔符

2. 参数类型说明

3. 示例：删除指定球员的所有比赛记录

DELIMITER $$
CREATE PROCEDURE delete_matches(IN p_playerno INT)
BEGIN
    DELETE FROM matches WHERE playerno = p_playerno;
END $$
DELIMITER ;

-- 调用示例
CALL delete_matches(57);  -- 删除球员57的所有比赛

4. 带输入输出参数的复杂示例

DELIMITER $$
CREATE PROCEDURE calculate_stats(
    IN p_dept_id INT,
    OUT avg_salary DECIMAL(10,2),
    INOUT total_count INT
)
BEGIN
    -- 计算平均工资
    SELECT AVG(salary) INTO avg_salary FROM employees WHERE dept_id = p_dept_id;
    -- 更新总人数
    SET total_count = total_count + (SELECT COUNT(*) FROM employees WHERE dept_id = p_dept_id);
END $$
DELIMITER ;

-- 调用示例
SET @total = 0;
CALL calculate_stats(101, @avg_sal, @total);
SELECT @avg_sal AS avg_salary, @total AS total_employees;

四、存储过程实战场景

场景1：批量数据插入

DELIMITER $$
CREATE PROCEDURE batch_insert_orders(IN start_date DATE, IN end_date DATE)
BEGIN
    INSERT INTO orders (user_id, amount, order_date)
    SELECT user_id, FLOOR(RAND()*1000), DATE_ADD(start_date, INTERVAL n DAY)
    FROM users, (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2) days
    WHERE DATE_ADD(start_date, INTERVAL n DAY) <= end_date;
END $$
DELIMITER ;

-- 调用：生成2023-01-01至2023-01-03的模拟订单
CALL batch_insert_orders('2023-01-01', '2023-01-03');

场景2：事务处理与错误回滚

DELIMITER $$
CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2),
    OUT success BOOLEAN
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET success = FALSE;
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
    SET success = TRUE;
END $$
DELIMITER ;

-- 调用：从账户1001向2002转账500元
CALL transfer_funds(1001, 2002, 500.00, @result);
SELECT @result AS transfer_success;

五、存储过程管理：查看、修改与删除

1. 查看存储过程

-- 列出所有存储过程
SHOW PROCEDURE STATUS;

-- 查看定义
SHOW CREATE PROCEDURE calculate_stats;

2. 修改存储过程

-- 使用REPLACE覆盖原有定义
DELIMITER $$
CREATE OR REPLACE PROCEDURE delete_matches(IN p_playerno INT)
BEGIN
    -- 新增日志记录
    INSERT INTO audit_log (action) VALUES (CONCAT('Deleted matches for player ', p_playerno));
    DELETE FROM matches WHERE playerno = p_playerno;
END $$
DELIMITER ;

3. 删除存储过程

DROP PROCEDURE IF EXISTS delete_matches;

六、最佳实践与性能优化

1. 避免过度使用
   复杂业务逻辑尽量在应用层处理，数据库专注数据存取。

2. 参数验证
   在存储过程开头校验输入参数，防止无效数据操作。

3. 索引优化
   存储过程中的查询语句仍需遵循索引优化原则。

4. 定期清理
   废弃的存储过程及时删除，减少资源占用。

七、总结

存储过程是MySQL中强大的功能组件，适用于以下场景：

• 高频复杂查询：如报表生成、数据清洗。

• 事务一致性要求高：如资金转账、订单状态更新。

• 安全敏感操作：通过权限隔离保护核心数据。