【MySQL】变量、流程控制、游标、触发器

1、变量

变量分为系统变量以及用户自定义变量

1.1、系统变量

系统变量分为全局系统变量以及会话系统变量，

• 全局系统变量针对于所有会话有效，但不能跨重启
• 会话系统变量仅针对于当前会话有效。
• 不同会话对于全局系统变量值的修改可能会导致另一个会话中全局系统变量的修改。

# 查询全局系统变量
SHOW GLOBAL VARIABLES;
# 查询会话系统变量
SHOW SESSION VARIABLES;
# 默认查询会话系统变量
SHOW VARIABLES;

# 正则匹配方式
SHOW GLOBAL VARIABLES LIKE `admin_%`;

查看指定系统变量

SELECT @@global.变量名;
SELECT @@session.变量名;

修改系统变量

# 针对当前的数据库实例是有效的，一旦重启mysql服务就失效了。
# 全局系统变量方式一：
SET @@golbal.max_connecions = 1;
# 全局系统变量方式二：
SET GLOBAL max_connections = 1;

# 针对当前会话，建立新的会话后失效。
# 会话系统变量方式一：
SET @@session.character_set_client = 1;
# 会话系统变量方式二：
SET SESSION character_set_client = 1;

1.2、用户变量

会话用户变量 vs 局部变量

1.2.1、会话用户变量

使用@开头，作用于为当前对话

# 变量定义
SET @用户变量 := 值;
SET @用户变量 = 值;
SELECT @用户变量 := 表达式 # 表达式为FROM等子句

# 查看变量
SELECT @用户变量

1.2.2、局部变量

只能使用在存储过程和存储函数中

• 使用DECLARE声明
• 声明并使用在BEGIN…END中
• 声明在BEGIN…END的首行

#定义
DECLARE myparam INT;
# 赋值
SET 变量名 := 值;                 # 方式一
SELECT 表达式 INTO 变量名 FROM 表  # 方式二

2、流程控制

2.1、分支结构

IF

IF 表达式1 THEN 操作1
ELSEIF 表达式2 THEN 操作2
ELSE 表达式3 THEN 操作3
END IF

2.2、循环结构

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;

WHILE

[while_label]:WHILE 循环条件 DO
	循环语句
END WHILE [while_label]

REPEAT

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

LEAVE

可以看做为break

LEAVE 标记名_label

ITERATE

可以看做是continue

ITERATE label

3、游标

游标可以对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构

声明游标

# MySQL、SQL Server、DB2、MariaDB中
DECLARE cursor_name CURSOR FOR select_statement
# Oracle、PostgreSQL
DECLARE cursor_name CURSOR IS select_statement

打开游标

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

OPEN cursor_name

使用游标

游标查询结果集中的字段数，必须与INTO后面的变量数相同，否则会报错。

FETCH cursor_name INTO var_name [, var_name]...
# 记录有多少列，var_name就有多少个

关闭游标

游标会占据系统资源，不关闭的话会一直保持到存储过程结束。

CLOSE cursor_name

4、触发器

创建触发器

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

删除触发器

DROP TRIGGER IF  EXISTS 触发器名称;

查看触发器

# 查看当前数据库所有触发器的定义
SHOW TRIGGERS;
SHOW TRIGGERS\G # 格式好看一点
# 查看当前数据库某个触发器的定义
SHOW CREATE TRIGGER 触发器名
# 从系统库information_schema的TRIGGERS表中查询
SELECT * FROM information_schema.TRIGGERS;