MySQL 存储过程案例实战

MySQL 存储过程案例实战

关注微信公众号 DevXTalk 为你提供价值

概述

MySQL5.0 版本开始支持存储过程。在MySQL中存储过程主要分为两类，一类是普通的存储过程，另一类则是触发器类型的存储过程。

存储过程:（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。
调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。

就是数据库 SQL 语言层面的代码封装与重用。

存储过程可以被看做是用 SQL 语言编写的一个函数,定义和调用。存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类

• IN 类型的参数表示接受调用者传入的数据；
• OUT 类型的参数表示向调用者返回数据；
• INOUT 类型的参数即可以接受调用者传入的参数，也可以向调用者返回数据。

使用存储过程的目的在于：将常用且复杂的SQL语句预先写好，然后用一个指定名称存储起来，
这个过程经过MySQL编译解析、执行优化后存储在数据库中，因此称为存储过程。当以后需要使用这个过程时，只需调用根据名称调用即可。

对比常规的SQL语句来说，普通SQL在执行时需要先经过编译、分析、优化等过程，最后再执行，而存储过程则不需要，一般存储过程都是预先已经编译过的

优缺点

优点

• 复用: 存储过程被创建后，可以在程序中被反复调用，不必重新编写该存储过程的SQL语句，同时库表结构发生更改时，
  只需要修改数据库中的存储过程，无需修改业务代码，也就意味着不会影响到调用它的应用程序源代码。

• 灵活: 普通的SQL语句很难像写代码那么自由，而存储过程可以用流程控制语句编写，也支持在其中定义变量，
  有很强的灵活性，可以完成复杂的条件查询和较繁琐的运算。

• 节省资源: 普通的SQL一般都会存储在客户端,每次执行SQL需要通过网络将SQL语句发送给数据库执行，
  而存储过程是保存在MySQL中的，因此当客户端调用存储过程时，只需要通过网络传送存储过程的调用语句和参数，无需将一条大SQL通过网络传输，从而可降低网络负载。

• 高性能: 存储过程执行多次后，会将SQL语句编译成机器码驻留在线程缓冲区，
  在以后的调用中，只需要从缓冲区中执行机器码即可，无需再次编译执行，从而提高了系统的效率和性能。

• 安全: 对于不同的存储过程，可根据权限设置执行的用户，因此对于一些特殊的SQL，例如清空表这类操作，可以设定root、admin用户才可执行。
  同时由于存储过程编写好之后，对于客户端而言是黑盒的，因此减小了SQL被暴露的风险。

缺点

• CPU开销大: 如果一个存储过程中涉及大量逻辑运算工作，会导致MySQL所在的服务器CPU飙升，
  因而会影响正常业务的执行，有可能导致MySQL在线上出现抖动，毕竟MySQL在设计时更注重的是数据存储和检索，对于计算性的任务并不擅长。

• 内存占用高: 为了尽可能的提升执行效率，因此当一个数据库连接反复调用某个存储过程后，
  MySQL会直接将该存储过程的机器码放入到连接的线程私有区中，当MySQL中的大量连接都在频繁调用存储过程时，这必然会导致内存占用率同样飙升。

• 维护性差: 一方面是过于复杂的存储过程，一般大多数后端开发人员可能不太熟悉，毕竟存储过程类似于一门新的语言，不同语言之间跨度较大。
  另一方面是很少有数据库的存储过程支持Debug调试，MySQL的存储过程就不支持，这也就意味着Bug出现时，无法像应用程序那样正常调试排查，
  必须得采取“人肉运维”模式，即一步步拆解存储过程并排查。

• 难以移植: 不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；
  不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大；

最佳实践

讨论任何问题必须要基于某个背景和上下文,否则最后都被变成空泛的讨论,无法解决现实问题。
讨论任何问题的前提都是基于现实,在不同的特定场景下决策者需要基于现实的诸多因素（例如现状，资金成本，时间成本，可持续性，人员等）
来总和考量，从而选择一种比较合适的解决方案，不是最好的是最合适的。

适合使用存储过程的场景

• 复杂 SQL : SQL 很大(比如几百行甚至上千行)，网络传输预编译耗时较长;
• 需要重复执行的任务 : 定时任务,定时的更新某些数据或删除某个增长速度很快且无价值的数据比如日志记录。或是某些需要定时统计的任务,从某几张表中抽取数据到另外的表;

如果你有其他更合适的应用场景请打在评论区接受大家的膜拜 🙂

语法

创建存储过程

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])  
  
BEGIN  
  
-- SQL语句  
  
END ;  

创建存储示例

CREATE PROCEDURE p1()  
  
BEGIN  
SELECT count(*) FROM user;  
END;  

创建带参数的存储过程示例

CREATE PROCEDURE pro_now_in(in time VARCHAR(20) CHARACTER set "utf8")  
BEGIN  
SELECT now(),time;  
end;  

调用存储过程示例

# 调用 p1 存储过程  
call p1();  
  
# 调用 pro_now_in 存储过程  
set @time='当前时间';  
call pro_now_in(@time); -- call pro_now_in('当前时间'); 这样也可以  

if 语句

CREATE PROCEDURE pro_if(in num INT)  
BEGIN  
DECLARE result VARCHAR(20) CHARACTER set utf8 DEFAULT null;  
IF num = 0 THEN -- 开始if判断，注意用一个等号"="  
set result='num 为0啦'; -- 满足条件  
ELSEIF num > 0 THEN -- 下一个if判断  
set result='num 大于 0';  
ELSEIF num < 0 THEN  
set result='num 小于 0';  
ELSE -- 所有条件不满足的情况下  
set result='num is null or other status';  
end if; -- 结束if 判断  
SELECT result;  
end;  

case 语句

CREATE PROCEDURE pro_case(in num INT)  
BEGIN  
DECLARE result VARCHAR(20) CHARACTER set utf8 DEFAULT null;  
case num -- 开始case 判断  
when 2 THEN -- 满足条件执行  
set result='num 值是2';  
when -2 THEN  
set result='num 值是-2';  
else -- 所有条件不满足，执行  
set result='num 不等于2和-2';  
end case ; -- 结束case语句  
SELECT result;  
end;  

while 循环语句

有条件的循环控制语句，当满足条件的时候进入循环，不满足条件的时候退出循环。

CREATE PROCEDURE pro_while(in num INT)  
BEGIN  
DECLARE i int;  
DECLARE result int;  
set i=0;  
set result=0;  
while i < num DO -- 开始while 循环  
set result=result+i;  
set i=i+1;  
end while; -- 结束while 循环  
SELECT result,i;  
end;  

repeat 循环语句

类似do while 循环 先执行在判断。

create procedure pro_repeat(in num int(11))  
begin  
# 定义存储总数的变量  
declare total int(255) default 0;  
repeat  
set total = total + number;  
set num = num - 1;  
until num = 0  
end repeat;  
select total;  
end;  

loop 循环语句

loop循环不需要初始条件,是一个死循环，需要leave进行结束。

CREATE PROCEDURE proc_loop ()  
begin  
declare v int;  
set v=0;  
LOOP_LABLE:loop  
set v=v+1;  
if v >=5 then  
leave LOOP_LABLE;  
end if;  
end loop;  
end;  

退出 leave 语句

类似 java中的break; 需要配合label标签用。

CREATE PROCEDURE proc_leave()  
begin  
declare var int;  
set var=0;  
whiel: while var<6 do  
set var=var+1;  
if var=3 then  
leave whiel;  
end if;  
end while;  
SELECT var;  
end  

继续循环 iterate 语句

类似 java中的continue,需要配合label标签用。

CREATE PROCEDURE proc_iterate()  
begin  
declare var int;  
set var=0;  
whiel: while var<6 do  
set var=var+1;  
if var=3 then  
iterate whiel;  
end if;  
end while;  
SELECT var;  
end  

游标 cursor 语句

类似 Java 中的ResultSet 获得结果集。

CREATE procedure proc_cursor()  
BEGIN  
declare stu_no char(50);  
declare stu_name char(50);  
declare stu_ssex char(50);  
DECLARE cursor_student CURSOR for  
SELECT sno,sname,ssex FROM student;  
OPEN cursor_student;  
FETCH cursor_student into stu_no,stu_name,stu_ssex;  
SELECT stu_no,stu_name,stu_ssex;  
END  
#出现的问题，一次遍历一个结果，需要使用循环进行遍历  
#但是遍历不知道有多少 可以使用handler进行  

handler 语句

类似于：try catch捕获异常。

CREATE procedure proc_handler(gender char(1))  
BEGIN  
#第一步：创建变量用于存储遍历的结果  
declare s_name char(50);  
declare s_dept char(50);  
declare age int;  
#创建一个状态：用于判断是否结束循环  
declare status_stu boolean DEFAULT true;  
#第二步：创建游标  
declare student_cursor cursor for  
SELECT stu.sname,stu.sdept,stu.sage FROM student stu WHERE ssex=gender;  
#第三步：创建handler 并改变状态码  
declare continue HANDLER FOR Not found set status_stu=false;  
#第四步：打开handler  
open student_cursor;  
#创建循环遍历  
stu_loop:loop  
FETCH student_cursor into s_name,s_dept,age;  
if status_stu then  
SELECT s_name,s_dept,age;  
ELSE  
#关闭游标，退出循环  
CLOSE student_cursor;  
leave stu_loop;  
end if;  
end loop;  
END  

管理存储过程

• 查看存储过程: SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

• 删除存储过程: DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ;

实战

理论知识准备了这么多终于要实战了...

背景: 对一张一千多万数据的表新增加了几个字段,这张表是一张统计表,数据来源于订单相关数据的抽取。
在增加了这些字段后需要对现存数据的这些新增字段做数据的填充写入。

全表 update 方式

update shop_order_sales_stats t1  
inner join (select *  
from order_1 t2  
union  
select *  
from order_2 t3) t4  
on t1.id = t4.id  
set t1.c1 = t4.c1,  
t1.c2 = t4.c2 ;  

这种方式会有导致锁表的风险,一旦锁表，锁的时间还很长的话，会带来更多的麻烦。
对历史数据的处理我宁可接受更慢一些，也不能接受锁表，因为它会影响目前现有的东西。

CREATE PROCEDURE update_historical_for_shop_order_sales_stats_table()  
begin  
DECLARE done INT DEFAULT FALSE;  
DECLARE `target_id` INT;  
  
DECLARE cur CURSOR FOR select id from shop_order_sales_stats ;  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
OPEN cur;  
  
read_loop: LOOP  
FETCH cur INTO target_id;  
IF done THEN  
LEAVE read_loop;  
END IF;  
  
update shop_order_sales_stats t1  
inner join (select *  
from order_1 where id = target_id  
union  
select *  
from order_2 where id = target_id) t4  
on t1.id = t4.id  
set t1.c1 = t4.c1,  
t1.c2 = t4.c2  
where t1.id = `target_id`;  
  
END LOOP;  
  
CLOSE cur;  
end;  
  
call update_historical_for_shop_order_sales_stats_table();