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;

在这里插入图片描述


  1. 查看指定的会话系统变量的值 ,代码格式如下:
-- 查看指定的会话系统变量的值
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 对比会话用户变量与局部变量

作用域定义位置语法
会话用户变量当前会话连接会话的任意位置变量名前加@,不用指明数据类型
局部用户变量定义在它的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)

最后:

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


在这里插入图片描述


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值