酷播亮新聞
文章摘要： mysql> 如果要區分系統變數是全域性還是會話級別使用者自定義變數使用set語句
在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 :[email protected]+1 from dual; +-------+-----------------+ | @num1 | @num2 :[email protected]+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語句專門用於定義宣告區域性變數。
區域性變數與使用者自定義變數的區分在於下面這些方面:
- 使用者自定義變數是以」@」開頭的。區域性變數沒有這個符號。
- 定義變數方式不同。使用者自定義變數使用set語句，區域性變數使用declare語句定義
- 作用範圍不同。區域性變數只在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