站長資訊平臺

MySQL的變量分類總結(jié)

2018-07-02 來源：importnew

原文出處：瀟湘隱者

在MySQL中，my.cnf是參數(shù)文件（Option Files），類似于ORACLE數(shù)據(jù)庫中的spfile、pfile參數(shù)文件，照理說，參數(shù)文件my.cnf中的都是系統(tǒng)參數(shù)（這種稱呼比較符合思維習(xí)慣），但是官方又稱呼其為系統(tǒng)變量（system variables），那么到底這個叫系統(tǒng)參數(shù)或系統(tǒng)變量（system variables）呢？這個曾經(jīng)是一個讓我很糾結(jié)的問題，因為MySQL中有各種類型的變量，有時候語言就是這么博大精深；相信很多人也對這個問題或多或少有點困惑。其實拋開這些名詞，它們就是同一個事情（東西），不管你叫它系統(tǒng)變量（system variables）或系統(tǒng)參數(shù)都可，無需那么糾結(jié)。就好比王三，有人叫他王三；也有人也叫他王麻子綽號一樣。

另外，MySQL中有很多變量類型，確實有時候讓人有點混淆不清，本文打算總結(jié)一下MySQL數(shù)據(jù)庫的各種變量類型，理清各種變量類型概念。能夠從全局有個清晰思路。MySQL變量類型具體參考下圖：

Server System Variables（系統(tǒng)變量）

MySQL系統(tǒng)變量(system variables)是指MySQL實例的各種系統(tǒng)變量，實際上是一些系統(tǒng)參數(shù)，用于初始化或設(shè)定數(shù)據(jù)庫對系統(tǒng)資源的占用，文件存放位置等等，這些變量包含MySQL編譯時的參數(shù)默認(rèn)值，或者my.cnf配置文件里配置的參數(shù)值。默認(rèn)情況下系統(tǒng)變量都是小寫字母。官方文檔介紹如下：

The MySQL server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically at runtime using the?SET?statement, which enables you to modify operation of the server without having to stop and restart it. You can also use system variable values in expressions.

系統(tǒng)變量（system variables）按作用域范圍可以分為會話級別系統(tǒng)變量和全局級別系統(tǒng)變量。如果要確認(rèn)系統(tǒng)變量是全局級別還是會話級別，可以參考官方文檔，如果Scope其值為GLOBAL或SESSION，表示變量既是全局級別系統(tǒng)變量，又是會話級別系統(tǒng)變量。如果其Scope其值為GLOBAL，表示系統(tǒng)變量為全局級別系統(tǒng)變量。

–查看系統(tǒng)變量的全局值

select * from information_schema.global_variables;
select * from information_schema.global_variables where variable_name='xxxx';
select * from performance_schema.global_variables;

–查看系統(tǒng)變量的當(dāng)前會話值

select * from information_schema.session_variables;
select * from information_schema.session_variables where variable_name='xxxx';
select * from performance_schema.session_variables;

SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

mysql> show variables like '%connect_timeout%'; 
mysql> show local variables like '%connect_timeout%';
mysql> show session variables like '%connect_timeout%';
mysql> show global variables like '%connect_timeout%';

注意：對于SHOW VARIABLES，如果不指定GLOBAL、SESSION或者LOCAL，MySQL返回SESSION值，如果要區(qū)分系統(tǒng)變量是全局還是會話級別。不能使用下面方式，如果某一個系統(tǒng)變量是全局級別的，那么在當(dāng)前會話的值也是全局級別的值。例如系統(tǒng)變量AUTOMATIC_SP_PRIVILEGES，它是一個全局級別系統(tǒng)變量，但是 show session variables like ‘%automatic_sp_privileges%’一樣能查到其值。所以這種方式無法區(qū)別系統(tǒng)變量是會話級別還是全局級別。

mysql> show session variables like '%automatic_sp_privileges%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| automatic_sp_privileges | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> select * from information_schema.global_variables
    -> where variable_name='automatic_sp_privileges';
+-------------------------+----------------+
| VARIABLE_NAME           | VARIABLE_VALUE |
+-------------------------+----------------+
| AUTOMATIC_SP_PRIVILEGES | ON             |
+-------------------------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql>

如果要區(qū)分系統(tǒng)變量是全局還是會話級別，可以用下面方式：

方法1：查官方文檔中系統(tǒng)變量的Scope屬性。
方法2：使用SET VARIABLE_NAME=xxx; 如果報ERROR 1229 (HY000)，則表示該變量為全局，如果不報錯，那么證明該系統(tǒng)變量為全局和會話兩個級別。

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

可以使用SET命令修改系統(tǒng)變量的值，如下所示：

修改全局級別系統(tǒng)變量：

SET GLOBAL max_connections=300;
SET @@global.max_connections=300;

注意：更改全局變量的值，需要擁有SUPER權(quán)限

修改會話級別系統(tǒng)變量：

SET @@session.max_join_size=DEFAULT;
SET max_join_size=DEFAULT;  --默認(rèn)為會話變量。如果在變量名前沒有級別限定符，表示修改會話級變量。
SET SESSION max_join_size=DEFAULT;

如果修改系統(tǒng)全局變量沒有指定GLOBAL或@@global的話，就會報“Variable ‘xxx’ is a GLOBAL variable and should be set with SET GLOBAL”這類錯誤。

mysql> set max_connections=300;
ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set global max_connections=300;
Query OK, 0 rows affected (0.00 sec)

mysql>

系統(tǒng)變量（system variables）按是否可以動態(tài)修改，可以分為系統(tǒng)動態(tài)變量(Dynamic System Variables)和系統(tǒng)靜態(tài)變量。怎么區(qū)分系統(tǒng)變量是動態(tài)和靜態(tài)的呢？這個只能查看官方文檔，系統(tǒng)變量的”Dynamic”屬性為Yes，則表示可以動態(tài)修改。Dynamic Variable具體可以參考https://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html

另外，有些系統(tǒng)變量是只讀的，不能修改的。如下所示：

mysql>
mysql> set global innodb_version='5.6.21';
ERROR 1238 (HY000): Variable 'innodb_version' is a read only variable
mysql>

另外，還有一個Structured System Variables概念，其實就是系統(tǒng)變量是一個結(jié)構(gòu)體（Strut），官方介紹如下所示：

Structured System Variables
A structured variable differs from a regular system variable in two respects:
Its value is a structure with components that specify server parameters considered to be closely related.
There might be several instances of a given type of structured variable. Each one has a different name and refers to a different resource maintained by the server.

Server Status Variables(服務(wù)器狀態(tài)變量）

MySQL狀態(tài)變量（Server Status Variables）是當(dāng)前服務(wù)器從啟動后累計的一些系統(tǒng)狀態(tài)信息，例如最大連接數(shù)，累計的中斷連接等等，主要用于評估當(dāng)前系統(tǒng)資源的使用情況以進(jìn)一步分析系統(tǒng)性能而做出相應(yīng)的調(diào)整決策。這個估計有人會跟系統(tǒng)變量混淆，其實狀態(tài)變量是動態(tài)變化的，另外，狀態(tài)變量是只讀的：只能由MySQL服務(wù)器本身設(shè)置和修改，對于用戶來說是只讀的，不可以通過SET語句設(shè)置和修改它們，而系統(tǒng)變量則可以隨時修改。狀態(tài)變量也分為會話級與全局級別狀態(tài)信息。有些狀態(tài)變量可以用FLUSH STATUS語句重置為零值。

關(guān)于查看狀態(tài)變量，show status也支持like匹配查詢。如下所示：

show status like '%variable_name%'
show global status like '%variable_name%'

#當(dāng)前測試環(huán)境
ysql> select version() from dual;
-----------+
 version() |
-----------+
 5.7.21    |
-----------+
 row in set (0.00 sec)

mysql> show status;  --查看所有的狀態(tài)變量

ysql> show global status like 'Aborted_connects%';
------------------+-------+
 Variable_name    | Value |
------------------+-------+
 Aborted_connects | 2     |
------------------+-------+
 row in set (0.01 sec)

ysql> show session status like 'Aborted_connects%';
------------------+-------+
 Variable_name    | Value |
------------------+-------+
 Aborted_connects | 2     |
------------------+-------+
 row in set (0.00 sec)

ysql> select * from information_schema.global_status;
RROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'
ysql> #
ysql> show variables like '%show_compatibility_56%';
-----------------------+-------+
 Variable_name         | Value |
-----------------------+-------+
 show_compatibility_56 | OFF   |
-----------------------+-------+
 row in set (0.00 sec)

ysql> set global show_compatibility_56=on;
uery OK, 0 rows affected (0.00 sec)

ysql> select * from information_schema.global_status;
-----------------------------------------------+---------------------------------------+
 VARIABLE_NAME                                  VARIABLE_VALUE                         |
-----------------------------------------------+---------------------------------------+
 ABORTED_CLIENTS                               | 138097                                |
 ABORTED_CONNECTS                              | 5                                     |
 BINLOG_CACHE_DISK_USE                         | 0                                     |
 BINLOG_CACHE_USE                              | 0                                     |
....................................................................................

select * from performance_schema.global_status;
select * from performance_schema.session_status;

注意：MySQL 5.7以后系統(tǒng)變量和狀態(tài)變量需要從performance_schema中進(jìn)行獲取，information_schema仍然保留了GLOBAL_STATUS，GLOBAL_VARIABLES兩個表做兼容，如果希望沿用information_schema中進(jìn)行查詢的習(xí)慣，5.7提供了show_compatibility_56參數(shù)，設(shè)置為ON可以兼容5.7之前的用法，否則就會報錯（ERROR 3167 (HY000)）.

User-Defined Variables（用戶自定義變量）

用戶自定義變量，顧名思義就是用戶自己定義的變量。用戶自定義變量是基于當(dāng)前會話的。也就是說用戶自定義變量的作用域局限于當(dāng)前會話（連接），由一個客戶端定義的用戶自定義變量不能被其他客戶端看到或使用。（例外：可以訪問performance_schema.user_variables_by_thread表的用戶可以看到所有會話的定義的用戶自定義變量，當(dāng)然僅僅能看到那些會話定義了哪些變量，而不能訪問這些變量。）。當(dāng)客戶端會話退出時，當(dāng)前會話所有的自定義變量都會自動釋放。

一般可以在SQL語句將值存儲在用戶自定義變量中，然后再利用另一條SQL語句來查詢用戶自定義變量。這樣以來，可以在不同的SQL間傳遞值。

另外，用戶自定義變量是大小寫不敏感的，最大長度為64個字符，用戶自定義變量的形式一般為@var_name，其中變量名稱由字母、數(shù)字、“.”、“_”和“$”組成。當(dāng)然，在以字符串或者標(biāo)識符引用時也可以包含其他特殊字符（例如：@’my-var’，@”my-var”，或者@`my-var`）。。使用SET設(shè)置變量時，可以使用“=”或者“:=”操作符進(jìn)行賦值。對于SET，可以使用=或:=來賦值，對于SELECT只能使用:=來賦值。如下所示：

mysql> set @$test1="test";
Query OK, 0 rows affected (0.00 sec)
mysql> select @$test1 from dual;
+---------+
| @$test1 |
+---------+
| test    |
+---------+
1 row in set (0.00 sec)

mysql> 
mysql> set @"ac#k":='kerry';
Query OK, 0 rows affected (0.00 sec)

mysql> select @"ac#k" from dual;
+---------+
| @"ac#k" |
+---------+
| kerry   |
+---------+
1 row in set (0.00 sec)

mysql> 

mysql> select version() from dual;
+-----------+
| version() |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.00 sec)

mysql> 
mysql> set @my_test=1200;
Query OK, 0 rows affected (0.00 sec)

mysql> select @my_test;
+----------+
| @my_test |
+----------+
|     1200 |
+----------+
1 row in set (0.00 sec)

mysql> select connection_id() from dual;
+-----------------+
| connection_id() |
+-----------------+
|          149379 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT c.id, 
    ->        b.thread_id
    -> FROM   performance_schema.threads b 
    ->     join information_schema.processlist c 
    ->          ON b.processlist_id = c.id 
    -> where c.id=149379;
+--------+-----------+
| id     | thread_id |
+--------+-----------+
| 149379 |    149404 |
+--------+-----------+
1 row in set (0.00 sec)

mysql> select @My_Test, @my_TEST from dual;
+----------+----------+
| @My_Test | @my_TEST |
+----------+----------+
|     1200 |     1200 |
+----------+----------+
1 row in set (0.00 sec)

mysql>

mysql> select connection_id() from dual;
+-----------------+
| connection_id() |
+-----------------+
|          151821 |
+-----------------+
1 row in set (0.00 sec)

mysql> select @my_test from dual;
+----------+
| @my_test |
+----------+
| NULL     |
+----------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.user_variables_by_thread;
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
|    149404 | my_test       | 1200           |
+-----------+---------------+----------------+
1 row in set (0.00 sec)

mysql>

用戶自定義變量注意事項，以下為總結(jié)：

總結(jié)

1 未定義的用戶自定義變量初始值是NULL

mysql> select @kerry from dual;

+--------+

| @kerry |

+--------+

| NULL   |

+--------+

1 row in set (0.00 sec)

注意：使用未定義變量不會產(chǎn)生任何語法錯誤，由于其被初始化為NULL值，如果沒有意識到這一點，非常容易犯錯。如下所示：

mysql> select @num1, @num2 :=@num1+1 from dual;
+-------+-----------------+
| @num1 | @num2 :=@num1+1 |
+-------+-----------------+
| NULL  |            NULL |
+-------+-----------------+
1 row in set (0.00 sec)

mysql>

2 用戶變量名對大小寫不敏感（上面已經(jīng)敘述，此處從略）

3 自定義變量的類型是一個動態(tài)類型

MySQL中用戶自定義變量，不嚴(yán)格限制數(shù)據(jù)類型的，它的數(shù)據(jù)類型根據(jù)你賦給它的值而隨時變化。而且自定義變量如果賦予數(shù)字值，是不能保證進(jìn)度的。官方文檔介紹：

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value. Assignment of decimal and real values does not preserve the precision or scale of the value. A value of a type other than one of the permissible types is converted to a permissible type. For example, a value having a temporal or spatial data type is converted to a binary string. A value having the JSON data type is converted to a string with a character set of utf8mb4 and a collation of utf8mb4_bin.

4 賦值的順序和賦值的時間點并不總是固定的，這依賴于優(yōu)化器的決定

使用用戶自定義變量的一個最常見的問題就是沒有注意到在賦值和讀取用戶自定義變量的時候可能是在查詢的不同階段。例如，在SELECT語句中進(jìn)行賦值然后再WHERE子句中讀取用戶自定義變量，則可能用戶自定義變量取值并不不是你所想象的那樣，如下例子所示，因為按照MySQL語句的執(zhí)行順序，WHERE部分優(yōu)先與SELECT部分操作，所以你會看到msgid 和 @rownum的最大值為6.

mysql> select msgid from message order by msgid limit 12;
+-------+
| msgid |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
|     6 |
|     7 |
|    11 |
|    12 |
|    13 |
|    18 |
|    19 |
+-------+
12 rows in set (0.00 sec)

mysql> set @rownum := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select msgid , @rownum := @rownum +1 as rownum
    -> from message
    -> where @rownum <=5;
+-------+--------+
| msgid | rownum |
+-------+--------+
|     1 |      1 |
|     2 |      2 |
|     3 |      3 |
|     4 |      4 |
|     5 |      5 |
|     6 |      6 |
+-------+--------+
6 rows in set (0.00 sec)

mysql> select msgid , @rownum := @rownum +1 as rownum
    -> from message
    -> where @rownum <=5;
Empty set (0.00 sec)

mysql> select @rownum from dual;
+---------+
| @rownum |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

mysql>

如上所示，第二次查詢可能你想要的邏輯跟實際邏輯已經(jīng)出現(xiàn)了偏差，這個是使用自定義變量需要小心的地方。因為用戶自定義變量在當(dāng)前會話中也算一個“全局變量”，它已經(jīng)變成了6，where條件后面的 @rownum <= 5 邏輯為false了。一不小小心就會出現(xiàn)和你預(yù)想的結(jié)果出現(xiàn)偏差。

不要在同一個非SET語句中同時賦值并使用同一個用戶自定義變量，因為WHERE和SELECT是在查詢執(zhí)行的不同階段被執(zhí)行的。如果在查詢中再加入ORDER BY的話，結(jié)果可能會更不同；

mysql> set @rownum :=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select msgid , @rownum := @rownum +1 as rownum
    -> from message
    -> where @rownum <=5;
+-------+--------+
| msgid | rownum |
+-------+--------+
|     1 |      1 |
|     2 |      2 |
|     3 |      3 |
|     4 |      4 |
|     5 |      5 |
|     6 |      6 |
+-------+--------+
6 rows in set (0.00 sec)

mysql> 

mysql> set @rownum := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select msgid, @rownum := @rownum +1 as rownum
    -> from message
    -> where @rownum <=5
    -> order by msgcontent;
+-------+--------+
| msgid | rownum |
+-------+--------+
|    20 |      1 |
|    28 |      2 |
|    43 |      3 |
|    47 |      4 |
..................
..................
|    22 |     57 |
|    69 |     58 |
|    40 |     59 |
|    52 |     60 |
|    24 |     61 |
|    66 |     62 |
|    51 |     63 |
+-------+--------+
63 rows in set (0.00 sec)

mysql>

如果按msgid排序，那么又是正常的，那三者有啥區(qū)別呢？

mysql> set @rownum :=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select msgid, @rownum := @rownum +1 as rownum
    -> from message
    -> where @rownum <=5
    -> order by msgid;
+-------+--------+
| msgid | rownum |
+-------+--------+
|     1 |      1 |
|     2 |      2 |
|     3 |      3 |
|     4 |      4 |
|     5 |      5 |
|     6 |      6 |
+-------+--------+
6 rows in set (0.00 sec)

mysql>

我們先看執(zhí)行計劃

官方的解釋如下：

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected

在SELECT語句中，每個選擇表達(dá)式僅在發(fā)送給客戶端時才被計算。這意味著在HAVING，GROUP BY或ORDER BY子句中，引用在選擇表達(dá)式列表中指定值的用戶自定義變量不能按預(yù)期工作。也就是說用戶自定義變量的值是在結(jié)果集發(fā)送到客戶端后才計算的

測試官方的例子：

這種解釋算是比較權(quán)威的，但是，讓人有點不解的是，SQL執(zhí)行順序中WHERE在SELECT操作之前，但是第一個SQL語句又怎么解釋呢？有種解釋是“MySQL優(yōu)化器在某些場景下可能會將這些變量優(yōu)化掉，這可能導(dǎo)致代碼不按預(yù)想的方式運行�！� 解決這個問題的辦法是讓變量的賦值和取值發(fā)生在執(zhí)行查詢的同一階段，如下所示：

關(guān)于用戶自定義變量，如果運用的好，能夠?qū)懗龈咝Ш啙嵉腟QL語句，如果運用不當(dāng)，也可能把自己給坑了。這個完全取決于使用它的人。

官方文檔也有介紹用戶自定義變量不適合使用場景。摘抄部分如下：

User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such as SELECT.

局部變量

局部變量：作用范圍在begin到end語句塊之間。在該語句塊里設(shè)置的變量。declare語句專門用于定義聲明局部變量。

局部變量與用戶自定義變量的區(qū)分在于下面這些方面:

用戶自定義變量是以”@”開頭的。局部變量沒有這個符號。
定義變量方式不同。用戶自定義變量使用set語句，局部變量使用declare語句定義
作用范圍不同。局部變量只在begin-end語句塊之間有效。在begin-end語句塊運行完之后，局部變量就消失了。而用戶自定義變量是對當(dāng)前連接（會話）有效。

參考資料

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
https://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html
https://dev.mysql.com/doc/refman/5.7/en/structured-system-variables.html
https://dev.mysql.com/doc/refman/5.7/en/declare-local-variable.html
https://www.jianshu.com/p/357a02fb2d64

標(biāo)簽： Mysql ssl 代碼服務(wù)器權(quán)限數(shù)據(jù)庫

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請與原作者聯(lián)系。