MySQL学习（七）：变量、流程控制、游标和触发器以及MySQL8新特性

阅读数: 次 2024-10-17

1. 变量、流程控制、游标

1.1 系统变量：数据库运行的基石

MySQL中的系统变量控制着数据库的运行行为。它们分为全局变量和会话变量

全局系统变量（@@global）：影响整个MySQL服务器实例。
- 查看全局变量：
  1
  SHOW GLOBAL VARIABLES;

会话系统变量（@@session）：影响当前数据库会话。
- 查看会话变量
  1
  SHOW VARIABLES;
  或
  1
  SHOW SESSION VARIABLES;
变量查找顺序：如果仅写@@，MySQL会首先查找会话变量，如果没有找到，再查找全局变量。

1.2 修改系统变量值

系统变量的值可以通过SET语句修改，但修改全局变量的值可能需要管理员权限

1	SET @@'global.session.变量名' = '修改的值';

注意：修改会话系统变量只对当前会话有效，而修改全局系统变量的值对本次服务有效。

1.3 用户变量：灵活的数据存储

用户变量在会话级别使用，分为会话用户变量和局部用户变量

会话用户变量（@）

设置变量

1	SET @用户变量 = '值';

或

1	SET @用户变量 := '值';

从查询中赋值

1	SELECT 查询字段 INTO @用户变量 FROM 表;

局部用户变量

在存储函数或过程中声明

1	DECLARE 变量名类型 [DEFAULT 值];

赋值
1
SET 变量名 = '值';

从查询中赋值

1	SELECT 查询字段 INTO 变量名 FROM 表;

1.4 异常处理：程序的稳健之盾

在存储过程中，异常处理是确保程序稳健性的关键

1	DECLARE '异常名' CONDITION FOR (sqlstate) 错误码;

处理异常

1	DECLARE '变量' HANDLER FOR '错误名'(sqlwarning\|sqlstate\|数值\|自定义名 not found\|sqlexception) SET @info = '错误信息';

continue：继续执行后续语句。
exit：退出存储过程。
undo：撤销之前执行的语句。

1.5 流程控制：程序的灵魂

MySQL支持多种流程控制语句，用于根据条件执行不同的代码路径

分支结构

IF THEN - ELSE

IF 条件 THEN
    执行代码块1;
ELSEIF 条件 THEN
    执行代码块2;
ELSE
    执行代码块3;
END IF;

CASE WHEN THEN

CASE
    WHEN 条件1 THEN
        执行代码块1;
    WHEN 条件2 THEN
        执行代码块2;
    ELSE
        执行代码块3;
END CASE;

循环结构

LOOP

[标签]: LOOP
    循环体;
    LEAVE [标签]; -- 跳出循环
END LOOP [标签];

WHILE

1
2
3

WHILE 循环条件 DO
    循环体;
END WHILE;

REPEAT

[标签]: REPEAT
    循环体;
    UNTIL 条件; -- 循环直到条件为真
END REPEAT [标签];

跳转语句
- LEAVE：跳出标签指定的循环。
- ITERATE：相当于continue，跳过当前循环的剩余部分，进入下一次循环迭代。

1.6 游标：逐行处理数据

游标允许程序逐行遍历查询结果集，这在处理大量数据时非常有用

定义游标

1	DECLARE 游标名 CURSOR FOR 查询语句;

打开游标
1
OPEN 游标名;
获取数据
1
FETCH 游标名 INTO 变量名列表;
关闭游标
1
CLOSE 游标名;
缺点：游标操作可能会对数据库的并发性能产生影响，因为它需要锁定相关的数据行。

2. 触发器

2.1 触发器的核心功能

触发器，这一数据库中的隐秘守护，能够在预定义的事件发生时自动执行特定的操作。如：

数据变更：对表进行插入、更新或删除操作时。
数据库操作：如开启或提交事务。

==触发器的价值==

自动化业务逻辑：无需手动编写额外的应用程序代码，即可在数据变更时自动执行复杂的业务规则。
数据一致性保障：通过在数据变更前进行验证和约束，确保数据的一致性和完整性。
简化应用程序设计：将业务逻辑直接集成到数据库层面，减少应用程序的复杂性，提高维护性和可扩展性。

2.2 触发器的创建与定义

创建触发器的过程

CREATE TRIGGER `triggerName`
BEFORE|AFTER INSERT|UPDATE|DELETE ON `tableName`
FOR EACH ROW
BEGIN
    -- 触发器逻辑，使用`NEW`和`OLD`关键字引用行数据
    -- `NEW`：代表即将插入或更新的行
    -- `OLD`：代表被删除或即将被更新的行
    -- 定义触发器行为
END;

2.3 触发器的管理与查询

要管理和查询触发器，可以使用以下SQL命令

查看所有触发器
1
SHOW TRIGGERS;
查看特定触发器的详细信息
1
SHOW CREATE TRIGGER `triggerName`;
删除触发器
1
DROP TRIGGER `triggerName`;

3. MySQL 8.0新特性

3.1 窗口函数

MySQL 8.0的窗口函数为数据分析提供了强大的工具，它们能够对数据进行分组和排序，同时返回每个组的聚合值。

**ROW_NUMBER()**：为结果集中的每一行赋予一个独一无二的序号。
**RANK()**：为结果集中的每一行分配一个排名，并列行共享相同的排名。
**DENSE_RANK()**：与RANK()类似，但并列行后的排名会连续。

1 2	SELECT row_number() OVER (PARTITION BY 分组字段 ORDER BY 排序字段) AS row_num FROM 表名;

3.2 分布函数：数据分布的深度解析

分布函数帮助用户深入理解数据在不同维度的分布情况。

**PERCENT_RANK()**：计算每个分组中当前行的排名百分比，了解其在分组中的相对位置。
**CUME_DIST()**：计算当前行在分组中的累积分布比例。
**LAG()和LEAD()**：分别获取当前行之前或之后的行值，用于时间序列分析。

1 2	SELECT percent_rank() OVER (PARTITION BY 分组字段 ORDER BY 排序字段) AS percent_rank FROM 表名;

3.3 公用表表达式（CTE）：查询的模块化与递归

公用表表达式（CTE）允许定义一个临时结果集，并在查询中重复使用，提高了查询的可读性和维护性。

普通CTE：用于定义一个非递归的临时结果集。
递归CTE：支持定义递归查询，用于处理层次数据结构。

1 2	WITH CTE_NAME AS (SELECT 查询语句) SELECT * FROM CTE_NAME;