1. 变量
在MySQL数据库的存储过程和函数中,可以使用变量来存储查询或计算的中间结果数据,或者输出最终的结果数据。
在 MySQL 数据库中,变量分为 系统变量 以及 用户自定义变量 。
1.1 系统变量
变量由系统定义,不是用户定义,属于 服务器 层面。
启动MySQL服务,生成MySQL服务实例期间,MySQL将为MySQL服务器内存中的系统变量赋值,这些系统变量定义了当前MySQL服务实例的属性、特
征。这些系统变量的值要么是 编译MySQL时参数 的默认值,要么是 配置文件 (例如my.ini等)中的参数值。
1.1.1 系统变量分类
系统变量分为全局系统变量(需要添加 global 关键字)以及会话系统变量(需要添加 session 关键字)。
有时也把全局系统变量简称为全局变量,有时也把会话系统变量称为local变量。如果不写变量是全局系统变量还是会话系统变量,默认是会话级别。
每一个MySQL客户机成功连接MySQL服务器后,都会产生与之对应的会话。会话期间,MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量,这些会话系统变量的初始值是全局系统变量值的复制。
在MySQL中,有些全局系统变量是所有会话共享的,一个会话对值进行了修改,所有会话中相应的全局系统变量的值也会发生修改;而有些全局系统变量既是全局系统变量又是会话系统变量(这个会话系统变量相当于全局系统变量的副本),此时的全局系统变量和会话系统变量一样,每个会话都有自己的系统变量,不同会话之间的不会发生干扰。
在MySQL中有些系统变量只能是全局的,例如 max_connections 用于限制服务器的最大连接数;有些系统变量作用域既可以是全局又可以是会话,例如 character_set_client 用于设置客户端的字符集;有些系统变量的作用域只能是当前会话,例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID。
- 全局系统变量针对于所有会话(连接)有效,但 不能跨重启,一旦重启,会重新进行系统默认值的赋值;
- 会话系统变量仅针对于当前会话(连接)有效。会话期间,当前会话对某个会话系统变量值的修改,不会影响其他会话同一个会话系统变量的值。
- 会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。
1.1.2 查看系统变量
查看所有系统变量
# 查看所有的全局变量 SHOW GLOBAL VARIABLES;
# 查看所有会话变量 SHOW SESSION VARIABLES; SHOW VARIABLES; # 不指定变量的级别,默认为会话级别
查询部分系统变量
# 查看部分的全局变量 SHOW GLOBAL VARIABLES LIKE 'admin_%';
# 查看部分会话变量 SHOW SESSION VARIABLES LIKE 'character_%'; SHOW VARIABLES LIKE 'character_%';
查看指定系统变量
作为 MySQL 编码规范,MySQL 中的系统变量以 两个“@” 开头,其中 “@@global”仅用于标记全局系统变量 ,“@@session”仅用于标记会话系统变量。 “@@” 首先标记会话系统变量,如果会话系统变量不存在,则标记全局系统变量,即先在会话系统变量中进行查找,查找不到,再到全局系统变量中查找。
# 查看指定全局系统变量 SELECT @@global.max_connections;
# 查看指定会话系统变量 SELECT @@session.character_set_client;
# 查看指定系统变量 # 先查询会话系统变量,在查询全局系统变量 SELECT @@character_set_client;
1.1.3 修改系统变量
方式一:修改配置文件
修改MySQL 配置文件 ,继而修改MySQL系统变量的值(该方法需要重启MySQL服务) 。
方式二:使用 set 设置系统变量的值
在MySQL服务运行期间,使用“set”命令重新设置系统变量的值。
#修改全局系统变量 #方式1: SET @@global.变量名=变量值; #方式2: SET GLOBAL 变量名=变量值;
#修改会话系统变量 #方式1: SET @@session.变量名=变量值; #方式2: SET SESSION 变量名=变量值;
# 修改全局系统变量 SET @@global.max_connections = 100; SET GLOBAL max_connections = 100; SELECT @@max_connections;
这个修改全局系统变量针对于当前MySQL服务器实例有效,重启MySQL服务,就失效了。
# 修改会话系统变量 SET @@session.character_set_client = 'gbk'; SET SESSION character_set_client = 'gbk'; SELECT @@character_set_client, @@global.character_set_client;
这个修改会话系统变量针对于当前会话有效,结束会话重新建立新的会话,就失效了。
1.2 用户变量
1.2.1 用户变量的分类
用户变量是用户自己定义的,作为 MySQL 编码规范,MySQL 中的用户变量以 一个“@” 开头。
根据作用范围不同,用户变量分为 会话用户变量 和 局部变量 。
- 会话用户变量:作用域和会话系统变量一样,只在 当前连接会话有效。
- 局部变量:只在 BEGIN 和 END 语句块中有效。局部变量只能在 存储过程和函数 中使用。
由于会话用户变量与会话系统变量的作用域一样,所以需要进行区分,会话系统变量使用@@,而用户会话变量使用@,因为局部变量只在 BEGIN 和 END 语句块中有效,可以不用使用@与会话系统变量进行区分。
1.2.2 会话用户变量
变量的声明与赋值
#方式1:“=”或“:=” # 使用 := 为变量赋值,可以与WHERE中等于条件判断进行区分 SET @用户变量 = 值; SET @用户变量 := 值; #方式2:“:=” 或 INTO关键字 SELECT @用户变量 := 表达式 [FROM 等子句]; SELECT 表达式 INTO @用户变量 [FROM 等子句];
变量的使用
SELECT @用户变量
代码示例
SET @num1 = 1; SET @num2 := 2; SET @sum := @num1 + @num2; # 查询不存在的用户变量返回NULL SELECT @sum, @a;
SELECT @count := COUNT(*) FROM employees; SELECT @count;
SELECT AVG(salary) INTO @avg_sal FROM employees; SELECT @avg_sal;
1.2.3 局部变量
可以使用 DECLARE 语句定义一个局部变量,局部变量仅仅在定义它的 BEGIN … END 中有效,使用 DECLARE 语句定义一个局部变量只能放在 BEGIN … END 中,而且只能放在第一句。
局部变量必须使用 DECLARE声明。
局部变量的声明
DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句,初始值为NULL
局部变量的赋值
方式1:一般用于赋简单的值
SET 局部变量名=值; SET 局部变量名:=值;
方式2:一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 局部变量名 FROM 表;
使用局部变量
SELECT 局部变量名;
代码示例
CREATE PROCEDURE test1() BEGIN # 声明局部变量 DECLARE a INT DEFAULT 0; # 默认值为0 DECLARE b INT; # 默认值为NULL # 两个变量如果类型和默认值一样可以合并声明 DECLARE c, d INT DEFAULT 1; DECLARE emp_name VARCHAR(25); # 赋值 SET a = 100; SET c := 200; SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101; # 使用局部变量 SELECT a, b, c, d, emp_name; END; CALL test1();
1.2.4 练习
声明局部变量,并分别赋值为employees表中employee_id为102的last_name和salary
# 声明局部变量,并分别赋值为employees表中employee_id为102的last_name和salary CREATE PROCEDURE test2() BEGIN # 声明局部变量 DECLARE emp_name VARCHAR(25); DECLARE sal DOUBLE(10, 2) DEFAULT 0; # 为局部变量赋值 SELECT last_name, salary INTO emp_name, sal FROM employees WHERE employee_id = 102; # 使用局部变量 SELECT emp_name, sal; END; CALL test2();
声明两个变量,求和并打印 (分别使用会话用户变量、局部变量的方式实现)
SET @num1 := 10; SET @num2 := 20; SET @sum := @num1 + @num2; SELECT @sum;
CREATE PROCEDURE test3() BEGIN DECLARE num1, num2, sum INT DEFAULT 0; SET num1 := 10; SET num2 := 20; SET sum := num1 + num2; SELECT sum; END; CALL test3();
创建存储过程“different_salary”查询某员工和他领导的薪资差距,并用IN参数emp_id接收员工id,用OUT参数dif_salary输出薪资差距结果。
# 创建存储过程“different_salary”查询某员工和他领导的薪资差距,并用IN参数emp_id接收员工 # id,用OUT参数dif_salary输出薪资差距结果。 CREATE PROCEDURE different_salary( IN emp_id INT, OUT dif_salary DOUBLE(10, 2)) BEGIN # 声明局部变量,保存员工工资和管理者工资 DECLARE emp_sal, mgr_sal DOUBLE(10, 2) DEFAULT 0.0; # 查询赋值 # 查询员工工资 SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; # 查询管理者工资 SELECT salary INTO mgr_sal FROM employees WHERE employee_id = ( SELECT manager_id FROM employees WHERE employee_id = emp_id ); # 为输出参数赋值 SET dif_salary := mgr_sal - emp_sal; END; CALL different_salary(102, @dif_sal); SELECT @dif_sal;
1.2.5 对比会话用户变量与局部变量