?
在MySQL中，my.cnf是參數文件（Option Files），類似于ORACLE數據庫中的spfile、pfile參數文件，照理說，參數文件my.cnf中的都是系統參數（這種稱呼比較符合思維習慣），但是官方又稱呼其為系統變量（system variables），那么到底這個叫系統參數或系統變量（system variables）呢？ 這個曾經是一個讓我很糾結的問題，因為MySQL中有各種類型的變量，有時候語言就是這么博大精深；相信很多人也對這個問題或多或少有點困惑。其實拋開這些名詞，它們就是同一個事情（東西），不管你叫它系統變量（system variables）或系統參數都可，無需那么糾結。 就好比王三，有人叫他王三；也有人也叫他王麻子綽號一樣。
?
另外，MySQL中有很多變量類型，確實有時候讓人有點混淆不清，本文打算總結一下MySQL數據庫的各種變量類型，理清各種變量類型概念。能夠從全局有個清晰思路。MySQL變量類型具體參考下圖：
?
?
?
?
?
Server System Variables（系統變量）
?
?
MySQL系統變量(system variables)是指MySQL實例的各種系統變量，實際上是一些系統參數，用于初始化或設定數據庫對系統資源的占用，文件存放位置等等，這些變量包含MySQL編譯時的參數默認值，或者my.cnf配置文件里配置的參數值。默認情況下系統變量都是小寫字母。官方文檔介紹如下：
?
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.
?
?
系統變量（system variables）按作用域范圍可以分為會話級別系統變量和全局級別系統變量。如果要確認系統變量是全局級別還是會話級別，可以參考官方文檔，如果Scope其值為GLOBAL或SESSION，表示變量既是全局級別系統變量，又是會話級別系統變量。如果其Scope其值為GLOBAL，表示系統變量為全局級別系統變量。
?
--查看系統變量的全局值
?
select * from information_schema.global_variables;
select * from information_schema.global_variables
? where variable_name='xxxx';
select * from performance_schema.global_variables;
?
?
--查看系統變量的當前會話值
?
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值，如果要區分系統變量是全局還是會話級別。不能使用下面方式，如果某一個系統變量是全局級別的，那么在當前會話的值也是全局級別的值。例如系統變量AUTOMATIC_SP_PRIVILEGES，它是一個全局級別系統變量，但是 show session variables like '%automatic_sp_privileges%'一樣能查到其值。所以這種方式無法區別系統變量是會話級別還是全局級別。
?
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>
?
?
如果要區分系統變量是全局還是會話級別，可以用下面方式：
?
方法1： 查官方文檔中系統變量的Scope屬性。
方法2： 使用SET VARIABLE_NAME=xxx; 如果報ERROR 1229 (HY000)，則表示該變量為全局，如果不報錯，那么證明該系統變量為全局和會話兩個級別。
???
?
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命令修改系統變量的值，如下所示：
?
修改全局級別系統變量：
?
SET?GLOBAL max_connections=300;
?
SET @@global.max_connections=300;
?
注意：更改全局變量的值，需要擁有SUPER權限
?
修改會話級別系統變量：
?
?? SET @@session.max_join_size=DEFAULT;
? SET max_join_size=DEFAULT;? --默認為會話變量。如果在變量名前沒有級別限定符，表示修改會話級變量。
?? SET SESSION max_join_size=DEFAULT;
?
如果修改系統全局變量沒有指定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>
?
?
?
系統變量（system variables）按是否可以動態修改，可以分為系統動態變量(Dynamic System Variables)和系統靜態變量。怎么區分系統變量是動態和靜態的呢？ 這個只能查看官方文檔，系統變量的"Dynamic"屬性為Yes，則表示可以動態修改。Dynamic Variable具體可以參考https://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html
?
另外，有些系統變量是只讀的，不能修改的。如下所示：
?
mysql>
mysql> set global innodb_version='5.6.21';
ERROR 1238 (HY000): Variable 'innodb_version' is a read only variable
mysql>
?
?
另外，還有一個Structured System Variables概念，其實就是系統變量是一個結構體（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(服務器狀態變量）
?
?
MySQL狀態變量（Server Status Variables）是當前服務器從啟動后累計的一些系統狀態信息，例如最大連接數，累計的中斷連接等等，主要用于評估當前系統資源的使用情況以進一步分析系統性能而做出相應的調整決策。這個估計有人會跟系統變量混淆，其實狀態變量是動態變化的，另外，狀態變量是只讀的：只能由MySQL服務器本身設置和修改，對于用戶來說是只讀的，不可以通過SET語句設置和修改它們，而系統變量則可以隨時修改。狀態變量也分為會話級與全局級別狀態信息。有些狀態變量可以用FLUSH STATUS語句重置為零值。
?
關于查看狀態變量，show status也支持like匹配查詢。如下所示：
?
show status like '%variable_name%'
show global status like '%variable_name%'
?
??
#當前測試環境
ysql> select version() from dual;
-----------+
version() |
-----------+
5.7.21??? |
-----------+
?row?in?set (0.00 sec)
??
mysql> show status;? --查看所有的狀態變量
?
?
?
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以后系統變量和狀態變量需要從performance_schema中進行獲取，information_schema仍然保留了GLOBAL_STATUS，GLOBAL_VARIABLES兩個表做兼容，如果希望沿用information_schema中進行查詢的習慣，5.7提供了show_compatibility_56參數，設置為ON可以兼容5.7之前的用法，否則就會報錯（ERROR 3167 (HY000)）.
?
?
?
?
User-Defined Variables（用戶自定義變量）
?
?
用戶自定義變量，顧名思義就是用戶自己定義的變量。用戶自定義變量是基于當前會話的。 也就是說用戶自定義變量的作用域局限于當前會話（連接），由一個客戶端定義的用戶自定義變量不能被其他客戶端看到或使用。（例外：可以訪問performance_schema.user_variables_by_thread表的用戶可以看到所有會話的定義的用戶自定義變量，當然僅僅能看到那些會話定義了哪些變量，而不能訪問這些變量。）。當客戶端會話退出時，當前會話所有的自定義變量都會自動釋放。
一般可以在SQL語句將值存儲在用戶自定義變量中，然后再利用另一條SQL語句來查詢用戶自定義變量。這樣以來，可以在不同的SQL間傳遞值。
?
另外，用戶自定義變量是大小寫不敏感的，最大長度為64個字符，用戶自定義變量的形式一般為@var_name，其中變量名稱由字母、數字、“.”、“_”和“$”組成。當然，在以字符串或者標識符引用時也可以包含其他特殊字符（例如：@'my-var'，@"my-var"，或者@`my-var`）。。使用SET設置變量時，可以使用“=”或者“:=”操作符進行賦值。對于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>
?
?
?
?
?
用戶自定義變量注意事項，以下為總結：
?
1：未定義的用戶自定義變量初始值是NULL
?
mysql> select @kerry from dual;
?
+--------+
?
| @kerry |
?
+--------+
?
| NULL?? |?
+--------+
?
1 row?in?set (0.00 sec)
?
注意：使用未定義變量不會產生任何語法錯誤，由于其被初始化為NULL值，如果沒有意識到這一點，非常容易犯錯。如下所示：
?
mysql> select @num1, @num2 :=@num1+1 from dual;
+-------+-----------------+
| @num1 | @num2 :=@num1+1 |
+-------+-----------------+
| NULL? |??????????? NULL |
+-------+-----------------+
1 row?in?set (0.00 sec)
?
mysql>
?
?
?
2：用戶變量名對大小寫不敏感（上面已經敘述，此處從略）
?
3：自定義變量的類型是一個動態類型。
?
MySQL中用戶自定義變量，不嚴格限制數據類型的，它的數據類型根據你賦給它的值而隨時變化。而且自定義變量如果賦予數字值，是不能保證進度的。官方文檔介紹：
?
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：賦值的順序和賦值的時間點并不總是固定的，這依賴于優化器的決定。
?
?
使用用戶自定義變量的一個最常見的問題就是沒有注意到在賦值和讀取用戶自定義變量的時候可能是在查詢的不同階段。例如，在SELECT語句中進行賦值然后再WHERE子句中讀取用戶自定義變量，則可能用戶自定義變量取值并不不是你所想象的那樣，如下例子所示，因為按照MySQL語句的執行順序，WHERE部分優先與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>
?
?
如上所示，第二次查詢可能你想要的邏輯跟實際邏輯已經出現了偏差，這個是使用自定義變量需要小心的地方。因為用戶自定義變量在當前會話中也算一個“全局變量”，它已經變成了6，where條件后面的 @rownum <= 5 邏輯為false了。一不小小心就會出現和你預想的結果出現偏差。
?
?
不要在同一個非SET語句中同時賦值并使用同一個用戶自定義變量，因為WHERE和SELECT是在查詢執行的不同階段被執行的。如果在查詢中再加入ORDER BY的話，結果可能會更不同；
?
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排序，那么又是正常的，那三者有啥區別呢？
?
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>
?
我們先看執行計劃
?
?
官方的解釋如下：
?
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語句中，每個選擇表達式僅在發送給客戶端時才被計算。 這意味著在HAVING，GROUP BY或ORDER BY子句中，引用在選擇表達式列表中指定值的用戶自定義變量不能按預期工作。 也就是說用戶自定義變量的值是在結果集發送到客戶端后才計算的
?
測試官方的例子：
?
?
?
?
?
這種解釋算是比較權威的，但是，讓人有點不解的是，SQL執行順序中WHERE在SELECT操作之前， 但是第一個SQL語句又怎么解釋呢？有種解釋是“MySQL優化器在某些場景下可能會將這些變量優化掉，這可能導致代碼不按預想的方式運行。” 解決這個問題的辦法是讓變量的賦值和取值發生在執行查詢的同一階段，如下所示：
?
?
?
?
?
關于用戶自定義變量，如果運用的好，能夠寫出高效簡潔的SQL語句，如果運用不當，也可能把自己給坑了。這個完全取決于使用它的人。
?
官方文檔也有介紹用戶自定義變量不適合使用場景。摘抄部分如下：
?
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語句塊之間。在該語句塊里設置的變量。declare語句專門用于定義聲明局部變量。
?
?
局部變量與用戶自定義變量的區分在于下面這些方面:
?
1.用戶自定義變量是以"@"開頭的。局部變量沒有這個符號。
?
2.定義變量方式不同。用戶自定義變量使用set語句，局部變量使用declare語句定義
?
3.作用范圍不同。局部變量只在begin-end語句塊之間有效。在begin-end語句塊運行完之后，局部變量就消失了。而用戶自定義變量是對當前連接（會話）有效。
?
?
?
?
?
?
?
參考資料：
?
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
