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

MySQL
415
0
0
2023-03-07
MYSQL中的json数据操作代码
上一篇
mysql存储过程 返回 list结果集方式
下一篇
目录
  • 系统变量
  • 什么是Mysql系统变量
  • 设置系统变量
  • my.cnf设置配置
  • 启动项配置
  • Set的形式修改系统变量
  • SESSION作用范围语法
  • GLOBAL作用范围语法
  • 查看系统变量值
  • 系统变量注意点
  • 并不是所有的系统变量都有session范围和global范围
  • 并不是所有系统变量可以修改
  • 状态变量

系统变量

什么是Mysql系统变量

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

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

演示如下：

### 模糊搜索系统变量中包含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文件配置;

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

客户端读取:

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或者启动配置项中采用中划线或者下划线是等价的，一定要注意区分！！！！！！。

启动项配置

服务端启动配置

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

客户端查询系统变量

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作用范围，那么只针对当前客户端的系统变量需要如何设置呢，

体验如下所示:

#### 先查询一次存储引擎的值
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范围的语法，具体语法如下:

### 语法一：默认采用session
mysql> set default_storage_engine=InnoDB;
### 语法二：指定作用范围session
mysql> set session default_storage_engine InnoDB;
### 语法三
mysql> set @@session.default_storage_engine=InnoDB;

GLOBAL作用范围语法

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

### 语法一
mysql> set global default_storage_engine=InnoDB;
### 语法二
mysql> set @@global.default_storage_engine=MyISAM;

查看系统变量值

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

show [global|session] variables like [匹配元素支持模糊匹配%]
默认显示的是session作用范围的系统变量。

命令演示如下:

### 查询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初始值，

演示如下:

### 初始值默认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作用范围的不能设置为全局系统变量，提示如下:

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修改

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

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

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

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

命令演示如下:

### 默认查询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中的json数据操作代码
上一篇
mysql存储过程 返回 list结果集方式
下一篇