mysql存储过程实例详解

上周帮同事排查一个订单重复插入的存储过程实问题，最后发现是例详应用层没做幂等，但数据库这边其实可以用存储过程兜底。存储过程实我顺手写了个简单的例详例子，结果他看完说“原来存储过程没我想的存储过程实那么玄乎”。确实，例详很多人一听到“存储过程”就觉得是存储过程实上古遗物，或者只有DBA才碰的例详东西。其实在业务逻辑相对固定、存储过程实调用频繁的例详场景下，它能把网络交互和SQL解析的存储过程实开销省掉一大截。

先看个最基础的例详。假设有张用户表 users，存储过程实字段是例详 id、name、存储过程实age。现在要写个存储过程，根据用户ID查年龄：

DELIMITER // CREATE PROCEDURE get_user_age(IN uid INT, OUT user_age INT) BEGIN SELECT age INTO user_age FROM users WHERE id = uid; END // DELIMITER ; 

这里 DELIMITER // 是因为存储过程内部有分号，MySQL默认把分号当语句结束符，不改的话它读到第一个分号就以为你写完了。这坑我踩过，当时报错信息看得一头雾水。调用也简单：

CALL get_user_age(1, @age); SELECT @age; 

注意 IN 和 OUT 的方向。IN 是传进去的，OUT 是带出来的。如果既想传进去又想带出来，用 INOUT。我见过有人把所有参数都写成 IN，然后在过程里 SELECT 结果集返回，这当然也行，但客户端拿结果集的方式和拿 OUT 参数不一样，得看具体驱动怎么处理。

再进一步，加个条件判断。比如插入用户前检查年龄是否合法：

DELIMITER // CREATE PROCEDURE add_user(IN uname VARCHAR(50), IN uage INT) BEGIN IF uage < 0 OR uage > 150 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄不合法'; ELSE INSERT INTO users(name, age) VALUES(uname, uage); END IF; END // DELIMITER ; 

SIGNAL 是MySQL 5.5之后才有的，用来主动抛错。以前只能用 SELECT '错误' 这种土办法，客户端还得自己解析。现在直接抛SQLSTATE，应用层能捕获到标准异常。不过说实话，业务校验放数据库里，调试起来比放代码里麻烦，我一般只把强约束放这儿。

循环也常用。比如批量给用户加积分，每次加10分，加5次：

DELIMITER // CREATE PROCEDURE batch_add_points(IN uid INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i < 5 DO UPDATE users SET points = points + 10 WHERE id = uid; SET i = i + 1; END WHILE; END // DELIMITER ; 

DECLARE 必须写在 BEGIN 开头，不能中间插。变量作用域是当前 BEGIN...END 块。如果嵌套了块，内层能看见外层变量，反过来不行。这个规则和写代码时的块级作用域一个道理。

存储过程里还能开事务。比如转账，扣A加B，要么都成要么都败：

DELIMITER // CREATE PROCEDURE transfer(IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2)) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; UPDATE accounts SET balance = balance - amount WHERE id = from_id; UPDATE accounts SET balance = balance + amount WHERE id = to_id; COMMIT; END // DELIMITER ; 

EXIT HANDLER 捕获异常后回滚，RESIGNAL 把异常继续往上抛，让调用方知道出错了。不写 RESIGNAL 的话，异常就被吞了，应用层以为成功，实际数据没变。这个细节挺关键，我见过线上事故就是因为没重新抛。

最后说个实际感受。存储过程不适合写太复杂的业务，尤其是那种经常改逻辑的。改一次得 DROP 再 CREATE，版本管理也麻烦。但如果是数据清理、定时统计、或者高频短小的操作，它确实能减少应用和数据库之间的来回。我现在的习惯是：能用SQL直接搞定的不写过程，需要多步逻辑且调用频繁的，才考虑封装进去。别为了用而用。