MySQL 变量的详细讲解

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 都可以查询

1. 查看指定的全局系统变量的值 ,代码格式如下:

-- 查看指定的全局系统变量的值
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;

---

2. 查看指定的会话系统变量的值 ,代码格式如下:

-- 查看指定的会话系统变量的值
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服务以及建立新的会话连接无效，会重新置为一个新的默认值

1. 会话用户变量的定义声明可以在任意位置

2. 会话用户变量的定义不需要指明数据类型

3. 会话用户变量的定义前加上 @，用于区别局部变量

4. 会话用户变量的定义必须赋初始值，不然报错

---

会话用户变量的定义，格式如下：

方式一，使用 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 局部变量

1. 局部变量：作用域在 begin...end 中。
2. 局部变量最好不要加 @ ，便于区分 会话用户变量
3. 局部变量只能在 存储过程 和 存储函数 中使用，关于 存储过程和存储函数的内容，大家可以移步到 🔜🔜🔜 MySQL 详解 存储过程 和 存储函数_ChinaRainbowSea的博客-CSDN博客_mysql 存储过程
4. 局部变量的定义需要使用 关键字DECLARE ，必须指明局部变量的数据类型，数据类型在变量名的后面写明
5. 局部变量的定义声明，必须放在 begin...end 中的首行(第一句)的位置，不然报错
6. 局部变量可以使用 DEFAULT 设定初始默认值,如果没有 DEFAULT子句，初始值为NULL
7. 注意：局部变量的作用域只在 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 对比会话用户变量与局部变量

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)

最后:

限于自身水平，其中存在的错误，希望大家给予指教，韩信点兵——多多益善，谢谢大家，后会有期，江湖再见 ！！！

---

---