变量

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

系统变量

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

系统变量分类

系统变量分为全局系统变量（需要添加 global 关键字）以及会话系统变量（需要添加 session 关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为 local 变量。如果不写，默认会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。

每一个 MySQL 客户机成功连接 MySQL 服务器后，都会产生与之对应的会话。会话期间，MySQL 服务实例会在 MySQL 服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制。如下图：

  • 全局系统变量针对于所有会话（连接）有效，但不能跨重启
  • 会话 1 对某个全局系统变量值的修改会导致会话 2 中同一个全局系统变量值的修改

查看系统变量

查看所有或部分系统变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查看所有全局变量
SHOW GLOBAL VARIABLES;

-- 查看所有会话变量
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;

-- 查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
-- 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';

-- 如
SHOW GLOBAL VARIABLES LIKE 'admin_%';

查看指定系统变量

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

1
2
3
4
5
6
7
-- 查看指定的系统变量的值
SELECT @@global.变量名;

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

修改系统变量的值

  • 方式1：修改 MySQL 配置文件 ，继而修改 MySQL 系统变量的值（该方法需要重启 MySQL 服务）
  • 方式2：在 MySQL 服务运行期间，使用 set 命令重新设置系统变量的值 
1
2
3
4
5
6
7
8
9
10
11
-- 为某个系统变量赋值
-- 方式1：
SET @@global.变量名=变量值;
-- 方式2：
SET GLOBAL 变量名=变量值;

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

用户变量

用户变量分类

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

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

会话用户变量

变量的定义

1
2
3
4
5
6
7
-- 方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;

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

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

1
SELECT @用户变量

局部变量

  • 定义：可以使用 DECLARE 语句定义一个局部变量
  • 作用域：仅仅在定义它的 BEGIN ... END 中有效
  • 位置：只能放在 BEGIN ... END 中，而且只能放在第一句 
1
2
3
4
5
6
7
8
9
10
11
12
BEGIN
    -- 声明局部变量
    DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
    DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];

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

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

定义变量

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

变量赋值 

1
2
3
4
5
6
-- 一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;

-- 一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;

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

1
SELECT 局部变量名;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
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 ;

2. 声明两个变量，求和并打印 （分别使用会话用户变量、局部变量的方式实现） 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 方式1：使用用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

-- 方式2：使用局部变量
DELIMITER //
CREATE PROCEDURE add_value()
BEGIN
	-- 局部变量
    DECLARE m INT DEFAULT 1;
    DECLARE n INT DEFAULT 3;
    DECLARE SUM INT;
    SET SUM = m+n;
    SELECT SUM;
END //
DELIMITER ;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 声明
DELIMITER //

CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE)
BEGIN
	-- 声明局部变量
    DECLARE emp_sal,mgr_sal DOUBLE DEFAULT 0.0;
    DECLARE mgr_id INT;
    SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
    SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = emp_id;
END //

DELIMITER ;

-- 调用
SET @emp_id = 102;
CALL different_salary(@emp_id,@diff_sal);

-- 查看
SELECT @diff_sal;

用户变量和局部变量对比

作用域 定义位置 语法
会话用户变量 当前会话 会话的任何地方 加@符号，不用指定类型
局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型

定义条件

定义条件就是给 MySQL 中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。 

1
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

MySQL_error_code 和 sqlstate_value 都可以表示 MySQL 的错误。

  • MySQL_error_code 是数值类型错误代码

  • sqlstate_value 是长度为 5 的字符串类型错误代码。

定义处理程序

可以为 SQL 执行过程中发生的某种类型的错误定义特殊的处理程序 

1
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

处理方式：处理方式有 3 个取值：CONTINUE、EXIT、UNDO

  • CONTINUE ：表示遇到错误不处理，继续执行
  • EXIT ：表示遇到错误马上退出
  • UNDO ：表示遇到错误后撤回之前的操作。MySQL 中暂时不支持这样的操作

错误类型（即条件）可以有如下取值：

  • SQLSTATE ‘字符串错误码’ ：表示长度为 5 的 sqlstate_value 类型的错误代码

  • MySQL_error_code ：匹配数值类型错误代码；

  • 错误名称 ：表示 DECLARE … CONDITION 定义的错误条件

  • SQLWARNING ：匹配所有以 01 开头的 SQLSTATE 错误代码

  • NOT FOUND ：匹配所有以 02 开头的 SQLSTATE 错误代码；

  • SQLEXCEPTION ：匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码；

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是 像“ SET 变量 = 值 ”这样的简单语句，也可以是使用 BEGIN … END 编写的复合语句。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

-- 方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

-- 方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';

-- 方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

-- 方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

-- 方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

流程控制

分支结构 IF

1
2
3
4
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF

分支结构 CASE 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

-- 情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

循环结构 LOOP 

1
2
3
4
-- 其中，loop_label表示LOOP语句的标注名称，该参数可以省略。
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]
1
2
3
4
5
6
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id +1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop;

循环结构 WHILE 

1
2
3
4
-- while_label为WHILE语句的标注名称；如果循环条件结果为真，WHILE语句内的语句或语句群被执行，直至循环条件为假，退出循环。
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];

循环结构 REPEAT

REPEAT 语句创建一个带条件判断的循环过程。与 WHILE 循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。 

1
2
3
4
5
-- repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至expr_condition为真。
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

跳转结构 LEAVE

LEAVE 语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出 程序体的操作 （类似 break）

1
LEAVE 标记名

跳转结构 ITERATE

ITERATE 语句：只能用在循环语句（LOOP、REPEAT 和 WHILE 语句）内，表示重新开始循环，将执行顺序 转到语句段开头处。(类似 continue)

1
ITERATE label

游标

在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当指针的作用 ，可以通过操作游标来对数据行进行操作。MySQL 中游标可以在存储过程和函数中使用。

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。 如果我们想要使用游标，一般需要经历四个步骤。

第一步，声明游标 

1
DECLARE cursor_name CURSOR FOR select_statement;

第二步，打开游标 

1
OPEN cursor_name

当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的 逐条读取 结果集中的记录做准备。

第三步，使用游标（从游标中取得数据） 

1
FETCH cursor_name INTO var_name [, var_name] ...

使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游 标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。

注意：var_name 必须在声明游标之前就定义好。

第四步，关闭游标 

1
CLOSE cursor_name

案例

创建存储过程 get_count_by_limit_total_salary()，声明 IN 参数 limit_total_salary，DOUBLE 类型；声明 OUT 参数 total_count，INT 类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到 limit_total_salary 参数的值，返回累加的人数给 total_count。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
    DECLARE sum_salary DOUBLE DEFAULT 0;-- 记录累加的总工资
    DECLARE cursor_salary DOUBLE DEFAULT 0;-- 记录某一个工资值
    DECLARE emp_count INT DEFAULT 0;-- 记录循环个数
    -- 定义游标
    DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
    -- 打开游标
    OPEN emp_cursor;
    REPEAT
        -- 使用游标（从游标中获取数据）
        FETCH emp_cursor INTO cursor_salary;
        SET sum_salary = sum_salary + cursor_salary;
        SET emp_count = emp_count + 1;
        UNTIL sum_salary >= limit_total_salary
	END REPEAT;
	
    SET total_count = emp_count;
    -- 关闭游标
    CLOSE emp_cursor;
    
END //

DELIMITER ;