一、变量
在MySQL的存储过程与函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。它可以分为用户自定义变量与系统变量
1、系统变量
1）系统变量分为全局变量（需要使用关键字global）和会话系统变量（需要使用关键字session），如果没有声明global，那默认是session级别的变量
- 全局系统变量对所有会话（连接）都有效，但是
重启后就会重置（但是在MySQL8.0中，可以使用关键字PERSIST进行持久化，也就是SET GLOBAL 变量名 = 变量值;的形式，即使MySQL服务器重启，这个全局变量也不会变回去） - 会话系统变量仅针对当前会话（连接）有效。会话期间，当前会话对某个会话系统变量的修改，并不会影响其他会话中同一会话系统变量的值。而且会话重新连接之后，变量值也会重置
- 会话1修改了某个全局系统变量会影响到会话2中同一全局系统变量
2）MySQL中有些变量只能是全局系统变量，例如max_connection用于限制服务器的最大连接数
有些系统变量既可以是全局的又可以是会话级别的，如character_client_set用于设置客户端的字符集
有些系统变量只能是会话级别的，例如pseudo_thread_id用于标记当前会话的MySQL连接id
3）查看系统变量
# 查看所有全局变量
SHOW GLOBAL VARIABLES;
# 查看所有会话变量
SHOW SESSION VARIABLES;
或者
SHOW VARIABLES;
# 查看满足条件的部分全局系统变量
SHOW GLOBAL VARIABLES LIKE '%标识符%';
# 查看满足条件的部分会话系统变量
SHOW SESSION VARIABLES LIKE '%标识符%';
或者
SHOW VARIABLES LIKE '%标识符%';
# 查看指定全局系统变量
SELECT @@global.变量名;
# 查看指定会话系统变量
SELECT @@session.变量名;
或者
SELECT @@变量名; #不声明全局还是会话级别的情况下，会先去会话系统变量中找，如果没有再去全局系统变量中找
4）修改系统变量
- 方式一：修改MySQL的配置文件，但是需要重启服务
- 方式二：使用set命令重新设置系统变量的值
# 修改全局系统变量值
SET @@global.变量名 = 变量值;
或者
SET GLOBAL 变量名 = 变量值;
# 修改会话系统变量值
SET @@session.变量名 = 变量值;
或者
SET SESSION 变量名 = 变量值;
2、用户变量
1）用户变量是用户自己定义的，MySQL中用户变量以@开头进行定义。根据作用范围的不同分为会话用户变量和局部变量
- 会话用户变量：作用域和会话变量一样，只对当前连接会话有效。注意和会话系统变量区分开
- 局部变量：只在
BEGIN和END语句块中有效，只能在存储过程和函数中使用
2）会话用户变量的使用
声明和赋值
# 方式一：使用=或者:=
SET @用户变量 = 值;
SET @用户变量 := 值;
# 方式二：使用:=或者INTO关键字，就相当于在查询语句中插入一段用户变量的声明，方式二不能使用=
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
#例如
SELECT @A1 := COUNT(*) FROM t_test;
SELECT AVG(salary) INTO @A2 FROM t_employee;
查询
SELECT @用户变量;
3）局部变量
- 相关关键字：使用关键字
DECLARE进行定义，使用SET进行赋值，使用SELECT进行查询 - 作用域：仅在定义它的
BEGIN...END之间有效，即存储过程、函数中 - 位置：只能放在
BEGIN...END中，且只能放在第一句
定义变量，如果不声明默认值，那么初始值为null
DECLARE 变量名 类型 [default值];
例如
DECLARE v1 INT DEFAULT 100;
变量赋值
# 方式一：使用=或者:=
SET 局部变量 = 值;
SET 局部变量 := 值;
# 方式二：使用INTO关键字，就相当于在查询语句中插入一段用户变量的声明
SELECT 字段或表达式 INTO 局部变量 FROM 表;
#例如
SELECT AVG(salary) INTO A2 FROM t_employee;
变量的使用
SELECT 局部变量名;
它是怎么在存储过程、函数中进行使用的呢？
DELIMITER $
CREATE PROCEDURE test_var()
BEGIN
# 变量声明，如果a，b默认值都是0，可以使用DECLARE a,b INT DEFAULT 0来声明
DECLARE a INT DEFAULT 0;
DECLARE b INT;
DECLARE c VARCHAR(25);
# 变量赋值
SET a = 1;
SET b := 2;
SELECT emp_name INTO c FROM t_emp WHERE emp_id = 101;
# 变量使用
SELECT a,b,c;
END $
DELIMITER ;
# 调用存储过程
CALL test_var();
4）用户变量（包括会话用户变量、局部变量）和系统变量的区别
- 声明方面：
- 用户变量：
- 会话用户变量的声明和使用需要带一个@
- 局部变量不需要带@，但是
局部变量的定义需要使用关键字DECLAER
- 系统变量如果使用时带@符号需要带2个
- 用户变量：
- 定义位置：
- 会话用户变量可以定义在任何地方
- 局部变量只能放在
BEGIN...END中，且只能放在第一句
- 作用域：
- 会话用户变量：当前会话
- 局部变量：定义它的
BEGIN...END中
二、定义条件与处理程序
定义条件：指事先定义程序执行过程中可能遇到的问题
处理程序：定义了在遇到问题之后的处理方式，保证了存储过程或者存储函数在遇到警告或者错误时能继续执行，增强了其处理问题的能力，避免程序因异常停止运行
定义条件和处理程序在存储函数、存储过程中都是支持的
1、定义条件
假设我们有一个学生表t_student
它有2个字段，stu_id和stu_name，其中stu_id在声明的时候就加了非空约束
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`stu_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生id',
`stu_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生姓名'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `t_student` VALUES ('001', 'jack');
INSERT INTO `t_student` VALUES ('002', 'rose');
按照我们的认知，加了非空限制的字段不管是更新还是新增，都不应该支持null做值
但是，我测试的时候发现了一个很神奇的现象
这里的stu_id被我更新成null了！！
后来排查一番发现是我的MySQL数据库缺少STRICT_TRANS_TABLES（严格模式）配置
改完再重启mysql服务就好了
言归正传，我们声明一个存储过程，将t_student中的stu_id更新为null
DELIMITER //
CREATE PROCEDURE testExecption()
BEGIN
SET @X = 1;
UPDATE t_student set stu_id = NULL where stu_name = 'jack';
SET @X = 2;
UPDATE t_student set stu_id = '001' where stu_name = 'jack';
SET @X = 3;
END //
DELIMITER ;
# 调用存储过程
call testExecption();
# 查询变量X
select @X;
调用存储过程，发现报错如下
这里的1048和23000就是错误码
1048是MySQL_error_code，他是数值类型错误代码
23000是sqlstate_value，它是长度为5的字符串类型错误代码
1）定义条件就是给MySQL中错误码命名。它将一个错误名字和指定的错误条件进行关联
这个名字可以随后被用在定义处理程序的DECALRE HANDLER语句中，
定义条件不是必要的，只有在处理程序中需要用到的时候才要去定义
定义条件使用DECLARE关键字，语法为
DECLARE 错误名称 CONDITION FOR 错误码(或者错误条件);
举例：我们现在要使用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、定义处理程序
1）定义语法
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;
处理方式：
CONTINUE：遇到指定的错误类型不处理，继续往下执行EXIT：遇到指定的错误类型马上退出UNDO：遇到指定的错误类型撤回之前的操作，MySQL中暂时不支持
错误类型：
MySQL_error_codesqlstate_value错误名称：上面定义条件里声明的错误名称SQLWARNING：匹配所有01开头的sqlstate错误码NOT FOUND：匹配所有02开头的sqlstate错误码SQLEXCEPTION：匹配所有没被SQLWARNING和NOT FOUND捕获的sqlstate错误码
处理语句：
- 可以是
SET 变量 = 值这样的简单语句 - 也可以是
BEGIN...END编写的符合语句
接下来，我们看下如何使用上面说的6种错误类型来定义处理程序
# 捕获MySQL_error_code
DECLARE CONTINUE HANDLER FOR 1048 SET @result = 'Column stu_id cannot be null';
# 捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @result = 'Column stu_id cannot be null';
# 先定义条件，再调用错误类型
DECLARE Field_Not_Be_Null CONDITION FOR 1048;
DECLARE CONTINUE HANDLER FOR Field_Not_Be_Null SET @result = 'Column stu_id cannot be null';
# 使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @result = 'Column stu_id cannot be null';
# 使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @result = 'Column stu_id cannot be null';
# 使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @result = 'Column stu_id cannot be null';
2）案例处理
掌握了定义条件与处理程序之后
我们怎么处理上面更新t_student中stu_id为null时执行报错的问题呢
DROP PROCEDURE testExecption;
# 重新定义存储过程，体现错误的处理程序
DELIMITER //
CREATE PROCEDURE testExecption()
BEGIN
# 在开始就声明处理程序
# 方式一：如果是使用错误名称，需要先定义条件
# DECLARE Field_Not_Be_Null CONDITION FOR 1048;
# DECLARE CONTINUE HANDLER FOR Field_Not_Be_Null SET @result = 'Column stu_id cannot be null';
# 方式二：捕获sqlstate_value
# DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @result = 'Column stu_id cannot be null';
# 方式三：捕获MySQL_error_code
DECLARE CONTINUE HANDLER FOR 1048 SET @result = 'Column stu_id cannot be null';
SET @X = 1;
UPDATE t_student set stu_id = NULL where stu_name = 'jack';
SET @X = 2;
UPDATE t_student set stu_id = '001' where stu_name = 'jack';
SET @X = 3;
END //
DELIMITER ;
# 调用存储过程
call testExecption();
# 查询变量X和result
select @X,@result;
执行结果如下
如有错误，欢迎指正！！！
1579
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
