文章目录

• 1. 变量
• • 1.1 系统变量
  • • 1.1.1 系统变量分类
    • 1.1.2 查看系统变量
    • • 查看所有系统变量
      • 查询部分系统变量
      • 查看指定系统变量
    • 1.1.3 修改系统变量
    • • 方式一：修改配置文件
      • 方式二：使用 set 设置系统变量的值
  • 1.2 用户变量
  • • 1.2.1 用户变量的分类
    • 1.2.2 会话用户变量
    • • 变量的声明与赋值
      • 变量的使用
      • 代码示例
    • 1.2.3 局部变量
    • • 局部变量的声明
      • 局部变量的赋值
      • 使用局部变量
      • 代码示例
    • 1.2.4 练习
    • 1.2.5 对比会话用户变量与局部变量

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; # 默认值为0DECLARE 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_nameFROM employeesWHERE 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, salFROM employeesWHERE 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()
BEGINDECLARE 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_salFROM employeesWHERE employee_id = emp_id;# 查询管理者工资SELECT salary INTO mgr_salFROM employeesWHERE employee_id = (SELECT manager_idFROM employeesWHERE employee_id = emp_id);# 为输出参数赋值SET dif_salary := mgr_sal - emp_sal;
END;CALL different_salary(102, @dif_sal);
SELECT @dif_sal;

1.2.5 对比会话用户变量与局部变量