MySQL 变量的详细讲解
每博一文案
很喜欢席慕容写的一句话，挫折回来，也会去，热泪会流下，也会收起，没有什么
可以让我气馁的，因为我有着长长的一生。
人活着一世会发生什么，我们无从而知。
很多时候，我们以为的绝境，熬过去，会发现都是生活的常态。尽管生活总是让人感到绝望，
但我们可以选择兴高采烈的活着，人生最好的状态摸过于，前半生不拍，后半生不悔，即便
深处人生低谷，也不惧怕满山风雨。
世间的人和事，来和去，都有它的时间，我们只需要把自己修炼成最好的样子，然后静静的等待就好了。
人生是一场旅途，喜怒哀乐都有挫折。逆境是人生常态，关关难过，关关过，前路漫漫，一灿灿。
人生的旅程，会是宽阔平坦大道，也会是泥泞坎坷的羊肠小道，没有过不去的坎，我们走的每一步都算数。
即便是万丈深渊，走下去也是前程万里，后半生，悲欢得失不后悔。
生活不是我们活过的样子，而是我们记住的样子。
人生不如意事十之八九，但仍有一二成的如意，是值得我们高兴。生活总是山一程水一程，
一路风雨兼程，一路披荆斩棘，我们会面临许多诱惑，也会做出许多选择。
在这长长的一生，人人都有一段难以忘怀的往事。不悔是经历风风雨雨后的，智慧选择。
—————— 一禅心灵庙语
文章目录
变量
在MySQL数据库的存储过程和存储函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。在MySQL数据库中，变量分为 系统变量 以及 用户自定义变量。
1. 系统变量
变量由系统定义的，不是用户定义的，属于服务器层面。启动MySQL服务，生成MySQL服务实例期间，
MySQL将为 MySQL服务器 内存中的系统变量赋值，这些系统变量定义了，当前MySQL服务器实例的属性，特征。
这些系统变量的值，要么是编译MySQL时参数的默认值，要么就是 配置文件 例如my.ini 等中的参数值。大家可以通过网址 https://dev.mysql.com/doc/refman/8.0/en/server-systemvariables.html 查看MySQL文档的系统变量。
1.1 系统变量的分类
系统变量分为 全局系统变量(需要添加global关键字) 以及 会话系统变量(需要添加 session 关键字)，有时也把
全局系统变量简称为全局变量，有时也把会话系统变量 称为 local 变量 。如果不写，默认会话级别 。
静态变量 ：在 MySQL服务实例运行期间，它们的值不能使用 set 动态修改。属于特殊的全局系统变量。
每一个MySQL客户机成功连接 MySQL服务后，都会产生与之对应的会话。会话期间，MySQL服务实例，会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统被变量的复制。如下图：
1.2 全局系统变量
全局系统变量==作用范围：==针对于所有会话（连接）有效，但 不能跨重启(就是重启MySQL服务器，就会将修改好的全局系统变量，恢复会原来MySQL默认的值)
会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。
1.3 会话系统变量
会话系统变量 作用范围： 仅仅针对 当前时刻的单个会话连接数据库的客户端有效，当建立新的会话连接就无效了,重启MySQL服务器也会恢复到默认值。
1.4 查看系统变量
在MySQL中有些系统变量只能是全局的，例如max_connections 用于限制服务器的最大连接数；有些系统变量作用域既可以是全局又可以是会话 ，例如：character_set_client 用于设置客户端的字符集；有些系统变量的作用域，只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的MySQL连接 ID
查看所有或部分系统变量
- 查看所有的全局系统变量 ，代码如下。MySQL8.0 中 全局系统变量一共有
617个
-- 查看所有全局变量
SHOW GLOBAL VARIABLES;
-- global 全局变量的关键字
- 查看所有的会话系统变量 ，代码如下：在MySQL8.0 中 一共有
640个会话系统变量
-- 查看所有会话系统变量
SHOW SESSION VARIABLES;
-- session 会话系统变量的关键字
-- 或者 可以省略 session,因为指明是什么变量,默认优先是会话系统变量
SHOW VARIABLES;
- 查看满足条件的部分全局系统变量。 格式如下：
-- 查看满足条件的部分全局系统变量。
SHOW GLOBAL VARIABLES LIKE '%全局系统变量名%'; -- 模糊查询
举例: 查看以 admin 开头命名的全局系统变量
SHOW GLOBAL VARIABLES LIKE 'admi%';
- 查看满足条件的部分会话系统变量 ，代码格式如下：
-- 查看满足条件的部分会话系统变量
SHOW SESSION VARIABLES LIKE '%会话系统变量名%'; -- 模糊查询
举例: 查看以auto开头命名的会话系统变量
SHOW GLOBAL VARIABLES LIKE 'auto%';
- 查看指定系统变量
作为MySQL编码规范，MySQL中的系统变量以 两个 @@ 开头，其中 @@global 仅用于标记 全局系统变量 ，
而 @@session 仅用于标记会话系统变量。@@ 首先标记会话系统变量，如果会话系统变量不存在，则再去标记为全局系统变量。
注意：@@global 只能查询 全局系统变量，不能查询会话系统变量，同理 @@session 只能查询 会话系统变量，不能查询全局系统变量。而既是全局系统变量的又是会话系统变量的 无论是 @@global 还是 @@session 都可以查询
- 查看指定的全局系统变量的值 ,代码格式如下:
-- 查看指定的全局系统变量的值
SELECT @@global.全局系统变量名;
-- global 全局系统变量关键字
举例: 查看: max_connections 全局系统变量，表示用于限制服务器的最大连接数，代码如下
SELECT @@global.max_connections;
错误演示： 查询 pseudo_thread_id 的系统变量的值，表示 用于标记当前会话的MySQL连接 ID 错误原因: pseudo_thread_id 仅仅表示的是会话用户变量，而这里的 @@global 表示查询的是全局变量。
SELECT @@global.pseudo_thread_id;
举例: 查询character_set_client 的系统变量，表示该数据库的字符编码，character_set_client 既是全局系统变量又是会话系统变量
SELECT @@global.character_set_client;
- 查看指定的会话系统变量的值 ,代码格式如下:
-- 查看指定的会话系统变量的值
SELECT @@session.会话系统变量名;
-- session 会话系统变量的关键字
举例: 查看: pseudo_thread_id 会话系统变量的值，表示 用于标记当前会话的MySQL连接 ID
SELECT @@session.pseudo_thread_id;
举例: 查询character_set_client 的系统变量，表示该数据库的字符编码，character_set_client 既是全局系统变量又是会话系统变量
SELECT @@session.character_set_client;
1.5 修改指定系统变量的值
有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性，特征。
具体方法：
方式1：修改MySQL配置文件(my.ini )，继而修改MySQL系统变量的值(该方法需要重启MySQL服务)
方式2：在MySQL服务运行期间，使用 set 命令重新设置系统变量的值。
- 修改指定全局系统变量的值
全局系统变量的值，作用范围：只在当前所有 会话连接 有效，重启MySQL服务器就失效了。MySQL重新读取了其设定的默认参数值
格式如下：
方式一
-- 修改全局系统变量
SET @@global.全局系统变量名 = 变量值;
方式二
-- 修改全局系统变量
SET GLOBAL 全局系统变量名 = 变量值;
举例: 修改全局系统变量 : max_connections 表示可以连接数据库的客户端的个数从 151 修改为 200
SELECT @@global.max_connections; -- 查看该max_connections全局系统变量的值
修改为 200，代码如下；
-- 方式1
SET @@global.max_connections = 200;
-- 方式2
SET GLOBAL max_connections = 200;
-- 查看该max_connections 全局系统变量的值
SELECT @@global.max_connections;
修改后我们，重启MySQL服务器，管理员运行 dos命令，输入net stop mysql80关闭MySQL服务——>再 net start mysql80 重启 MySQL服务器。
net stop mysql80 -- 关闭 MySQL服务器
net start mysql80 -- 重启MySQL服务器
我们再运行，如下代码，查看刚刚我们修改后的 max_connections 发现又被修改回去了。
说明验证了，全局系统变量的修改，只在当前所有会话连接的客户端有效，重启MySQL有恢复默认值了
-- 查看该max_connections 全局系统变量的值
SELECT @@global.max_connections;
我们再创建一个新的会话连接，查看我们修改的变量值，发现并没有被，恢复成默认值，我们的修改依旧有效，
- 修改指定会话系统变量的值
会话系统变量的值的修改，只在当前时刻单个建立的会话连接的客户端有效，重新建立的会话连接无效,重启MySQL服务器也会恢复到默认值
格式如下：
方式一
-- 修改指定会话系统变量的值
SET @@session.会话系统变量名 = 变量值;
方式二
-- 修改指定会话系统变量的值
SET SESSION 会话系统变量名 = 变量值;
举例: 修改会话系统变量 pseudo_thread_id 用于标记当前会话的MySQL连接 ID 。将 8 修改为 10 ，代码如下
SELECT @@session.pseudo_thread_id; -- 查看该 pseudo_thread_id会话系统变量的值
将 8 修改为 10
-- 方式1
SET @@session.pseudo_thread_id = 10;
-- 方式2
SET SESSION pseudo_thread_id = 10;
-- 查看该 pseudo_thread_id会话系统变量的值
SELECT @@session.pseudo_thread_id;
修改后我们，重启MySQL服务器，管理员运行 dos命令，输入net stop mysql80关闭MySQL服务——>再 net start mysql80 重启 MySQL服务器。
net stop mysql80 -- 关闭 MySQL服务器
net start mysql80 -- 重启MySQL服务器
再次查看 该 pseudo_thread_id会话系统变量的值,恢复到了默认值。
SELECT @@session.pseudo_thread_id;
同样我们建立新的会话连接
在该新建的连接下查看 我们修改后的 pseudo_thread_id会话系统变量的值,发现也恢复到了一个新的默认值，不是我们修改后的值
2. 用户变量
2.1 用户变量的分类
用户变量是用户自己定义的，作为MySQL编码规范，MySQL中的用户变量以 一个 @ 开头(一般主要定义会话用户变量的时候加上 @，而局部用户变量不加，用于区分 )。根据作用范围不同，又分为 会话用户变量和局部用户变量
- 会话用户变量： 作用域和会话系统变量是一样的，只对 当前连接的会话有效，重启MySQL服务以及建立新的会话连接无效，会重新置为默认值
- 局部用户变量 ： 只在 **
begin...end**语句块中有效，局部变量只能在存储过程和存储函数中使用
2.2 会话用户变量
会话用户变量: 作用域和会话系统变量是一样的，只对 当前连接的会话有效，重启MySQL服务以及建立新的会话连接无效，会重新置为一个新的默认值
-
会话用户变量的定义声明可以在任意位置
-
会话用户变量的定义不需要指明数据类型
-
会话用户变量的定义前加上
@，用于区别局部变量 -
会话用户变量的定义必须赋初始值，不然报错
会话用户变量的定义，格式如下：
方式一，使用 SET 关键字，直接定义会话用户变量，同时直接赋值
-- 方式1 := 或 = 是一样的
SET @会话用户变量名 = 变量值;
SET @会话用户变量名 := 变量值;
方式二：通过表中的查询结果进行一个(定义并赋值)
-- 方式2
SELECT @会话用户变量名 = 表达式(查询的结果)
FROM xxx表;
-- 查询的结果于赋值的会话用户变量需要一一对应,防止赋值对象错误了
SELECT (表达式查询结果) INTO @会话用户变量1,@会话用户变量2
FROM xxx表;
查看会话用户变量的值，和查看表中的数据是一样的使用 select 关键字 ，格式如下
SELECT @会话用户变量名;
举例： 方式一：直接定义会话用户变量同时直接赋值，计算各个会话变量的数值
SET @num1 = 1; -- 会话用户变量,不用指明数据类型
SET @num2 = 2; -- 会话用户变量名前加上 @,用于区分局部变量
SET @sum = @num1 + @num2;
SELECT @num1,@num2,@sum; -- 查看会话用户变量的值;
举例: 方式二：计算表 employees 中的 平均工资，赋值到 会话用户变量中
SELECT AVG(salary) INTO @avg_sal -- 查询结果与变量一一对应,防止存储错误
FROM employees;
SELECT @avg_sal; -- 查看@avg_sal会话用户变量的值
2.3 局部变量
- 局部变量：作用域在
begin...end中。 - 局部变量最好不要加
@，便于区分 会话用户变量 - 局部变量只能在
存储过程和存储函数中使用，关于 存储过程和存储函数的内容，大家可以移步到 🔜🔜🔜 MySQL 详解 存储过程 和 存储函数_ChinaRainbowSea的博客-CSDN博客_mysql 存储过程 - 局部变量的定义需要使用 关键字
DECLARE，必须指明局部变量的数据类型，数据类型在变量名的后面写明 - 局部变量的定义声明，必须放在
begin...end中的首行(第一句)的位置，不然报错 - 局部变量可以使用
DEFAULT设定初始默认值,如果没有DEFAULT子句，初始值为NULL - 注意：局部变量的作用域只在
begin...end语句块的范围内，所以无论是定义使用还是赋值，查看局部变量都必须在该 begin…end 范围内
局部变量的定义格式如下：
begin
-- 局部变量的声明,必须在begin...end首行开始位置
DECLARE 局部变量名1 变量数据类型 DEFAULT xx; -- default 赋予初值
DECLARE 局部变量名2 变量数据类型 ; -- 没有default 默认是 null
end x
局部变量的赋值
方式1：直接使用 SET 赋值变量值，格式如下：
注意：局部变量的作用域只在 begin...end 语句块的范围内，所以无论是定义使用还是赋值，查看局部变量都必须在该 begin…end 范围内
begin
-- 方式1
SET 局部变量名 = 变量值;
end
方式2：根据查询的表中的结果 使用 关键字 INTO 进行赋值操作，格式如下
注意：数据类型最好一致，以及一一对应上，防止赋值存储失败或丢失数据 。注意：局部变量的作用域只在 begin...end 语句块的范围内，所以无论是定义使用还是赋值，查看局部变量都必须在该 begin…end 范围内
begin
-- 方式2
SELECT xxx INTO 局部变量名 -- 数据类型一一对应上,防止存储失败,或丢失数据
FROM xxx表;
end
查看局部变量的值
同时使用 查看表的关键字 SELECT ，格式如下
注意：局部变量的作用域只在 begin...end 语句块的范围内，所以无论是定义使用还是赋值，查看局部变量都必须在该 begin…end 范围内
begin
-- 查看局部变量的值
SELECT 局部变量名1,局部变量名2;
end
局部变量的完整使用 格式如下：
DELIMITER $ -- 修改;分号结束符,防止与存储过程体以及函数体中的;分号冲突
CREATE xxx(存储过程或存储函数)
BEGIN -- 开始
-- 局部变量的声明,必须在begin...end首行开始位置
DECLARE 局部变量名1 变量数据类型 DEFAULT xx; -- default 赋予初值
DECLARE 局部变量名2 变量数据类型 ; -- 没有default 默认是 null
-- 局部变量的赋值
-- 方式1
SET 局部变量名 = 变量值;
-- 方式2
SELECT xxx INTO 局部变量名 -- 数据类型一一对应上,防止存储失败,或丢失数据
FROM xxx表;
-- 查看局部变量的值,局部变量的范围只在 begin...end语句块中
SELECT 局部变量名1,局部变量名2;
END $ -- 使用修改后的$结束符,表示结束
DELIMITER ; -- 修改回去,防止影响后面的正常使用
举例: 创建一个存储函数名为 test_add()，计算 99 + 1 的总和，其中定义两个局部变量，分别为 99 的 num1,和 1 的num2
-- 存储函数的参数设置
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER $
CREATE FUNCTION test_add()
RETURNS INT -- 函数返回类型
BEGIN
-- 声明局部变量,必须在begin...end最开始位置声明
DECLARE num1 INT;
DECLARE num2 INT;
DECLARE test_sum INT;
-- 局部变量赋值,方式一
SET num1 = 99;
SET num2 = 1;
SET test_sum = num1 + num2;
-- 查看局部变量,作用域在begin...end语句块中
RETURN test_sum; -- 函数返回值;
END $
DELIMITER;
-- 调用函数
SELECT test_add();
举例2: 创建存储过程名为 test_pro( ) 声明局部变量，并分别赋值为employees表中的 employee_id 为 102 的last_name 和 salary
DELIMITER //
CREATE PROCEDURE test_pro()
BEGIN
-- 声明局部变量,必须在begin...end的最开始位置
DECLARE emp_name VARCHAR(25);
DECLARE sal DOUBLE DEFAULT 0.0; -- default 设置初始默认值为 0.0
-- 局部变量赋值,方式2
SELECT last_name, salary INTO emp_name, sal -- 注意要一一对应上,数据类型尽量一致,
FROM employees -- 防止存储错误,或数据缺失
WHERE employee_id = 102;
-- 查看局部变量的值,局部变量的作用域在begin...end语句块中,
SELECT emp_name,sal;
END //
DELIMITER ;
-- 调用存储过程
CALL test_pro();
2.4 对比会话用户变量与局部变量
| 作用域 | 定义位置 | 语法 | |
|---|---|---|---|
| 会话用户变量 | 当前会话连接 | 会话的任意位置 | 变量名前加@,不用指明数据类型 |
| 局部用户变量 | 定义在它的begin…end语句块中 | 必须在begin…end语句块的最开始位置 | 一般不建议加@,必须指明数据类型在变量名后面 |
3. MySQL 8.0的新特性
在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。例如，设置服务器语句超时的限制，可 以通过设置系统变量max_execution_time来实现：
SET GLOBAL MAX_EXECUTION_TIME=2000;
使用 SET GLOBAL语句设置的变量值只会 临时生效 。 数据库重启 后，服务器又会从MySQL配置文件中读取 变量的默认值。 MySQL 8.0版本新增了 SET PERSIST 命令。例如，设置服务器的最大连接数为1000：
SET PERSIST global max_connections = 1000;
MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用 其中的配置来覆盖默认的配置文件。
查看全局变量max_connections的值，结果如下：
mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)
设置全局变量max_connections的值：
mysql> set persist max_connections=1000;
Query OK, 0 rows affected (0.00 sec)
重启MySQL服务器 ，再次查询max_connections的值：
mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 1000 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)
最后:
限于自身水平，其中存在的错误，希望大家给予指教，韩信点兵——多多益善，谢谢大家，后会有期，江湖再见 ！！！
扫一扫
539
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
