MySQL存储过程讲解

标签: MySQL存储过程  数据库

相信很多开发者在平时的工作中,大多数都不会接触到数据库架构这方面的话题,所以在开始谈存储过程之前,先对MySQL的基本架构做一个简单的介绍,这样在阅读后面章节的时候,会对MySQL数据库有一个整体的认识。

1. MySQL数据库基本架构补充

就以我们身边的例子开始讲解,大家平时使用的qq、微信、支付宝、微博等软件,都是由两部分组成,一部分是客户端程序,另一部分是服务器端程序。我们平时使用MySQL的过程和使用这些软件是一样的,只不过在使用MySQL客户端连接服务器之后,MySQL服务器操作的是我们存储的数据。在实际工作中使用MySQL的流程一般是下图这样的:
enter image description here
从上图中可以看出,MySQL服务器处理客户端发起的请求的需要经过两部分处理,分别是解析优化、存储引擎。在上图中其实是少了连接管理这一步,以下分别对这几部分做一个简单的解释。
(1) 解析优化
MySQL客户端发送的增删改查指令到达MySQL服务器端,服务器端获取到的是文本形式的请求,MySQL服务层要对接收到的文本请求做一系列的处理操作,在这些操作中比较重要的就是图中所示的查询缓存、查询解析、查询优化操作,所有跨存储引擎的功能都是在这一层实现的,所以所有和存储引擎无关的特性都在这一层。经过处理之后生成执行计划,最后调用存储引擎提供的API。
(2) 存储引擎
经过上面MySQL服务层的处理生成执行计划为止,其实还没有真正访问到真实的数据表。MySQL服务器把对数据的增删改查操作都封装在了存储引擎模块。MySQL提供了多种类型的存储引擎,不同的存储引擎对表的存储结构是不一样的,对MySQL服务层开放的API也是不一样的。
存储引擎是数据库底层软件组件,不同的存储引擎提供不同的索引技巧、存储机制等功能。一个库中的多张表可以选择不同的存储引擎,对每张表根据需求选择不同的存储引擎会提高整个数据库的性能(那么为什么说选择表的存储引擎了?因为存储引擎是针对于表的,而不是针对于库的)。
如何选择表的存储引擎?
可以根据不同的实际需求选择不同的存储引擎:
(a)InnoDB支持回滚、提交和恢复的事务安全(ACID 兼容)能力;
(b)MyISAM引擎对记录的插入和查询提供比较高的处理效率、如果数据表主要用来插入和查询数据,可以选择MyISAM引擎
(c)Archive 引擎支持高并发的插入操作,如果只有插入和查询,也可以选择Archive 引擎
你可能会问,Archive 引擎和MyISAM引擎一样,我们该如何选择了?
其实这两个存储引擎在底层是有本质上的区别的,MyISAM引擎有存储限制,存储量是256TB,而Archive 引擎没有存储限制;MyISAM引擎支持全文索引、树索引,而Archive 引擎不支持;如果是记录日志记录这些归档数据的表,可以优先选择Archive 引擎
(d)MEMORY 引擎将数据保存在内存中,内存的一个特点就是执行效率贼快,所以你设计的数据表是用来存放临时数据、数据量不大,并且对数据的安全性要求不是贼高的话,优先选择MEMORY 引擎;

MySQL支持多种存储引擎,这里只对常见的几种存储引擎做了一个简单的介绍,其他的存储引擎可以自己查找资料充电。
(3) 连接管理
对于连接管理这里不多做介绍,主要是对不同的操作系统来划分的,MySQL客户端程序可以采用Unix域套接字、TCP/IP、命名管道与服务器程序建立连接。

对MySQL整个的请求流程,其实可以用生活中的很多例子来解释,比如厨师做菜,厨师拿到菜单(连接管理),拿到菜单可以有多重途径,厨师对菜单做一定的优化、分解、缓存等处理(解析优化),最后选择厨灶炒菜(存储引擎)。在我们的生活中,有很多这方面的例子,你可以根据自己的理解去发现,计算机本来就是源于生活,服务于生活的工具而已。

通过上面的介绍,相信你对MySQL的基础架构有一个大致的了解了,相信今后你遇到面试官问你怎么设计一个MySQL数据库,也不会不知所措了,其实设计MySQL数据库和设计一套系统原理一样,将它根据功能划分成单独的模块,这样问题就迎刃而解了。所以理解知识最好的办法就是站在设计者的角度去理解它。

###2. 存储过程的基本结构
一般的开发人员对数据库的学习大多数停留在sql语句的编写上,主要针对的就是对数据表进行单条sql语句的操作,或者比较复杂点的联表查询。但是并非所有的操作都是那么简单,有时候遇到的业务场景需要多条sql加条件、循环操作才能完成。

一个存储过程是一个可编程的函数,存储过程在数据库中创建保存。存储过程由sql语句、变量、判断语句、循环语句、游标等组成。如果我们希望在不同的平台或者不同的系统上执行对同一个数据库做相同的操作的时候,存储过程尤为合适。存储过程的结构如下所示:

DELIMITER //
CREATE PROCEDURE obtain_all_student()
BEGIN
SELECT * FROM student;
END //
DELIMITER ;

DELIMITER不是存储过程的语法, DELIMITER英文意思是分隔符分隔符、分界符的意思。在这里,DELIMITER的作用是将标准分隔符";“修改为分隔符”//",更改分隔符的目的是为了将存储过程作为一个整体传给服务器,大家都知道,";“分隔符,服务器是一次解释一个语句,END//标志着存储过程的结束;最后使用DELIMITER将分隔符改为”;"。

CREATE PROCEDURE命令是创建一个新的存储过程,后面的"obtain_all_student"是存储过程的名字。
创建好存储过程之后,使用CALL obtain_all_student()可以调用存储过程。
在可视化工具创建存储过程非常简单,这里使用Navicat可视化工具演示,如下图所示:
enter image description here
在可视化工具中打开要创建存储过程的数据库,找到functions右键创建存储过程。在可视化工具中创建完存储过程直接点击执行按钮,就可以调用执行。
这里补充一个使用Navicat打开控制台的方法,找到Tools–>console,就可以打开控制台连接,如下图:
打开控制台


控制台界面

3. 存储过程的变量、参数

(1)声明变量
高级语言之所以能够处理复杂的业务逻辑,是因为高级语言可以定义变量(当然不全是因为这个它就是高级语言了),使用变量进行传参、判断、循环等等一系列操作。我们知道SQL语句是没有变量的,在编写SQL的时候,需要使用占位符利用程序传入获取记录的条件。所以遇到复杂的业务逻辑,使用一般的SQL语句是很难实现的,当然你一定要写一个可以绕赤道几圈的SQL出来解决问题,那我只能献上我的膝盖了。幸运的是我们今天讨论的存储过程,是可以自定义变量的,下面详细讨论MySQL存储过程的变量,包括变量的声明、使用、作用域。

我们知道,变量必须先声明、后使用,在存储过程中使用DECLARE声明一个变量,如下所示:

DECLARE stopFlag INT DEFAULT 0;

DECLARE关键字后面是变量名,变量名必须遵循MySQL的命名规则,对于MySQL的设计规范和命名规范,可以看我在csdn上的一篇博客《MySql设计规范简介》,在变量名后面是变量的数据类型,数据类型可以是任何MySQL数据类型,如:VARCHAR(32)、INT等等,这里不多做介绍。在存储过程中对变量的初始化,使用关键字DEFAULT,如上面示例。
MySQL存储过程和高级语言类似,可以使用一个关键字DECLARE同时声明多个变量,并初始化,如下所示:

DECLARE stopFlag, startFlag INT DEFAULT 0;

声明了两个变量stopFlag、startFlag,默认值都是0
(2)为变量赋值
和高级语言开发一样,声明完变量之后,就是使用它了。在存储过程中为变量赋值可以使用SET语句,例如要给上面示例中的stopFlag赋值,可以如下:

SET stopFlag = 66;

上面语句给变量stopFlag赋值66,变量的值变为66 。

在存储过程中,还有一种赋值方式:使用SELECT INTO语句将查询到的结果赋值给变量,如下所示:

SELECT user_status INTO userStatus FROM user WHERE user_id='1024';

上面示例中,先从user表中查找user_id为1024的用户信息,然后使用SELECT INTO语句,将查询结果用户状态user_status赋值给变量userStatus 。
(3)变量的作用域
在高级语言(Java、php、Python、C、C++等)变量的作用域是以代码块区分的,在存储过程中,变量的作用域是按照BEGIN END区分的,当变量到达END时,它就会超出范围,其他BEGIN END之间的代码时不能获取到变量值的,所以,我们可以在不同的BEGIN END里面声明相同变量名的变量,程序不会报错,当然我们不会这么做,这样做代码的可读性严重下降。
在存储过程中还有一种变量是以@符号开头的,这种变量是会话变量,所以这种变量在会话结束之前都是可用、可访问的。

4. 存储过程的条件、循环语句

存储过程提供了一些高级语言的特性,那就是条件控制、循环语句,这使得在存储过程中根据这些控制语句+变量,就可以处理复杂的业务需求。
4.1 条件语句
在存储过程中提供了两种条件控制语句,语法如下所示:
IF语句:

IF if_expression THEN 
  statements;
  ELSEIF elseif-expression THEN
    elseif-statements;
ELSE
  else-statements;
END IF;

CASE语句:

CASE case_expression WHEN when_expression_1 THEN commands WHEN when_expression_2 THEN commands ELSE commands

存储过程中的条件控制和Java、Python、php等高级语言的一样,这里不多做介绍,在后面的实战例子中会频繁用到。
4.2 循环语句
循环循环,就是重复的执行同一个代码块,在MySQL存储过程中,给我们提供了WHILE,REPEAT和LOOP三种循环语句。这里不多赘述概念性的东西,我这里选择使用语法加图解的方式,说明每一个循环语句的语法,如下所示:
WHILE 循环语句:
WHILE expression DO
statements
END
while循环控制

REPEAT 语句:
REPEAT
statements;
UNTIL expression
END REPEAT
enter image description here
这里注意repeat和while的条件判断不一样,repeat是条件为true时停止。

LOOP 语句:

loop_label: loop
    set variable = variable + 1;
    if variable >= 6 then
        leave loop_label;
    end if;
end loop;

``
在使用LOOP循环语句的时候,要结合LEAVE和ITERATE 语句一起使用。LEAVE 就是高级语言的break ITERATE 就是高级语言的continue。

5. 存储过程的游标使用

游标其实就是一个查询的结果集。在数据库中,游标可以实现一些复杂的数据修改的需求,游标分为两种,一种是敏感游标,另一种是不敏感游标。游标主要有一下两个特性:
**只读:**无法根据光标去更新数据表中的数据;
**不可滚动:**获取游标的时候,只能按照select语句查出的顺序逐行获取,不能从后往前获取,也不能一行或者直接跳到指定的行。下面是一个简单的游标声明:

DECLARE user_cur CURSOR FOR select user_name from user where user_sex%2 = 0;

上面例句就声明了一个游标,在user_cur里面放的就是从user表中获取的性别为偶数的用户姓名。
注意:在声明游标的时候,变量必须声明在游标前面,并且游标声明之后需要使用FOR获取赋值结果集。游标的整个使用流程如下图:
enter image description here

警告!:游标为空之后一定要记得释放掉,因为游标结果集是放在内存中的,如果不释放游标,它占用的内存资源就会一直保存,所以切记一定要释放掉游标。
游标的使用细节在
实战讲解
里面用一个例子详细讲解。

6. MySQL函数、触发器

6.1 函数
大家都知道,每一个数据库都提供了大量的系统函数,但是所使用的系统函数并不能很好的和业务相互结合,有时候我们需要函数和业务相结合,需要自定义函数。MySQL中自定义函数语法如下所示:

CREATE FUNCTION defined_function_name(parameter1,parameter2,...)
RETURNS datatype
[NOT] DETERMINISTIC
statements

CREATE FUNCTION后面是函数名;
CREATE FUNCTION指定返回值的数据类型
MySQL的函数参数,都是IN参数,不能为参数指定IN,OUT或INOUT修饰符。
在这里举一个将用户信息的id、名称、性别合并成一个字段返回的函数,如下所示:

CREATE FUNCTION mergeUserInfo() RETURNS int(11)
  BEGIN
    DECLARE merge_value varchar(32) DEFAULT '';
    SELECT concat(user_id, '_', user_name, '_', user_sex) INTO merge_value FROM user; 
    RETURN merge_value;
  END

在上面示例中使用到了MySQL的concat()函数。
6.2 触发器
触发器通常用于审计的目的,也用于业务数据的完整性。当有人往一张表中插入一条记录时,把插入的用户id、用户名等,以及插入动作和操作时间都记录下来,对于触发器,使用一个示例代码进行讲解,示例代码如下:

create trigger tr_user_insert after insert on user
for each ROW
BEGIN
    insert into user_log(user_id, user_name, action, operate_time)
    values(NEW.user_id, NEW.user_name, 'insert', now())
END

创建触发器使用create trigger,后面时触发器名称,紧接着时after,意味着时操作之后进行插入动作;
for each ROW是指对每行都受影响。
上面示例是记录每一次对user表进行插入操作时,会在user_log插入一条操作日志。

7. 实战讲解

根据用户信息表更新用户表信息
下面示例时根据sp_goods表中的BRAND_NAME字段是否包含bgm_goods_brand表NAME_EN字段的值,去更新sp_goods表的BRAND_NAME字段的值为NAME_EN + NAME_CN拼接的值。

CREATE PROCEDURE sp_goods_data_replace_procedure()

BEGIN
DECLARE stopFlag INT DEFAULT 0;
DECLARE name_cn_result VARCHAR(32) DEFAULT ‘’;
DECLARE name_en_result VARCHAR(32) DEFAULT ‘’;
DECLARE nameCn_cur CURSOR FOR SELECT NAME_CN FROM bgm_goods_brand;
DECLARE CONTINUE HANDLER FOR NOT found SET stopFlag=1;

OPEN nameCn_cur;
FETCH nameCn_cur INTO name_cn_result;
WHILE (stopFlag=0) DO
BEGIN
	SELECT NAME_EN INTO name_en_result FROM bgm_goods_brand WHERE NAME_CN=name_cn_result;
	UPDATE sp_goods SET BRAND_NAME = CONCAT(name_en_result,'(',name_cn_result,')') WHERE  BRAND_NAME LIKE CONCAT('%',name_cn_result,'%');
	FETCH nameCn_cur INTO name_cn_result;
END;
END WHILE;
CLOSE nameCn_cur;

END
在上面示例中,使用到了前面讲解的变量、游标、循环的知识,可以很简单的去通过一个表的字段是否包含另一个表的字段,去更新数据。
在使用游标的时候,使用到了游标的句柄CONTINUE HANDLER,去判断游标句柄是不是最后一个,如果是最后一条记录,则设置stopFlag为1,停止循环。
实战讲解暂时就列举这一个,如果有什么需求,可以贴到读者圈,我看到会随时更新!
如果想了解更多技术知识,可以去公众号『编程之艺术』看文章,目前文章不是特别多,但是我每天都会更新免费的技术文章,研究底层原理,和大家一起探讨!

原文链接:加载失败,请重新获取