我刚工作的时候，有个复杂的业务：用户下单后，要扣库存、加销量、记日志、发消息。我傻乎乎地在应用层写了 200 行代码，结果 DBA 帮我改成存储过程，性能直接提升了 10 倍。
今天咱们就来聊聊 MySQL 存储过程，看完这篇，你就能根据业务场景决定要不要用存储过程了。
存储过程是啥？
存储过程（Stored Procedure） 是预编译并存储在数据库里的一组 SQL 语句，可以接受参数、执行逻辑、返回结果。
基本语法
DELIMITER $$
-- 创建存储过程
CREATE PROCEDURE procedure_name([IN|OUT|INOUT] parameter_name data_type)
BEGIN
-- 声明变量
DECLARE variable_name data_type DEFAULT default_value;
-- SQL 语句
SELECT COUNT(*) INTO variable_name FROM users;
-- 返回结果
SELECT variable_name;
END$$
DELIMITER ;
-- 调用存储过程
CALL procedure_name([parameter_value]);
关键点：
- DELIMITER：改变语句分隔符（因为存储过程里有
;） -
- IN：传入参数（默认）
-
- OUT：传出参数
-
- INOUT：传入传出参数
-
- DECLARE：声明局部变量
-
- CALL：调用存储过程
存储过程的优点
1. 性能好（预编译 + 减少网络往返）
预编译：存储过程在创建时就预编译了，每次调用不用再编译。
减少网络往返：如果应用层要执行 10 条 SQL，要 10 次网络往返；用存储过程，只要 1 次网络往返。
// 不用存储过程：10 次网络往返
public void createOrder(int userId, int productId, int quantity) {
// 1. 查库存
int stock = productDao.selectStock(productId);
if (stock < quantity) {
throw new Exception("库存不足");
}
// 2. 扣库存
productDao.updateStock(productId, stock - quantity);
// 3. 加销量
productDao.updateSales(productId, sales + quantity);
// 4. 记日志
logDao.insert(userId, productId, quantity);
// 5. 插入订单
orderDao.insert(userId, productId, quantity);
// ... 10 次网络往返
}
```
**用存储过程**：只要 1 次网络往返。
```sql
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE create_order(IN p_user_id INT, IN p_product_id INT, IN p_quantity INT)
BEGIN
DECLARE v_stock INT;
-- 1. 查库存
SELECT stock INTO v_stock FROM products WHERE id = p_product_id;
-- 2. 判断库存
IF v_stock < p_quantity THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';
END IF;
-- 3. 扣库存
UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;
-- 4. 加销量
UPDATE products SET sales = sales + p_quantity WHERE id = p_product_id;
-- 5. 记日志
INSERT INTO order_logs (user_id, product_id, quantity) VALUES (p_user_id, p_product_id, p_quantity);
-- 6. 插入订单
INSERT INTO orders (user_id, product_id, quantity) VALUES (p_user_id, p_product_id, p_quantity);
END$$
DELIMITER ;
-- 调用存储过程（1 次网络往返）
CALL create_order(1, 100, 2);
性能提升：10 次网络往返 → 1 次网络往返（10 倍提升！）
2. 业务逻辑集中（不用应用层操心）
场景：多个应用（Java、Python、PHP）都要实现「下单」逻辑，如果应用层写，要写 3 份（可能写错，导致不一致）。
用存储过程：只要写 1 份（数据库层），所有应用都调用这个存储过程。
// Java 应用
orderDao.createOrder(userId, productId, quantity); // 调用存储过程
// Python 应用
cursor.callproc('create_order', [user_id, product_id, quantity]) // 调用存储过程
// PHP 应用
$mysqli->query("CALL create_order($user_id, $product_id, $quantity)"); // 调用存储过程
好处：业务逻辑改动，只要改存储过程（不用改所有应用）。
3. 数据库管理员（DBA）好优化
场景：存储过程在数据库层，DBA 可以直接优化 SQL（应用层的 SQL 可能分散在各个文件里，不好优化）。
用存储过程：DBA 只要看存储过程的定义，就能优化。
-- DBA 可以直接优化这个存储过程
SHOW CREATE PROCEDURE create_order;
存储过程的缺点（重点！）
1. 可移植性差（绑定数据库）
问题：存储过程是数据库特定的（MySQL 的存储过程语法和 Oracle、SQL Server 不一样），如果换了数据库，要重写。
-- MySQL 存储过程
DELIMITER $$
CREATE PROCEDURE procedure_name()
BEGIN
-- MySQL 语法
DECLARE variable_name INT;
END$$
DELIMITER ;
-- Oracle 存储过程（语法不一样！）
CREATE OR REPLACE PROCEDURE procedure_name IS
variable_name INT;
BEGIN
-- Oracle 语法
NULL;
END procedure_name;
```
**解决方案**：如果可能换数据库，**别用存储过程**（用应用层）。
### 2. 难以调试（没有好用的 IDE）
**问题**：存储过程**难以调试**（不像应用层，可以用 IDE 断点调试）。
```sql
-- 存储过程出错了，只能看 MySQL 错误日志（很难调试）
SHOW PROCEDURE STATUS LIKE 'create_order';
解决方案：存储过程里别写复杂逻辑（比如循环、条件判断），只写简单的 SQL 操作（比如 INSERT、UPDATE、DELETE）。
3. 难以版本控制（Git 不好管理）
问题：存储过程是存在数据库里的，Git 不好管理（应用层代码可以 Git 管理）。
解决方案：用 Flyway 或 Liquibase（数据库版本控制工具），把存储过程的定义存成 .sql 文件，用 Git 管理。
-- V1__create_order_procedure.sql
DELIMITER $$
CREATE PROCEDURE create_order(IN p_user_id INT, IN p_product_id INT, IN p_quantity INT)
BEGIN
-- ...
END$$
DELIMITER ;
4. 数据库压力大（占用 CPU、内存）
问题：存储过程在数据库层执行，占用数据库 CPU、内存（应用层可以水平扩展，数据库不行）。
场景：如果存储过程很复杂（比如循环 100 万次），会把数据库 CPU 打满，影响其他业务。
解决方案：存储过程里别写复杂逻辑（比如大量循环、条件判断），只写简单的 SQL 操作。
实战：存储过程使用场景
场景 1：复杂的数据操作（比如批量更新）
合适用存储过程的场景：复杂的数据操作（比如批量更新、多表关联更新）。
-- 批量更新用户等级（根据订单金额）
DELIMITER $$
CREATE PROCEDURE update_user_level()
BEGIN
-- 临时表：存每个用户的订单总金额
DROP TEMPORARY TABLE IF EXISTS user_order_total;
CREATE TEMPORARY TABLE user_order_total AS
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;
-- 更新用户等级
UPDATE users u
JOIN user_order_total t ON u.id = t.user_id
SET u.level =
CASE
WHEN t.total_amount >= 10000 THEN 'VIP3'
WHEN t.total_amount >= 5000 THEN 'VIP2'
ELSE 'VIP1'
END;
-- 删除临时表
DROP TEMPORARY TABLE user_order_total;
END$$
DELIMITER ;
-- 调用存储过程
CALL update_user_level();
好处：
- 性能好（1 次网络往返，数据库层完成）
-
- 业务逻辑集中（不用应用层写复杂的 SQL）
场景 2：定时任务（比如每天凌晨统计）
合适用存储过程的场景：定时任务（比如每天凌晨统计昨日数据）。
-- 每天凌晨统计昨日新增用户数，插入统计表
DELIMITER $$
CREATE PROCEDURE stats_daily_new_users()
BEGIN
DECLARE v_count INT;
-- 统计昨日新增用户数
SELECT COUNT(*) INTO v_count FROM users
WHERE created_at >= CURDATE() - INTERVAL 1 DAY
AND created_at < CURDATE();
-- 插入统计表
INSERT INTO daily_stats (stats_date, new_users)
VALUES (CURDATE() - INTERVAL 1 DAY, v_count);
END$$
DELIMITER ;
-- 创建定时任务（每天凌晨 2 点执行）
CREATE EVENT IF NOT EXISTS evt_daily_new_users
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '02:00:00')
DO
CALL stats_daily_new_users();
```
**好处**：
1. **数据库层定时**（不用应用层写定时任务）
2. 2. **性能好**（数据库层完成，减少网络往返）
### 场景 3：事务控制（比如批量操作要原子性）
**合适**用存储过程的场景：**事务控制**（比如批量操作要原子性）。
```sql
-- 批量转账（要原子性：要么全成功，要么全失败）
DELIMITER $$
CREATE PROCEDURE batch_transfer(IN p_batch_id INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 出错回滚
ROLLBACK;
-- 更新批次状态为失败
UPDATE transfer_batches SET status = 'failed' WHERE id = p_batch_id;
END;
-- 开启事务
START TRANSACTION;
-- 批量转账（可能出错，比如余额不足）
INSERT INTO transfers (from_user_id, to_user_id, amount)
SELECT from_user_id, to_user_id, amount
FROM transfer_items
WHERE batch_id = p_batch_id;
-- 扣余额
UPDATE users u
JOIN transfer_items t ON u.id = t.from_user_id
SET u.balance = u.balance - t.amount
WHERE t.batch_id = p_batch_id;
-- 加余额
UPDATE users u
JOIN transfer_items t ON u.id = t.to_user_id
SET u.balance = u.balance + t.amount
WHERE t.batch_id = p_batch_id;
-- 提交事务
COMMIT;
-- 更新批次状态为成功
UPDATE transfer_batches SET status = 'success' WHERE id = p_batch_id;
END$$
DELIMITER ;
-- 调用存储过程
CALL batch_transfer(123);
好处：
- 事务控制（原子性，要么全成功，要么全失败）
-
- 性能好（1 次网络往返）
场景 4：复杂的数据校验（比如跨表校验）
合适用存储过程的场景：复杂的数据校验（比如跨表校验）。
-- 插入订单前，校验用户是否存在、商品是否存在、库存是否足够
DELIMITER $$
CREATE PROCEDURE validate_order(IN p_user_id INT, IN p_product_id INT, IN p_quantity INT)
BEGIN
DECLARE v_user_count INT;
DECLARE v_product_count INT;
DECLARE v_stock INT;
-- 校验用户是否存在
SELECT COUNT(*) INTO v_user_count FROM users WHERE id = p_user_id;
IF v_user_count = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户不存在';
END IF;
-- 校验商品是否存在
SELECT COUNT(*) INTO v_product_count FROM products WHERE id = p_product_id;
IF v_product_count = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '商品不存在';
END IF;
-- 校验库存是否足够
SELECT stock INTO v_stock FROM products WHERE id = p_product_id;
IF v_stock < p_quantity THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';
END IF;
-- 校验通过
SELECT 'validate_success' AS result;
END$$
DELIMITER ;
-- 调用存储过程
CALL validate_order(1, 100, 2);
好处：
- 数据库层校验（不用应用层写校验逻辑）
-
- 性能好（1 次网络往返）
实战建议
1. 互联网应用，高并发 → 别用存储过程
这是最重要的建议。存储过程难以调试、难以版本控制、数据库压力大，互联网应用别用。
// 互联网应用：用应用层
public void createOrder(int userId, int productId, int quantity) {
// 应用层写逻辑（好调试、好版本控制、可水平扩展）
// ...
}
```
### 2. 内部系统，数据量小 → 可以用存储过程
**如果是内部系统**（比如后台管理，并发不高），**数据量小**，可以用存储过程（性能好、业务逻辑集中）。
```sql
-- 内部系统：可以用存储过程
DELIMITER $$
CREATE PROCEDURE create_order(IN p_user_id INT, IN p_product_id INT, IN p_quantity INT)
BEGIN
-- ...
END$$
DELIMITER ;
3. 存储过程里别写复杂逻辑（比如大量循环、条件判断）
存储过程只适合简单的 SQL 操作（比如 INSERT、UPDATE、DELETE），不适合复杂逻辑（比如大量循环、条件判断）。
-- 错误：存储过程里写复杂逻辑（大量循环）
DELIMITER $$
CREATE PROCEDURE wrong_procedure()
BEGIN
DECLARE i INT DEFAULT 0;
-- 大量循环（性能差，数据库压力大）
WHILE i < 1000000 DO
INSERT INTO users (name) VALUES (CONCAT('user_', i));
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
优化：用 LOAD DATA 代替（性能提升 100 倍）。
-- 正确：用 LOAD DATA（性能好）
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name);
4. 用 Flyway 或 Liquibase 管理存储过程
存储过程要版本控制（Git 管理），用 Flyway 或 Liquibase。
-- V1__create_order_procedure.sql
DELIMITER $$
CREATE PROCEDURE create_order(IN p_user_id INT, IN p_product_id INT, IN p_quantity INT)
BEGIN
-- ...
END$$
DELIMITER ;
好处：Git 管理，版本控制，方便回滚。
总结
- 存储过程是预编译并存储在数据库里的一组 SQL 语句
-
- 存储过程的优点：
-
- 性能好（预编译 + 减少网络往返）
-
- 业务逻辑集中（不用应用层操心）
-
- 数据库管理员（DBA）好优化
-
- 存储过程的缺点：
-
- 可移植性差（绑定数据库）
-
- 难以调试（没有好用的 IDE）
-
- 难以版本控制（Git 不好管理）
-
- 数据库压力大（占用 CPU、内存）
-
- 存储过程使用场景：
-
- 复杂的数据操作（比如批量更新）
-
- 定时任务（比如每天凌晨统计）
-
- 事务控制（比如批量操作要原子性）
-
- 复杂的数据校验（比如跨表校验）
-
- 实战建议：互联网应用别用存储过程、内部系统可以用、存储过程里别写复杂逻辑、用 Flyway 或 Liquibase 管理存储过程
如果你能把存储过程的优缺点、使用场景、实战建议讲清楚，面试官绝对觉得你有实战经验。
- 实战建议：互联网应用别用存储过程、内部系统可以用、存储过程里别写复杂逻辑、用 Flyway 或 Liquibase 管理存储过程
实战代码都在我本地跑过，你可以放心复制。 如果有问题，欢迎评论区交流！
扫一扫
1119
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
