MySQL核心-存储过程与函数
大约 7 分钟
MySQL核心-存储过程与函数
业务场景引入
在电商系统中，订单处理涉及多个复杂的业务逻辑：
- 库存检查与扣减：确保商品有足够库存
- 价格计算：应用优惠券、会员折扣等
- 积分奖励：根据消费金额计算积分
- 物流安排：选择合适的配送方式
这些复杂的业务逻辑如果在应用层处理，会增加网络开销和数据不一致的风险。存储过程可以将这些逻辑封装在数据库端，提高性能和数据一致性。
存储过程基础
创建存储过程
-- 简单的存储过程示例：用户注册
DELIMITER $$
CREATE PROCEDURE RegisterUser(
IN p_username VARCHAR(50),
IN p_email VARCHAR(100),
IN p_password_hash VARCHAR(255),
OUT p_user_id BIGINT,
OUT p_result_code INT,
OUT p_message VARCHAR(200)
)
BEGIN
DECLARE v_count INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_result_code = -1;
SET p_message = 'Registration failed due to database error';
END;
START TRANSACTION;
-- 检查用户名是否已存在
SELECT COUNT(*) INTO v_count FROM users WHERE username = p_username;
IF v_count > 0 THEN
SET p_result_code = 1;
SET p_message = 'Username already exists';
ROLLBACK;
ELSE
-- 检查邮箱是否已存在
SELECT COUNT(*) INTO v_count FROM users WHERE email = p_email;
IF v_count > 0 THEN
SET p_result_code = 2;
SET p_message = 'Email already exists';
ROLLBACK;
ELSE
-- 创建用户
INSERT INTO users (username, email, password_hash, created_at)
VALUES (p_username, p_email, p_password_hash, NOW());
SET p_user_id = LAST_INSERT_ID();
SET p_result_code = 0;
SET p_message = 'User registered successfully';
COMMIT;
END IF;
END IF;
END$$
DELIMITER ;
-- 调用存储过程
CALL RegisterUser('john_doe', 'john@example.com', 'hashed_password', @user_id, @result, @msg);
SELECT @user_id, @result, @msg;复杂业务逻辑：订单处理
DELIMITER $$
CREATE PROCEDURE ProcessOrder(
IN p_user_id BIGINT,
IN p_products JSON, -- [{"product_id": 1, "quantity": 2}, {"product_id": 2, "quantity": 1}]
IN p_coupon_code VARCHAR(50),
OUT p_order_id BIGINT,
OUT p_final_amount DECIMAL(10,2),
OUT p_result_code INT,
OUT p_message VARCHAR(500)
)
BEGIN
DECLARE v_product_id BIGINT;
DECLARE v_quantity INT;
DECLARE v_price DECIMAL(10,2);
DECLARE v_stock INT;
DECLARE v_subtotal DECIMAL(10,2) DEFAULT 0.00;
DECLARE v_discount DECIMAL(10,2) DEFAULT 0.00;
DECLARE v_shipping_fee DECIMAL(6,2) DEFAULT 15.00;
DECLARE v_order_no VARCHAR(32);
DECLARE v_product_count INT DEFAULT 0;
DECLARE v_current_index INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_result_code = -1;
SET p_message = 'Order processing failed';
END;
START TRANSACTION;
-- 生成订单号
SET v_order_no = CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(CONNECTION_ID(), 4, '0'));
-- 获取商品数量
SET v_product_count = JSON_LENGTH(p_products);
-- 创建订单主记录
INSERT INTO orders (order_no, user_id, order_status, order_date)
VALUES (v_order_no, p_user_id, 'PENDING', NOW());
SET p_order_id = LAST_INSERT_ID();
-- 处理每个商品
WHILE v_current_index < v_product_count DO
SET v_product_id = JSON_EXTRACT(p_products, CONCAT('$[', v_current_index, '].product_id'));
SET v_quantity = JSON_EXTRACT(p_products, CONCAT('$[', v_current_index, '].quantity'));
-- 获取商品价格和库存
SELECT price, stock_quantity INTO v_price, v_stock
FROM products
WHERE product_id = v_product_id AND status = 'ACTIVE'
FOR UPDATE;
-- 检查库存
IF v_stock < v_quantity THEN
SET p_result_code = 1;
SET p_message = CONCAT('Insufficient stock for product ', v_product_id);
ROLLBACK;
LEAVE;
END IF;
-- 扣减库存
UPDATE products
SET stock_quantity = stock_quantity - v_quantity
WHERE product_id = v_product_id;
-- 创建订单明细
INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal)
VALUES (p_order_id, v_product_id, v_quantity, v_price, v_price * v_quantity);
-- 累计小计
SET v_subtotal = v_subtotal + (v_price * v_quantity);
SET v_current_index = v_current_index + 1;
END WHILE;
-- 处理优惠券
IF p_coupon_code IS NOT NULL AND p_coupon_code != '' THEN
CALL ApplyCoupon(p_user_id, p_coupon_code, v_subtotal, v_discount, @coupon_result);
IF @coupon_result != 0 THEN
SET v_discount = 0.00;
END IF;
END IF;
-- 计算最终金额
SET p_final_amount = v_subtotal + v_shipping_fee - v_discount;
-- 更新订单总额
UPDATE orders
SET subtotal = v_subtotal,
shipping_fee = v_shipping_fee,
discount_amount = v_discount,
total_amount = p_final_amount
WHERE order_id = p_order_id;
SET p_result_code = 0;
SET p_message = 'Order processed successfully';
COMMIT;
END$$
DELIMITER ;函数定义与使用
标量函数
-- 计算用户等级的函数
DELIMITER $$
CREATE FUNCTION GetUserLevel(p_total_spent DECIMAL(12,2))
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE v_level VARCHAR(20);
CASE
WHEN p_total_spent >= 10000 THEN SET v_level = 'DIAMOND';
WHEN p_total_spent >= 5000 THEN SET v_level = 'GOLD';
WHEN p_total_spent >= 1000 THEN SET v_level = 'SILVER';
ELSE SET v_level = 'BRONZE';
END CASE;
RETURN v_level;
END$$
DELIMITER ;
-- 使用函数
SELECT
user_id,
username,
total_spent,
GetUserLevel(total_spent) AS user_level
FROM user_statistics;复杂计算函数
-- 计算订单配送费的函数
DELIMITER $$
CREATE FUNCTION CalculateShippingFee(
p_total_weight DECIMAL(8,3),
p_distance_km INT,
p_is_express BOOLEAN,
p_user_level VARCHAR(20)
)
RETURNS DECIMAL(8,2)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE v_base_fee DECIMAL(8,2) DEFAULT 10.00;
DECLARE v_weight_fee DECIMAL(8,2) DEFAULT 0.00;
DECLARE v_distance_fee DECIMAL(8,2) DEFAULT 0.00;
DECLARE v_express_fee DECIMAL(8,2) DEFAULT 0.00;
DECLARE v_discount_rate DECIMAL(3,2) DEFAULT 1.00;
DECLARE v_final_fee DECIMAL(8,2);
-- 重量费用（超过1kg后每kg收费2元）
IF p_total_weight > 1.000 THEN
SET v_weight_fee = (p_total_weight - 1.000) * 2.00;
END IF;
-- 距离费用（超过50km后每10km收费3元）
IF p_distance_km > 50 THEN
SET v_distance_fee = CEIL((p_distance_km - 50) / 10) * 3.00;
END IF;
-- 快递费用
IF p_is_express THEN
SET v_express_fee = 20.00;
END IF;
-- 会员折扣
CASE p_user_level
WHEN 'DIAMOND' THEN SET v_discount_rate = 0.50;
WHEN 'GOLD' THEN SET v_discount_rate = 0.70;
WHEN 'SILVER' THEN SET v_discount_rate = 0.85;
ELSE SET v_discount_rate = 1.00;
END CASE;
-- 计算最终费用
SET v_final_fee = (v_base_fee + v_weight_fee + v_distance_fee + v_express_fee) * v_discount_rate;
-- 最低收费5元
IF v_final_fee < 5.00 THEN
SET v_final_fee = 5.00;
END IF;
RETURN v_final_fee;
END$$
DELIMITER ;流程控制结构
条件判断
DELIMITER $$
CREATE PROCEDURE ProcessRefund(
IN p_order_id BIGINT,
IN p_refund_reason VARCHAR(200),
OUT p_result_code INT,
OUT p_message VARCHAR(200)
)
BEGIN
DECLARE v_order_status VARCHAR(20);
DECLARE v_payment_date TIMESTAMP;
DECLARE v_refund_amount DECIMAL(10,2);
DECLARE v_days_since_payment INT;
-- 获取订单信息
SELECT order_status, payment_date, total_amount
INTO v_order_status, v_payment_date, v_refund_amount
FROM orders WHERE order_id = p_order_id;
-- 条件判断
IF v_order_status IS NULL THEN
SET p_result_code = 1;
SET p_message = 'Order not found';
ELSEIF v_order_status = 'CANCELLED' THEN
SET p_result_code = 2;
SET p_message = 'Order already cancelled';
ELSEIF v_order_status = 'REFUNDED' THEN
SET p_result_code = 3;
SET p_message = 'Order already refunded';
ELSE
-- 计算支付后天数
SET v_days_since_payment = DATEDIFF(NOW(), v_payment_date);
-- 根据不同情况处理退款
CASE v_order_status
WHEN 'PENDING' THEN
-- 未支付订单直接取消
UPDATE orders SET order_status = 'CANCELLED' WHERE order_id = p_order_id;
SET p_result_code = 0;
SET p_message = 'Order cancelled successfully';
WHEN 'PAID' THEN
IF v_days_since_payment <= 7 THEN
-- 7天内全额退款
CALL ProcessFullRefund(p_order_id, v_refund_amount);
SET p_result_code = 0;
SET p_message = 'Full refund processed';
ELSE
-- 超过7天收取10%手续费
SET v_refund_amount = v_refund_amount * 0.9;
CALL ProcessPartialRefund(p_order_id, v_refund_amount);
SET p_result_code = 0;
SET p_message = 'Partial refund processed (10% fee deducted)';
END IF;
WHEN 'SHIPPED' THEN
SET p_result_code = 4;
SET p_message = 'Cannot refund shipped order';
ELSE
SET p_result_code = 5;
SET p_message = 'Invalid order status for refund';
END CASE;
END IF;
END$$
DELIMITER ;循环结构
-- 使用WHILE循环批量处理数据
DELIMITER $$
CREATE PROCEDURE BatchUpdatePrices(IN p_category_id INT, IN p_adjustment_rate DECIMAL(5,4))
BEGIN
DECLARE v_product_id BIGINT;
DECLARE v_current_price DECIMAL(10,2);
DECLARE v_new_price DECIMAL(10,2);
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_count INT DEFAULT 0;
-- 声明游标
DECLARE product_cursor CURSOR FOR
SELECT product_id, price
FROM products
WHERE category_id = p_category_id AND status = 'ACTIVE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
OPEN product_cursor;
price_loop: LOOP
FETCH product_cursor INTO v_product_id, v_current_price;
IF v_done THEN
LEAVE price_loop;
END IF;
-- 计算新价格
SET v_new_price = v_current_price * p_adjustment_rate;
-- 更新价格
UPDATE products
SET price = v_new_price,
updated_at = NOW()
WHERE product_id = v_product_id;
SET v_count = v_count + 1;
-- 每处理100条记录输出一次进度
IF v_count % 100 = 0 THEN
SELECT CONCAT('Processed ', v_count, ' products') AS progress;
END IF;
END LOOP;
CLOSE product_cursor;
SELECT CONCAT('Total processed: ', v_count, ' products') AS final_result;
END$$
DELIMITER ;异常处理
错误处理机制
DELIMITER $$
CREATE PROCEDURE TransferFunds(
IN p_from_account BIGINT,
IN p_to_account BIGINT,
IN p_amount DECIMAL(12,2),
OUT p_result_code INT,
OUT p_message VARCHAR(200)
)
BEGIN
DECLARE v_from_balance DECIMAL(12,2);
DECLARE v_transfer_id BIGINT;
-- 声明异常处理器
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@text = MESSAGE_TEXT;
ROLLBACK;
SET p_result_code = -1;
SET p_message = CONCAT('Transfer failed: ', @text);
-- 记录错误日志
INSERT INTO error_logs (operation_type, error_code, error_message, created_at)
VALUES ('TRANSFER', @errno, @text, NOW());
END;
-- 自定义异常处理
DECLARE EXIT HANDLER FOR SQLSTATE '45000'
BEGIN
GET DIAGNOSTICS CONDITION 1 @custom_message = MESSAGE_TEXT;
ROLLBACK;
SET p_result_code = -2;
SET p_message = @custom_message;
END;
START TRANSACTION;
-- 检查转出账户余额
SELECT balance INTO v_from_balance
FROM accounts
WHERE account_id = p_from_account FOR UPDATE;
IF v_from_balance < p_amount THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Insufficient balance for transfer';
END IF;
-- 扣除转出账户金额
UPDATE accounts
SET balance = balance - p_amount
WHERE account_id = p_from_account;
-- 增加转入账户金额
UPDATE accounts
SET balance = balance + p_amount
WHERE account_id = p_to_account;
-- 记录转账记录
INSERT INTO transfers (from_account, to_account, amount, transfer_date, status)
VALUES (p_from_account, p_to_account, p_amount, NOW(), 'COMPLETED');
SET v_transfer_id = LAST_INSERT_ID();
COMMIT;
SET p_result_code = 0;
SET p_message = CONCAT('Transfer completed successfully. Transfer ID: ', v_transfer_id);
END$$
DELIMITER ;存储过程管理
查看和修改存储过程
-- 查看存储过程列表
SHOW PROCEDURE STATUS WHERE db = 'ecommerce';
-- 查看存储过程定义
SHOW CREATE PROCEDURE ProcessOrder;
-- 删除存储过程
DROP PROCEDURE IF EXISTS ProcessOrder;
-- 修改存储过程（需要先删除再创建）
DROP PROCEDURE IF EXISTS RegisterUser;
-- 然后重新创建修改后的版本
-- 查看函数列表
SHOW FUNCTION STATUS WHERE db = 'ecommerce';
-- 查看函数定义
SHOW CREATE FUNCTION GetUserLevel;权限管理
-- 创建存储过程执行用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
-- 授予存储过程执行权限
GRANT EXECUTE ON PROCEDURE ecommerce.ProcessOrder TO 'app_user'@'%';
GRANT EXECUTE ON PROCEDURE ecommerce.RegisterUser TO 'app_user'@'%';
-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION ecommerce.GetUserLevel TO 'app_user'@'%';
-- 查看权限
SHOW GRANTS FOR 'app_user'@'%';性能优化
存储过程调优
-- 使用索引优化存储过程性能
-- 确保WHERE条件中的字段有适当索引
ALTER TABLE products ADD INDEX idx_category_status (category_id, status);
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, order_status, order_date);
-- 避免在存储过程中使用SELECT *
-- ❌ 不好的做法
SELECT * FROM products WHERE category_id = p_category_id;
-- ✅ 好的做法
SELECT product_id, product_name, price, stock_quantity
FROM products
WHERE category_id = p_category_id;
-- 使用LIMIT限制结果集
SELECT product_id, product_name, price
FROM products
WHERE category_id = p_category_id
ORDER BY created_at DESC
LIMIT 100;监控存储过程性能
-- 查看存储过程执行统计
SELECT
object_schema,
object_name,
count_star AS exec_count,
sum_timer_wait/1000000000000 AS total_time_sec,
avg_timer_wait/1000000000000 AS avg_time_sec,
max_timer_wait/1000000000000 AS max_time_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE object_schema = 'ecommerce'
AND object_name IS NOT NULL
ORDER BY avg_timer_wait DESC;总结与最佳实践
使用场景选择
适合使用存储过程的场景：
- 复杂的业务逻辑处理
- 需要事务保证的多步操作
- 频繁执行的数据处理任务
- 需要减少网络传输的批量操作
不适合使用存储过程的场景：
- 简单的CRUD操作
- 需要频繁修改的业务逻辑
- 跨数据库平台的应用
- 复杂的计算密集型任务
开发规范
- 命名规范：使用有意义的过程名和参数名
- 参数验证：在过程开始处验证输入参数
- 异常处理：适当使用异常处理机制
- 事务控制：明确事务边界，避免长事务
- 性能优化：使用合适的索引和查询优化
存储过程是数据库端业务逻辑封装的重要工具，合理使用可以提高系统性能和数据一致性。