Mysql系統變數與狀態變數詳細介紹

2022-09-30 14:02:42

系統變數

什麼是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系統變數與狀態變數詳細介紹的文章就介紹到這了,更多相關Mysql系統變數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！