服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - Mysql系统变量与状态变量详细介绍

Mysql系统变量与状态变量详细介绍

2022-11-21 17:13Java面试365 Mysql

这篇文章主要介绍了Mysql系统变量与状态变量详细介绍，能够在程序运行过程中影响Mysql程序行为的变量称之为系统变量，想了解更多相关内容的小伙伴可以参考下面文章内容

系统变量

什么是Mysql系统变量

能够在程序运行过程中影响Mysql程序行为的变量称之为系统变量。

在Mysql中存在多个系统变量，可以使用show variables查看，如果直接执行得到的结果是查询了所有的系统变量，所以这个命令支持模糊匹配，

演示如下：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
### 模糊搜索系统变量中包含conn的所有变量，精确匹配时值不包含%
mysql> show variables like '%conn%';
+-----------------------------------------------+-----------------+
| Variable_name                                 | Value           |
+-----------------------------------------------+-----------------+
| character_set_connection                      | utf8            |
| collation_connection                          | utf8_general_ci |
| connect_timeout                               | 10              |
| disconnect_on_expired_password                | ON              |
| init_connect                                  |                 |
| max_connect_errors                            | 100             |
| max_connections                               | 400             |
| max_user_connections                          | 0               |
| performance_schema_session_connect_attrs_size | 512             |
+-----------------------------------------------+-----------------+

设置系统变量

设置系统变量其实在上篇已经聊过两种通过启动项配置以及my.cnf配置文件配置，演示如下

my.cnf设置配置

my.cnf文件配置;

?
1
2
3
4
5
[mysqld]
datadir=/usr/local/mysql-5.7.26/data
default-storage-engine=MyISAM
max-connections=10
### 其它配置省略

客户端读取:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show variables like '%datadir%';
+---------------+-------------------------------+
| Variable_name | Value                         |
+---------------+-------------------------------+
| datadir       | /usr/local/mysql-5.7.26/data/ |
+---------------+-------------------------------+
1 row in set (0.00 sec)
 
mysql> show variables like '%default-storage-engine%';
Empty set (0.01 sec)
#### 注意搜索系统变量名字的区别！！！
mysql> show variables like '%default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)

注意：在MySQL客户端读取配置时所有系统变量采用的是下划线（仅支持下划线），而在配置文件My.cnf或者启动配置项中采用中划线或者下划线是等价的，一定要注意区分！！！！！！。

启动项配置

服务端启动配置

?
1
2
### root用户执行采用配置--user=root，非root用户不用
mysqld --user=root --max-connections=111

客户端查询系统变量

?
1
2
3
4
5
6
7
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 111   |
+-----------------+-------+
1 row in set (0.00 sec)

这两种方法都可以修改系统变量，但是缺点也是存在的

  • 修改完需要重启服务端才可以生效。
  • 修改完对所有的客户端都生效，有时单个客户端需要有特定的系统变量，显然不符合要求。

所以这里介绍第三种方式，采用set的形式修改系统变量。

Set的形式修改系统变量

在Mysql客户端我们可以采用Set命令直接修改系统变量，不过需要注意的是，Set的形式修改会指定系统变量生效的范围，因为当多个客户端连接同一个服务端时可能一个系统变量需要作用于所有的客户端，有的系统变量只需要作用于当前的客户端即可，根据作用范围分为如下两种。

  • **GLOBAL：**针对所有的客户端生效。
  • **SESSION（也被称为local）：**只对当前会话生效，其余客户端不受影响。

显然对于my.cnf设置配置和启动项配置都是作用于所有的客户端，也就是GLOBAL作用范围，那么只针对当前客户端的系统变量需要如何设置呢，

体验如下所示:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#### 先查询一次存储引擎的值
mysql> show variables like '%default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)
 
mysql> set default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like '%default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

SESSION作用范围语法

我们在使用set命令改变系统变量的值其实默认就是session范围的语法，具体语法如下:

?
1
2
3
4
5
6
### 语法一：默认采用session
mysql> set default_storage_engine=InnoDB;
### 语法二：指定作用范围session
mysql> set session default_storage_engine InnoDB;
### 语法三
mysql> set @@session.default_storage_engine=InnoDB;

GLOBAL作用范围语法

global影响范围是所有的客户端，具体语法如下所示:

?
1
2
3
4
### 语法一
mysql> set global default_storage_engine=InnoDB;
### 语法二
mysql> set @@global.default_storage_engine=MyISAM;

查看系统变量值

从上面我们知道了Mysql中的系统变量分为两种操作范围，同样查询系统变量时也是分作用范围查询，查询系统变量语法如下所示。

show [global|session]  variables like [匹配元素支持模糊匹配%]

默认显示的是session作用范围的系统变量。

命令演示如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
### 查询session当前客户端存储引擎的系统变量
mysql> show session variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)
### 查询global所有客户端存储引擎的系统变量
mysql> show global  variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)
 
### 改变全局系统变量【默认存储引擎】
mysql> set global default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
 
### 再次查询全局系统变量【默认存储引擎】值，修改成功！！！！！！！
mysql> show global  variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)
 
### 再次查询当前客户端【默认存储引擎】值，没有被修改！！
mysql> show session variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

注意:当修改全局global作用范围的系统变量时，session作用范围的相同名字系统变量值并不会马上修改，需要等客户端下一次接入时才会生效，也就是说修改全局global作用范围的系统变量，只会影响后续接入客户端作用范围为session的系统变量。

系统变量注意点

并不是所有的系统变量都有session范围和global范围

只有session范围的系统变量

只有session范围的系统变量，如insert_id，表示对当前客户端表设置了自增长AUTO_INCREMENT列，插入数据的自增长id初始值，

演示如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
### 初始值默认0
mysql> show variables like 'insert_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| insert_id     | 0     |
+---------------+-------+
1 row in set (0.00 sec)
### 设置初始值为2
mysql> set session insert_id=2;
Query OK, 0 rows affected (0.00 sec)
 
### 创建一张表，带有自增长AUTO_INCREMENT列
mysql> CREATE TABLE tb_student(
    -> id INT(4) PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(25) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into tb_student(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
 
### 自增长初始值由0变为2，设置成功！！
mysql> select * from tb_student;
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

session作用范围的不能设置为全局系统变量，提示如下:

?
1
2
mysql> set global insert_id=2;
ERROR 1228 (HY000): Variable 'insert_id' is a SESSION variable and can't be used with SET GLOBAL

只有global范围的系统变量

这类变量多为系统功能支持变量，如max_connections系统最大连接数，只能采用global修改

?
1
2
mysql> set max_connections=123;
ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL

并不是所有系统变量可以修改

对于绝大多数系统变量来讲都是可以修改的，但万事有例外，有些系统变量只读，如version查看数据库版本。

?
1
2
3
4
5
6
7
8
9
10
11
mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.7.26 |
+---------------+--------+
1 row in set (0.01 sec)
 
### 只读系统变量修改报错
mysql> set version='1.0.0';
ERROR 1238 (HY000): Variable 'version' is a read only variable

状态变量

系统变量是能够影响程序的运行行为的变量，而状态变量是能够帮我们了解程序运行过程中的情况，这些状态变量的值由程序运行过程中产生，不可设置，具体语法如下

show [global|session] status like  [匹配元素支持模糊匹配%]

不写明作用范围默认session

命令演示如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
### 默认查询session作用范围的数据
mysql> show status like '%threads%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 0     |
| Threads_connected      | 2     |
| Threads_created        | 2     |
| Threads_running        | 1     |
+------------------------+-------+

到此这篇关于Mysql系统变量与状态变量详细介绍的文章就介绍到这了,更多相关Mysql系统变量内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://juejin.cn/post/7121361293298106398

  • mysql
  • 系统变量
  • 状态变量

    • 延伸 · 阅读

    精彩推荐
    • MysqlMySQL主从复制之半同步semi-sync replication

      MySQL主从复制之半同步semi-sync replication

      这篇文章主要介绍了MySQL主从复制之半同步semi-sync replication，半同步相对于异步复制而言，提高了数据的安全性，同时也造成了一定程度的延迟，这个延迟...

      GreatSQL社区7332022-09-26
    • MysqlMySQL时间格式化

      MySQL时间格式化

      在处理数据的时候，经常会在MySQL里面格式化时间进行统计。 ...

      今日头条11782021-05-20
    • MysqlMySQL数据库优化技术之索引使用技巧总结

      MySQL数据库优化技术之索引使用技巧总结

      这篇文章主要介绍了MySQL数据库优化技术之索引使用方法,结合实例形式总结分析了MySQL表的优化、索引设置、SQL优化等相关技巧,非常具有实用价值,需要的朋...

      miky1632020-06-17
    • MysqlLinux下mysql的root密码修改方法

      Linux下mysql的root密码修改方法

      mysql是我们经常在linux或者windows需要用的一种数据库，相信每位程序员们对mysql应该都再熟悉不过了，但是有时大脑短路，突然忘记mysql的超级用户root的密码...

      daisy2152020-07-20
    • Mysqlmysql 8.0.18 安装配置图文教程

      mysql 8.0.18 安装配置图文教程

      这篇文章主要为大家详细介绍了mysql 8.0.18 安装配置图文教程，文中安装步骤介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...

      陳Huid2442020-12-18
    • MysqlSQL面试题:求时间差之和(有重复不计)

      SQL面试题:求时间差之和(有重复不计)

      这篇文章主要介绍了SQL面试题:求时间差之和(有重复不计)，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋...

      开心学大数据2292020-12-16
    • MysqlMySql8设置远程连接的实战记录

      MySql8设置远程连接的实战记录

      与SQL Server类似,MySQL在需要远程操纵其他电脑时,也需要对其做远程连接的相应设置,下面这篇文章主要给大家介绍了关于MySql8设置远程连接的相关资料,文中通...

      有本事来咬我8742022-10-21
    • MysqlMySQL绿色版(zip解压版)的安装图文教程（mysql-5.6.22-win32.zip）

      MySQL绿色版(zip解压版)的安装图文教程（mysql-5.6.22-win32.zip）

      由于工作需要最近要开始研究MySQL了（看来学习都是逼出来的），本人对mysql没有研究，可以说一个小白。 下面就从安装开始吧，虽然网上关于这方面的东...

      MYSQL教程网4512020-06-12
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 2006 　 1615 　 1215 　 1175 　 10048 　 2003 　 1366 　 28000 　 1577 　 1135 　 1093 　 1786 　 1067 　 1698 　 2002 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图