MySQL 变量的详细讲解
每博一文案
见面少没关系，你不要喜欢上别人就好 一禅小和尚的微博视频
很喜欢席慕容写的一句话，挫折回来，也会去，热泪会流下，也会收起，没有什么
可以让我气馁的，因为我有着长长的一生。
人活着一世会发生什么，我们无从而知。
很多时候，我们以为的绝境，熬过去，会发现都是生活的常态。尽管生活总是让人感到绝望，
但我们可以选择兴高采烈的活着，人生最好的状态摸过于，前半生不拍，后半生不悔，即便
深处人生低谷，也不惧怕满山风雨。
世间的人和事，来和去，都有它的时间，我们只需要把自己修炼成最好的样子，然后静静的等待就好了。
人生是一场旅途，喜怒哀乐都有挫折。逆境是人生常态，关关难过，关关过，前路漫漫，一灿灿。
人生的旅程，会是宽阔平坦大道，也会是泥泞坎坷的羊肠小道，没有过不去的坎，我们走的每一步都算数。
即便是万丈深渊，走下去也是前程万里，后半生，悲欢得失不后悔。
生活不是我们活过的样子，而是我们记住的样子。
人生不如意事十之八九，但仍有一二成的如意，是值得我们高兴。生活总是山一程水一程，
一路风雨兼程，一路披荆斩棘，我们会面临许多诱惑，也会做出许多选择。
在这长长的一生，人人都有一段难以忘怀的往事。不悔是经历风风雨雨后的，智慧选择。
—————— 一禅心灵庙语
文章目录
MySQL 变量的详细讲解每博一文案变量1. 系统变量1.1 系统变量的分类1.2 全局系统变量1.3 会话系统变量1.4 查看系统变量1.5 修改指定系统变量的值
2. 用户变量2.1 用户变量的分类2.2 会话用户变量2.3 局部变量2.4 对比会话用户变量与局部变量
3. MySQL 8.0的新特性最后:
变量
在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)
最后:
限于自身水平，其中存在的错误，希望大家给予指教，韩信点兵——多多益善，谢谢大家，后会有期，江湖再见 ！！！
参考链接
您阅读本篇文章共花了：
发表评论