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），那么到底這個(gè)叫系統(tǒng)參數(shù)或系統(tǒng)變量（system variables）呢？ 這個(gè)曾經(jīng)是一個(gè)讓我很糾結(jié)的問題，因?yàn)镸ySQL中有各種類型的變量，有時(shí)候語言就是這么博大精深；相信很多人也對(duì)這個(gè)問題或多或少有點(diǎn)困惑。其實(shí)拋開這些名詞，它們就是同一個(gè)事情（東西），不管你叫它系統(tǒng)變量（system variables）或系統(tǒng)參數(shù)都可，無需那么糾結(jié)。 就好比王三，有人叫他王三；也有人也叫他王麻子綽號(hào)一樣。
另外，MySQL中有很多變量類型，確實(shí)有時(shí)候讓人有點(diǎn)混淆不清，本文打算總結(jié)一下MySQL數(shù)據(jù)庫的各種變量類型，理清各種變量類型概念。能夠從全局有個(gè)清晰思路。MySQL變量類型具體參考下圖：
Server System Variables（系統(tǒng)變量）
MySQL系統(tǒng)變量(system variables)是指MySQL實(shí)例的各種系統(tǒng)變量，實(shí)際上是一些系統(tǒng)參數(shù)，用于初始化或設(shè)定數(shù)據(jù)庫對(duì)系統(tǒng)資源的占用，文件存放位置等等，這些變量包含MySQL編譯時(shí)的參數(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）按作用域范圍可以分為會(huì)話級(jí)別系統(tǒng)變量和全局級(jí)別系統(tǒng)變量。如果要確認(rèn)系統(tǒng)變量是全局級(jí)別還是會(huì)話級(jí)別，可以參考官方文檔，如果Scope其值為GLOBAL或SESSION，表示變量既是全局級(jí)別系統(tǒng)變量，又是會(huì)話級(jí)別系統(tǒng)變量。如果其Scope其值為GLOBAL，表示系統(tǒng)變量為全局級(jí)別系統(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)前會(huì)話值
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%';
注意：對(duì)于SHOW VARIABLES，如果不指定GLOBAL、SESSION或者LOCAL，MySQL返回SESSION值，如果要區(qū)分系統(tǒng)變量是全局還是會(huì)話級(jí)別。不能使用下面方式，如果某一個(gè)系統(tǒng)變量是全局級(jí)別的，那么在當(dāng)前會(huì)話的值也是全局級(jí)別的值。例如系統(tǒng)變量AUTOMATIC_SP_PRIVILEGES，它是一個(gè)全局級(jí)別系統(tǒng)變量，但是 show session variables like ‘%automatic_sp_privileges%’一樣能查到其值。所以這種方式無法區(qū)別系統(tǒng)變量是會(huì)話級(jí)別還是全局級(jí)別。
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)變量是全局還是會(huì)話級(jí)別，可以用下面方式：
- 方法1： 查官方文檔中系統(tǒng)變量的Scope屬性。
- 方法2： 使用SET VARIABLE_NAME=xxx; 如果報(bào)ERROR 1229 (HY000)，則表示該變量為全局，如果不報(bào)錯(cuò)，那么證明該系統(tǒng)變量為全局和會(huì)話兩個(gè)級(jí)別。
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)變量的值，如下所示：
修改全局級(jí)別系統(tǒng)變量：
SET GLOBAL max_connections=300; SET @@global.max_connections=300;
注意：更改全局變量的值，需要擁有SUPER權(quán)限
修改會(huì)話級(jí)別系統(tǒng)變量：
SET @@session.max_join_size=DEFAULT; SET max_join_size=DEFAULT; --默認(rèn)為會(huì)話變量。如果在變量名前沒有級(jí)別限定符，表示修改會(huì)話級(jí)變量。 SET SESSION max_join_size=DEFAULT;
如果修改系統(tǒng)全局變量沒有指定GLOBAL或@@global的話，就會(huì)報(bào)“Variable ‘xxx’ is a GLOBAL variable and should be set with SET GLOBAL”這類錯(cuò)誤。
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）按是否可以動(dòng)態(tài)修改，可以分為系統(tǒng)動(dòng)態(tài)變量(Dynamic System Variables)和系統(tǒng)靜態(tài)變量。怎么區(qū)分系統(tǒng)變量是動(dòng)態(tài)和靜態(tài)的呢？ 這個(gè)只能查看官方文檔，系統(tǒng)變量的”Dynamic”屬性為Yes，則表示可以動(dòng)態(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>
另外，還有一個(gè)Structured System Variables概念，其實(shí)就是系統(tǒng)變量是一個(gè)結(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ù)器從啟動(dòng)后累計(jì)的一些系統(tǒng)狀態(tài)信息，例如最大連接數(shù)，累計(jì)的中斷連接等等，主要用于評(píng)估當(dāng)前系統(tǒng)資源的使用情況以進(jìn)一步分析系統(tǒng)性能而做出相應(yīng)的調(diào)整決策。這個(gè)估計(jì)有人會(huì)跟系統(tǒng)變量混淆，其實(shí)狀態(tài)變量是動(dòng)態(tài)變化的，另外，狀態(tài)變量是只讀的：只能由MySQL服務(wù)器本身設(shè)置和修改，對(duì)于用戶來說是只讀的，不可以通過SET語句設(shè)置和修改它們，而系統(tǒng)變量則可以隨時(shí)修改。狀態(tài)變量也分為會(huì)話級(jí)與全局級(jí)別狀態(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)前測(cè)試環(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兩個(gè)表做兼容，如果希望沿用information_schema中進(jìn)行查詢的習(xí)慣，5.7提供了show_compatibility_56參數(shù)，設(shè)置為ON可以兼容5.7之前的用法，否則就會(huì)報(bào)錯(cuò)（ERROR 3167 (HY000)）.
User-Defined Variables（用戶自定義變量）
用戶自定義變量，顧名思義就是用戶自己定義的變量。用戶自定義變量是基于當(dāng)前會(huì)話的。 也就是說用戶自定義變量的作用域局限于當(dāng)前會(huì)話（連接），由一個(gè)客戶端定義的用戶自定義變量不能被其他客戶端看到或使用。（例外：可以訪問performance_schema.user_variables_by_thread表的用戶可以看到所有會(huì)話的定義的用戶自定義變量，當(dāng)然僅僅能看到那些會(huì)話定義了哪些變量，而不能訪問這些變量。）。當(dāng)客戶端會(huì)話退出時(shí)，當(dāng)前會(huì)話所有的自定義變量都會(huì)自動(dòng)釋放。
一般可以在SQL語句將值存儲(chǔ)在用戶自定義變量中，然后再利用另一條SQL語句來查詢用戶自定義變量。這樣以來，可以在不同的SQL間傳遞值。
另外，用戶自定義變量是大小寫不敏感的，最大長(zhǎng)度為64個(gè)字符，用戶自定義變量的形式一般為@var_name，其中變量名稱由字母、數(shù)字、“.”、“_”和“$”組成。當(dāng)然，在以字符串或者標(biāo)識(shí)符引用時(shí)也可以包含其他特殊字符（例如：@’my-var’，@”my-var”，或者@`my-var`）。。使用SET設(shè)置變量時(shí)，可以使用“=”或者“:=”操作符進(jìn)行賦值。對(duì)于SET，可以使用=或:=來賦值，對(duì)于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>
用戶自定義變量注意事項(xiàng)，以下為總結(jié)：
總結(jié)
1 未定義的用戶自定義變量初始值是NULL
mysql> select @kerry from dual; +--------+ | @kerry | +--------+ | NULL | +--------+ 1 row in set (0.00 sec)
注意：使用未定義變量不會(huì)產(chǎn)生任何語法錯(cuò)誤，由于其被初始化為NULL值，如果沒有意識(shí)到這一點(diǎn)，非常容易犯錯(cuò)。如下所示：
mysql> select @num1, @num2 :=@num1+1 from dual; +-------+-----------------+ | @num1 | @num2 :=@num1+1 | +-------+-----------------+ | NULL | NULL | +-------+-----------------+ 1 row in set (0.00 sec) mysql>
2 用戶變量名對(duì)大小寫不敏感（上面已經(jīng)敘述，此處從略）
3 自定義變量的類型是一個(gè)動(dòng)態(tài)類型
MySQL中用戶自定義變量，不嚴(yán)格限制數(shù)據(jù)類型的，它的數(shù)據(jù)類型根據(jù)你賦給它的值而隨時(shí)變化。而且自定義變量如果賦予數(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 賦值的順序和賦值的時(shí)間點(diǎn)并不總是固定的，這依賴于優(yōu)化器的決定
使用用戶自定義變量的一個(gè)最常見的問題就是沒有注意到在賦值和讀取用戶自定義變量的時(shí)候可能是在查詢的不同階段。例如，在SELECT語句中進(jìn)行賦值然后再WHERE子句中讀取用戶自定義變量，則可能用戶自定義變量取值并不不是你所想象的那樣，如下例子所示，因?yàn)榘凑誐ySQL語句的執(zhí)行順序，WHERE部分優(yōu)先與SELECT部分操作，所以你會(huì)看到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>
如上所示，第二次查詢可能你想要的邏輯跟實(shí)際邏輯已經(jīng)出現(xiàn)了偏差，這個(gè)是使用自定義變量需要小心的地方。因?yàn)橛脩糇远x變量在當(dāng)前會(huì)話中也算一個(gè)“全局變量”，它已經(jīng)變成了6，where條件后面的 @rownum <= 5 邏輯為false了。一不小小心就會(huì)出現(xiàn)和你預(yù)想的結(jié)果出現(xiàn)偏差。
不要在同一個(gè)非SET語句中同時(shí)賦值并使用同一個(gè)用戶自定義變量，因?yàn)閃HERE和SELECT是在查詢執(zhí)行的不同階段被執(zhí)行的。如果在查詢中再加入ORDER BY的話，結(jié)果可能會(huì)更不同；
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í)行計(jì)劃
官方的解釋如下：
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語句中，每個(gè)選擇表達(dá)式僅在發(fā)送給客戶端時(shí)才被計(jì)算。 這意味著在HAVING，GROUP BY或ORDER BY子句中，引用在選擇表達(dá)式列表中指定值的用戶自定義變量不能按預(yù)期工作。 也就是說用戶自定義變量的值是在結(jié)果集發(fā)送到客戶端后才計(jì)算的
測(cè)試官方的例子：
這種解釋算是比較權(quán)威的，但是，讓人有點(diǎn)不解的是，SQL執(zhí)行順序中WHERE在SELECT操作之前， 但是第一個(gè)SQL語句又怎么解釋呢？有種解釋是“MySQL優(yōu)化器在某些場(chǎng)景下可能會(huì)將這些變量?jī)?yōu)化掉，這可能導(dǎo)致代碼不按預(yù)想的方式運(yùn)行。” 解決這個(gè)問題的辦法是讓變量的賦值和取值發(fā)生在執(zhí)行查詢的同一階段，如下所示：
關(guān)于用戶自定義變量，如果運(yùn)用的好，能夠?qū)懗龈咝Ш?jiǎn)潔的SQL語句，如果運(yùn)用不當(dāng)，也可能把自己給坑了。這個(gè)完全取決于使用它的人。
官方文檔也有介紹用戶自定義變量不適合使用場(chǎ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ū)分在于下面這些方面:
- 用戶自定義變量是以”@”開頭的。局部變量沒有這個(gè)符號(hào)。
- 定義變量方式不同。用戶自定義變量使用set語句，局部變量使用declare語句定義
- 作用范圍不同。局部變量只在begin-end語句塊之間有效。在begin-end語句塊運(yùn)行完之后，局部變量就消失了。而用戶自定義變量是對(duì)當(dāng)前連接（會(huì)話）有效。
參考資料
- 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)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。
