SQL 进阶:视图与存储过程的第一课
上篇我们学会了用事务保证多条写操作要么一起成功、要么一起回滚,用约束把规则写进表里,脏数据在入库那一刻就被拦住。查询写得对、写得安全之后,下一个问题是写得省事:同一段复杂的查询每个地方都要重写一遍,同一套固定流程每次都要拼一遍语句——视图和存储过程就是把这些常用的东西收进数据库里,起个名字,以后直接调用。
一、视图:给常用查询起个名字
视图是一张虚拟的表,它本身不存数据,存的是一条查询语句。每次查视图,数据库都会按那条语句重新算一遍结果。
比如经常要看每个用户的订单总额,原始写法要连接用户表和订单表再分组汇总,写一次不难,到处写就容易写错。把它存成视图,以后查视图就像查一张现成的汇总表:
CREATE VIEW user_order_total AS
SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;
建好之后这样用:
SELECT user_id, total_amount
FROM user_order_total
WHERE total_amount > 500;
视图的好处有三点:第一,复杂查询只写一次,业务代码变短,也不容易各处写出不一致的版本;第二,可以只把视图开放给某些人,原始表里的敏感列不暴露,等于多了一层权限隔离;第三,底层表结构微调时,只要视图的输出列不变,调用方不用跟着改。
视图也有边界:它不存数据,所以查视图的速度取决于底层那条查询本身,视图套视图层数多了会变慢;而且不是所有视图都能直接写入数据,带分组汇总的视图一般只读,想改数据还是回到原始表上操作。
二、存储过程:把固定流程收进数据库
存储过程是一段存在数据库里的程序:给它起个名字、定好输入参数,以后调用名字就能执行一整套语句,适合那种每次步骤固定、参数不同的操作。
比如给用户发一张优惠券,流程固定是查用户是否存在、往优惠券表插一条记录、返回新券编号。写成存储过程,应用层每次只用传一个用户编号:
CREATE PROCEDURE grant_coupon (IN p_user_id INT)
BEGIN
INSERT INTO coupons (user_id, amount, status)
VALUES (p_user_id, 50, 'unused');
SELECT LAST_INSERT_ID() AS coupon_id;
END
调用时只传参数:
CALL grant_coupon(1);
和视图一样,存储过程的核心价值也是复用加收口:流程只实现一次,改规则只改一处;应用层不用再拼长串语句,传参调用即可;权限也可以只开放执行权,不开放底层表的直接写权限。
存储过程也有代价:业务逻辑一旦进了数据库,调试和版本管理就比应用代码麻烦,换数据库时还要重写。所以经验是只把真正稳定、通用、贴近数据的流程放进去,比如批量结算、对账、定期归档;经常变的业务规则还是留在应用层更灵活。
三、视图和存储过程怎么选
两者都是复用,但位置不同:视图复用的是查询结果的样子,适合读多写少、到处都要的汇总口径;存储过程复用的是操作流程,适合步骤固定、需要写数据的固定动作。一个管“怎么看”,一个管“怎么做”。
回到上篇的例子就能对上号:查“每个用户的订单总额”用视图;“给新注册用户发券”用存储过程;转账那组扣款加记账的事务,可以包在存储过程里调用,事务保证一起成功、过程保证每次流程一致。
四、动手练习
- 给订单表建一个视图,只输出用户编号、订单数和订单总额,再用它查出订单总额超过 500 的用户。
- 写一个存储过程,输入用户编号,给该用户插一条默认金额的优惠券记录,调用两次,观察两次返回的编号是否不同。
- 对比一下:把“查某个月的订单总额”写成视图和写成存储过程各有什么不方便,什么场景下你会选哪一个。
小结
视图把常用的复杂查询收成一张虚拟表,查起来像查表,改起来只改一处;存储过程把固定的操作流程收成一个可调用的名字,调用方只传参数,不用再拼语句。一个让读更省事,一个让写更省事,合起来就是从“会写 SQL”到“把 SQL 组织成可复用的资产”的第一步。下一步可以学触发器:数据变化时让数据库自动执行一段逻辑,比如订单写入后自动扣减库存——但自动执行的逻辑也要小心,它会在你意想不到的时候悄悄跑起来。
- 点赞
- 收藏
- 关注作者
评论(0)