[大数据]MySQL之变量、存储过程以及函数精讲篇

MySQL之变量、存储过程以及函数精讲篇

0.前言

本文是在看了康师傅的b站的尚硅谷视频
总结出来的笔记以及见解

点击跳转

1.变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。

在 MySQL 数据库中，变量分为系统变量以及用户自定义变量。

1.1 系统变量

变量由系统定义，不是用户定义，属于服务器层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。

系统变量又可分为全局系统变量(global)，会话系统变量(session)。

1.1.1 查看所有系统变量

• 查看所有系统变量

show global variables;  # 全局
show session variables;  # 会话
show variables;  # 不写默认为会话

• 查看部分系统变量

show variables like 'character_%';
show global variables like 'admin_%';

1.1.2 查看指定系统变量(@@)

作为 MySQL 编码规范，MySQL 中的系统变量以两个“@”开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

#查看指定的系统变量的值
SELECT @@global.变量名;

#查看指定的会话变量的值
SELECT @@session.变量名;
#或者
SELECT @@变量名;

1.1.3 修改系统变量的值

• 方式1：修改MySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）

• 方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

	#为某个系统变量赋值
	#方式1：
	SET @@global.变量名=变量值;
	#方式2：
	SET GLOBAL 变量名=变量值;

	#为某个会话变量赋值
	#方式1：
	SET @@session.变量名=变量值;
	#方式2：
	SET SESSION 变量名=变量值;

• 举例：

  #举例全局系统变量
	set @@global.max_connections = 161;
	set global max_connections = 171;
	select @@global.max_connections;
	# 针对当前的数据库实例是有效的，一旦重启mysql服务，就失效了

	# 举例会话系统变量
	set @@session.character_set_client = 'utf8';
	set session character_set_client = 'utf8';
	# 针对于当前会话有效，一旦结束会话，建立新的连接（会话）后就失效了

1.2 用户自定义变量

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以一个“@”开头。根据作用范围不同，又分为会话用户变量和局部变量。

• 会话用户变量：作用域和会话变量一样，只对当前连接会话有效。
• 局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用。

1.2.1 会话用户变量

• 变量的定义

1)变量的声明和赋值
#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;

#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量  [FROM 等子句];

2)使用
select @变量名

• 查看用户变量的值（查看、比较、运算等）

SELECT @用户变量

• 举例

SET @a = 1;

SELECT @a;

SELECT @num := COUNT(*) FROM employees;

SELECT @num;

SELECT AVG(salary) INTO @avgsalary FROM employees;

SELECT @avgsalary;

SELECT @big;  #查看某个未声明的变量时，将得到NULL值

1.2.2 局部变量

• 定义：可以使用DECLARE语句定义一个局部变量

• 作用域：仅仅在定义它的 BEGIN … END 中有效

• 位置：只能放在 BEGIN … END 中，而且只能放在第一句

BEGIN
	#声明局部变量
	DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
	DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];

	#为局部变量赋值
	SET 变量名1 = 值;
	SELECT 值 INTO 变量名2 [FROM 子句];

	#查看局部变量的值
	SELECT 变量1,变量2,变量3;
END

1.定义变量

DECLARE 变量名 类型 [default 值];  # 如果没有DEFAULT子句，初始值为NULL

举例：

DECLARE　myparam　INT　DEFAULT 100;

2.变量赋值

方式1：一般用于赋简单的值

SET 变量名=值;
SET 变量名:=值;

方式2：一般用于赋表中的字段值

SELECT 字段名或表达式 INTO 变量名 FROM 表;

3.使用变量（查看、比较、运算等）

SELECT 局部变量名;

这里暂不举例，因为涉及到存储过程以及函数的使用，等讲解完这两部分再做举例。

1.2.3 对比

2.存储过程

MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起，应用程序无须关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可。

2.1 存储过程概述

含义：存储过程的英文是 Stored Procedure。它的思想很简单，就是一组经过预先编译的 SQL 语句的封装。

对于存储过程的使用，一直都存在着很多争议，比如有些公司对于大型项目要求使用存储过程，而有些公司在手册中明确禁止使用存储过程，为什么这些公司对存储过程的使用需求差别这么大呢？

2.1.1 优缺点

• 优点

  **1、存储过程可以一次编译多次使用。**存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。

  **2、可以减少开发工作量。**将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。

  **3、存储过程的安全性强。**我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。

  **4、可以减少网络传输量。**因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。

  **5、良好的封装性。**在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

• 缺点

  基于上面这些优点，不少大公司都要求大型项目使用存储过程，比如微软、IBM 等公司。但是国内的阿里并不推荐开发人员使用存储过程，这是为什么呢？

  阿里开发规范

  【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

  存储过程虽然有诸如上面的好处，但缺点也是很明显的。

  **1、可移植性差。**存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。

  **2、调试困难。**只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。

  **3、存储过程的版本管理很困难。**比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。

  **4、它不适合高并发的场景。**高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

总之，存储过程既方便，又有局限性。尽管不同的公司对存储过程的态度不一，但是对于我们开发人员来说，不论怎样，掌握存储过程都是必备的技能之一。

2.1.2 分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

• 1、没有参数（无参数无返回）
• 2、仅仅带 IN 类型（有参数无返回）
• 3、仅仅带 OUT 类型（无参数有返回）
• 4、既带 IN 又带 OUT（有参数有返回）
• 5、带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

2.2 创建和调用存储过程

2.2.1 语法分析

语法：

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体

END

类似于Java中的方法：

修饰符 返回类型 方法名(参数类型 参数名,...){

	方法体;
}

说明：

1、参数前面的符号的意思

• IN：当前参数为输入参数，也就是表示入参；

  存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。

• OUT：当前参数为输出参数，也就是表示出参；

  执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。

• INOUT：当前参数既可以为输入参数，也可以为输出参数。

2、形参类型可以是 MySQL数据库中的任意类型。

3、characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
• [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。
  • CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
  • NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
  • READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
  • MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
  • 默认情况下，系统会指定为CONTAINS SQL。
• SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
  • DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
  • INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
  • 如果没有设置相关的值，则MySQL默认指定值为DEFINER。
• COMMENT 'string'：注释信息，可以用来描述存储过程。

4、存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

5、需要设置新的结束标记

DELIMITER 新的结束标记

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

示例：

DELIMITER $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
[characteristics ...]
BEGIN
	sql语句1;
	sql语句2;

END $

2.2.2 调用格式

存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。

CALL 存储过程名(实参列表)

格式：

1、调用in模式的参数：

CALL sp1('值');

2、调用out模式的参数：

SET @name;
CALL sp1(@name);
SELECT @name;

3、调用inout模式的参数：

SET @name=值;
CALL sp1(@name);
SELECT @name;

2.2.3 代码示例

• 准备工作

  # 0. 准备工作
  create database dbtest15;
  use dbtest15;
  
  create table employees 
  as 
  select * from atguigudb.employees;
  
  create table departments
  as 
  select * from atguigudb.departments;
  
  select * from employees;
  select * from departments;
  

  

  

• 类型一：空参

  • 举例1:创建存储过程select_all_data(),查看emps表的所有数据

    delimiter $
    create procedure select_all_data()
    begin
    		select * from employees;
    end $
    delimiter ;
    
    call select_all_data();
    

    

  • 举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资

    delimiter //
    create procedure avg_employee_salary()
    begin
    		select avg(salary) from employees;
    end //
    delimiter ;
    
    call avg_employee_salary();
    

    

  • 举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值。

    delimiter //
    create procedure show_max_salary()
    begin
    		select max(salary) from employees;
    end //
    delimiter ;
    
    call show_max_salary();
    

    

• 类型二：带out

  • 举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出

    delimiter //
    create procedure show_min_salary(out ms double)
    begin
       select min(salary) into ms from employees;
    end //
    delimiter ;
    # 调用
    call show_min_salary(@ms);
    # 查看变量值
    select @ms;
    

    

• 类型三：带in

  • 举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名。

    delimiter //
    create procedure show_someone_salary(in empname varchar(20))
    begin
       select salary from employees 
    	 where last_name = empname; 
    end//
    delimiter ;
    
    # 调用方式一：
    call show_someone_salary('Abel');
    # 调用方式二：
    set @empname = 'Abel';
    # set @empname := 'Abel';  := 赋值符号
    call show_someone_salary(@empname);
    

    

• 类型四：带 in 和 out

  • 举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。

    delimiter //
    create procedure show_someone_salary2(in empname varchar(20),out empsalary double)
    begin
          select salary into empsalary from employees
          where last_name = empname;
    end//
    delimiter ;
    # 调用
    set @empname = 'Abel';
    call show_someone_salary2(@empname,@empsalary);
    
    select @empsalary;
    

  

• 类型五：带 inout

  • 举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名。

    delimiter //
    create procedure show_mgr_name(inout empname varchar(20))
    begin
    		select last_name into empname from employees
    		where employee_id in 
    		(select manager_id from employees where last_name = empname);
    end//
    delimiter ;
    
    # 调用
    set @empname = 'Abel';
    call show_mgr_name(@empname);
    select @empname;
    
    

    

3.存储函数

MySQL本身提供了很多的函数，比如日期函数，字符串函数等。使用这些函数可以对数据进行的各种处理操作，极大地提高用户对数据库的管理效率。MySQL支持自定义函数，定义好之后，调用方式与调用MySQL预定义的系统函数一样。

3.1 创建语法

语法格式：

CREATE FUNCTION 函数名(参数名 参数类型,...) 
RETURNS 返回值类型
[characteristics ...]
BEGIN
	函数体   #函数体中肯定有 RETURN 语句

END

说明：

1、参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。

2、RETURNS type 语句表示函数返回数据的类型；

RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。

4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

注意：

若在创建存储函数中报错“you might want to use the less safe log_bin_trust_function_creators variable”，有两种处理方法：

• 方式1：加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”

• 方式2：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

3.2 调用语法

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

SELECT 函数名(实参列表)

3.3 代码示例

• 举例1：创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型。

  delimiter //
  create function email_by_name()
  returns varchar(25)
  begin
  		return	(select email from employees where last_name = 'Abel');
  end //
  delimiter ;
  
  select email_by_name();
  

  

• 举例2: 创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型为字符串型。

  delimiter //
  create function email_by_id(emp_id int)
  returns varchar(25)
  begin
  	   return(select email from employees where employee_id = emp_id);
  end //
  delimiter ;
  
  select email_by_id(100);
  
  set @emp_id = 102;
  select email_by_id(@emp_id);
  

  

• 举例3：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。

  delimiter //
  create function count_by_id(dept_id int)
  returns int
  begin
  		return (select count(*) from departments where department_id = dept_id);
  end //
  delimiter ;
  
  set @dept_id = 50;
  select count_by_id(@dept_id);
  

  

4.存储过程以及函数的查看、修改和删除

4.1 查看

MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可直接从系统的information_schema数据库中查询。这里介绍3种方法。

1. 使用SHOW CREATE语句查看存储过程和函数的创建信息

基本语法结构如下：

SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

举例：

show create procedure show_mgr_name\G

2. 使用SHOW STATUS语句查看存储过程和函数的状态信息

基本语法结构如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

[LIKE ‘pattern’]：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程或函数的信息。
举例：SHOW STATUS语句示例，代码如下：

3. 从information_schema.Routines表中查看存储过程和函数的信息

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

说明：如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。

举例：

4.2 修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。
• NO SQL，表示子程序中不包含SQL语句。
• READS SQL DATA，表示子程序中包含读数据的语句。
• MODIFIES SQL DATA，表示子程序中包含写数据的语句。
• SQL SECURITY { DEFINER | INVOKER }，指明谁有权限来执行。
  • DEFINER，表示只有定义者自己才能够执行。
  • INVOKER，表示调用者可以执行。
• COMMENT 'string'，表示注释信息。

修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。但是，这两个语句的结构是一样的，语句中的所有参数也是一样的。

举例：

alter procedure show_max_salary
sql security invoker 
comment '查询最高工资';

show procedure status like 'show_max_salary'\G

4.3 删除

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。

drop function count_by_id;
drop function if exists count_by_id;# (建议)

5.对比存储函数和存储过程

6.局部变量的举例使用

• 举例1：声明局部变量，并分别赋值为employees表中employee_id为102的last_name和salary

  DELIMITER //
  CREATE PROCEDURE set_value()
  BEGIN
  	DECLARE emp_name VARCHAR(25);
  	DECLARE sal DOUBLE(10,2);
  	
  	SELECT last_name,salary INTO emp_name,sal
  	FROM employees 
  	WHERE employee_id = 102;
  	
  	SELECT emp_name,sal;
  END //
  DELIMITER ;
  call set_value();
  

  

• 举例2：创建存储过程“different_salary”查询某员工和他领导的薪资差距，并用IN参数emp_id接收员工id，用OUT参数dif_salary输出薪资差距结果。

  delimiter //
  create procedure different_salary(in emp_id int,out dif_salary double)
  begin
  		declare emp_sal double;
  		declare man_sal double;
  		declare man_id int;
  		
  		select manager_id,salary into man_id,emp_sal from employees 
  		where emp_id = employee_id;
  		
  		select salary into man_sal from employees where employee_id = man_id;
  		
  		set dif_salary = man_sal - emp_sal;
  end//
  delimiter ;
  
  set @emp_id = 102;
  call different_salary(@emp_id,@diff_sal);
  select @diff_sal;