变量、流程控制与游标

1. 变量

1.1 系统变量

1.1.1 系统变量分类

属于 服务器 层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件（例如my.ini等）中的参数值。

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

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

全局系统变量针对于所有会话（连接）有效，但不能 跨重启
会话系统变量仅针对于当前会话（连接）有效。

有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；有些既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；有些只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID。

1.1.2 查看系统变量

查看所有或部分系统变量

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

查看指定系统变量

#  @@global. 查看指定的系统变量的值
SELECT @@global.变量名;
# @@session.查看指定的会话变量的值
SELECT @@session.变量名;
# @@ 首先标记会话系统变量，不存在则标记全局系统变量
SELECT @@变量名;

修改系统变量的值

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

1.2 用户变量

1.2.1 用户变量分类

以一个 @ 开头,又分为会话用户变量和局部变量。

1.2.2 会话用户变量

作用域和会话变量一样，只对 当前连接 会话有效。

定义

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

查看用户变量的值

SELECT @用户变量

查看某个未声明的变量时，将得到NULL值

1.2.3 局部变量

以使用 DECLARE 语句定义一个局部变量作用域：仅仅在定义它的 BEGIN ... END 中有效位置：只能放在 BEGIN ... END 中，而且只能放在第一句

BEGIN
    #声明局部变量
    DECLARE 变量名1,变量名2... 变量数据类型 [DEFAULT 变量默认值];
    #为局部变量赋值
    SET 变量名1 = 值;
    SELECT 值 INTO 变量名2 [FROM 子句];
    #查看局部变量的值
    SELECT 变量1,变量2,变量3;
END

定义变量

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

变量赋值与会话用户变量相同
使用变量

SELECT 局部变量名

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

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

2. 定义条件与处理程序

定义条件 是事先定义程序执行过程中可能遇到的问题， 处理程序 定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。类似 JAVA 的 try catch 这样可以增强存储程序处理问题的能力，避免程序异常停止运行。说明：定义条件和处理程序在存储过程、存储函数中都是支持的。

2.1 案例分析

创建一个名称为“UpdateDataNoCondition”的存储过程。代码如下：

DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
    BEGIN
        SET @x = 1;
        UPDATE employees SET email = NULL WHERE last_name = 'Abel';
        SET @x = 2;
        UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
        SET @x = 3;
    END //
DELIMITER ;

CALL UpdateDataNoCondition(); # ERROR 1048 (23000): Column 'email' cannot be null

SELECT @x; # 1

2.2 定义条件

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

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

错误码的说明：

MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。
- MySQL_error_code是数值类型错误代码。
- sqlstate_value是长度为5的字符串类型错误代码。
例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000'是sqlstate_value。
例如，在ERROR 1142（42000）中，1142是MySQL_error_code，'42000'是sqlstate_value。

举例1：定义 Field_Not_Be_NULL 错误名与MySQL中违反非空约束的错误类型是 ERROR 1048 (23000) 对应。

#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

2.3 定义处理程序

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

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

处理方式：3个取值
- CONTINUE ：遇到错误不处理，继续执行。
- EXIT ：马上退出。
- UNDO ：撤回之前的操作。MySQL暂不支持。
错误类型（即条件）可以有如下取值：
- SQLSTATE ：长度为5的sqlstate_value类型的错误代码；
- MySQL_error_code ：匹配数值类型错误代码；
- 错误名称 ：DECLARE 错误条件名称。
- SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码；
- NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码；
- SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像 SET 变量 = 值 这样的简单语句，也可以是使用 BEGIN ... END 编写的复合语句。

定义处理程序的几种方式，代码如下：

#方法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';

2.4 案例解决

承接 2.1 的案例分析

DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
    BEGIN
        #定义处理程序
        DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;

        SET @x = 1;
        UPDATE employees SET email = NULL WHERE last_name = 'Abel';
        SET @x = 2;
        UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
        SET @x = 3;
    END //
DELIMITER ;

CALL UpdateDataWithCondition();  # CALL UpdateDataWithCondition();

SELECT @x,@proc_value; # 3 -1

3. 流程控制

流程三大类：顺序结构、分支结构、循环结构

针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。

条件判断语句：IF 语句和 CASE 语句
循环语句：LOOP、WHILE 和 REPEAT 语句
跳转语句：ITERATE 和 LEAVE 语句

3.1 分支结构之 IF

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

3.2 分支结构之 CASE

#情况一：类似于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后面不需要）

3.3 循环结构之LOOP

# loop_label表示LOOP语句的标注名称，该参数可以省略。
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]

举例：

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;

3.4 循环结构之WHILE

[while_label:] WHILE 循环条件 DO
    循环体
END WHILE [while_label];

3.5 循环结构之REPEAT

REPEAT 首先会执行一次循环，然后在 UNTIL 中进行表达式的判断

[repeat_label:] REPEAT
    循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

3.6 对比三种循环

这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。
LOOP：一般用于实现简单的"死"循环 WHILE：先判断后执行 REPEAT：先执行后判断，无条件至少执行一次

3.7 跳转语句之LEAVE语句

用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。可理解为 break。

LEAVE 标记名

举例：

DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
    begin_label: BEGIN
        IF num<=0
            THEN LEAVE begin_label;
        ELSEIF num=1
            THEN SELECT AVG(salary) FROM employees;
        ELSEIF num=2
            THEN SELECT MIN(salary) FROM employees;
        ELSE
            SELECT MAX(salary) FROM employees;
        END IF;
        SELECT COUNT(*) FROM employees;
    END //
DELIMITER ;

3.8 跳转语句之ITERATE语句

用在循环语句内，表示重新开始循环，将执行顺序转到语句段开头处。可以理解为 continue

ITERATE label

4. 游标

4.1 什么是游标(光标)

游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位并操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。

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

4.2 使用游标步骤

第一步，声明游标

DECLARE cursor_name CURSOR FOR select_statement;

select_statement 代表的是 SELECT 语句，返回一个用于创建游标的结果集。比如：

DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;

第二步，打开游标

打开游标时 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的 逐条读取 结果集中的记录做准备。

OPEN cursor_name

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

FETCH cursor_name INTO var_name [, var_name] ...

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

注意： var_name必须在声明游标之前就定义好。游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致

第四步，关闭游标

CLOSE cursor_name

游标占用系统资源，不及时关闭会一直保持到存储过程结束，影响系统运行的效率。

4.3 举例

实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数

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 ;

4.5 小结

游标为 逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。性能问题:使用过程中，会对数据行进行 加锁 ，并发量大的时候，不仅会影响业务之间的效率，还会 消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理。

切记：用完之后就关闭！！！！！

补充：MySQL 8.0的新特性—全局变量的持久化

# 临时生效, 数据库重启后，服务器从配置文件中读取变量的默认值。
SET GLOBAL MAX_EXECUTION_TIME=2000;

MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。

SET PERSIST global max_connections = 1000;

触发器

1. 触发器概述

商品信息和库存信息，每添加一条商品信息则需要同时添加库存信息。
触发器由事件触发某个操作，事件包括INSERT等

2. 触发器创建

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 触发器监控的表名
FOR EACH ROW
触发器执行的语句块;

例子

例一
- 在向t1插入一条记录之前向 t2 的插入一条记录
- 触发器执行的语句块若非单条SQL语句需用 BEGIN END 围起

CREATE TRIGGER before_insert
BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
INSERT INTO t2
VALUES('lfd');
END

例二
- 添加一个若新来的员工比ta领导工资高就报错的触发器
- DECLARE 声明领导的工资
- NEW 为新插入的记录，即新员工的记录。用ta的领导的id与 employees 表的员工id匹配出ta领导并获取工资注入声明的 mgrsalary
- 删除的则为 OLD
- 如果高则报错

CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
DECLARE mgrsalary DOUBLE;
SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
IF NEW.salary > mgrsalary THEN
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
END IF;
END

3. 查看删除触发器

3.1 查看

SHOW TRIGGERS; # 查看当前库所有触发器
SHOW CREATE TRIGGER 触发器名; # 查看指定触发器
SELECT * FROM information_schema.TRIGGERS; 从系统库的 TRIGGER表中查询所有库的 触发器

3.2 删除

DROP TRIGGER IF EXISTS 触发器名称;

4. 优缺点

4.1 优缺点

保证数据完整性
帮助我们记录操作日志
操作数据前检查数据合法性

4.2 缺点

可读性差
相关数据变更可能导致触发器出错

4.3 注意点

只有直接对表进行操作才会激活触发器例子：子表定义了外键约束外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句修改或删除父表被引用的记录时，也会引起子表的变动但不会激活触发器

基础篇(7) 变量、流程控制与游标 、触发器