前言

之前在項(xiàng)目的存儲(chǔ)過(guò)程中發(fā)現(xiàn)有通過(guò) DECLARE 關(guān)鍵字定義的變量如DECLARE cnt INT DEFAULT 0;，還有形如 @count 這樣的變量，存儲(chǔ)過(guò)程中拿過(guò)來(lái)直接就進(jìn)行設(shè)置，像這樣set @count=1;，這兩種類(lèi)型的變量究竟有什么區(qū)別卻弄不清楚，趕緊上網(wǎng)查詢資料，發(fā)現(xiàn)還有@@sql_mode這樣的變量，這一個(gè)圈倆圈的到底是什么??？會(huì)不會(huì)出現(xiàn)三個(gè)圈的情況？

變量分類(lèi)與關(guān)系

經(jīng)過(guò)一段時(shí)間學(xué)習(xí)和測(cè)試，再配合官方的文檔，現(xiàn)在大致弄清楚了這些變量的區(qū)別，一般可以將MySQL中的變量分為全局變量、會(huì)話變量、用戶變量和局部變量，這是很常見(jiàn)的分類(lèi)方法，這些變量的作用是什么呢？可以從前往后依次看一下。

首先我們知道MySQL服務(wù)器維護(hù)了許多系統(tǒng)變量來(lái)控制其運(yùn)行的行為，這些變量有些是默認(rèn)編譯到軟件中的，有些是可以通過(guò)外部配置文件來(lái)配置覆蓋的，如果想查詢自編譯的內(nèi)置變量和從文件中可以讀取覆蓋的變量可以通過(guò)以下命令來(lái)查詢:

mysqld --verbose --help

如果想只看自編譯的內(nèi)置變量可以使用命令：

mysqld --no-defaults --verbose --help

接下來(lái)簡(jiǎn)單了解一下這幾類(lèi)變量的應(yīng)用范圍，首先MySQL服務(wù)器啟動(dòng)時(shí)會(huì)使用其軟件內(nèi)置的變量（俗稱(chēng)寫(xiě)死在代碼中的）和配置文件中的變量（如果允許，是可以覆蓋源代碼中的默認(rèn)值的）來(lái)初始化整個(gè)MySQL服務(wù)器的運(yùn)行環(huán)境，這些變量通常就是我們所說(shuō)的全局變量，這些在內(nèi)存中的全局變量有些是可以修改的。

當(dāng)有客戶端連接到MySQL服務(wù)器的時(shí)候，MySQL服務(wù)器會(huì)將這些全局變量的大部分復(fù)制一份作為這個(gè)連接客戶端的會(huì)話變量，這些會(huì)話變量與客戶端連接綁定，連接的客戶端可以修改其中允許修改的變量，但是當(dāng)連接斷開(kāi)時(shí)這些會(huì)話變量全部消失，重新連接時(shí)會(huì)從全局變量中重新復(fù)制一份。

其實(shí)與連接相關(guān)的變量不只有會(huì)話變量一種，用戶變量也是這樣的，用戶變量其實(shí)就是用戶自定義變量，當(dāng)客戶端連接上MySQL服務(wù)器之后就可以自己定義一些變量，這些變量在整個(gè)連接過(guò)程中有效，當(dāng)連接斷開(kāi)時(shí)，這些用戶變量消失。

局部變量實(shí)際上最好理解，通常由DECLARE 關(guān)鍵字來(lái)定義，經(jīng)常出現(xiàn)在存儲(chǔ)過(guò)程中，非常類(lèi)似于C和C++函數(shù)中的局部變量，而存儲(chǔ)過(guò)程的參數(shù)也和這種變量非常相似，基本上可以作為同一種變量來(lái)對(duì)待。

變量的修改

先說(shuō)全局變量有很多是可以動(dòng)態(tài)調(diào)整的，也就是說(shuō)可以在MySQL服務(wù)器運(yùn)行期間通過(guò) SET 命令修改全局變量，而不需要重新啟動(dòng) MySQL 服務(wù)，但是這種方法在修改大部分變量的時(shí)候都需要超級(jí)權(quán)限，比如root賬戶。

相比之下會(huì)話對(duì)變量修改的要求要低的多，因?yàn)樾薷臅?huì)話變量通常只會(huì)影響當(dāng)前連接，但是有個(gè)別一些變量是例外的，修改它們也需要較高的權(quán)限，比如 binlog_format 和 sql_log_bin，因?yàn)樵O(shè)置這些變量的值將影響當(dāng)前會(huì)話的二進(jìn)制日志記錄，也有可能對(duì)服務(wù)器復(fù)制和備份的完整性產(chǎn)生更廣泛的影響。

至于用戶變量和局部變量，聽(tīng)名字就知道，這些變量的生殺大權(quán)完全掌握在自己手中，想改就改，完全不需要理會(huì)什么權(quán)限，它的定義和使用全都由用戶自己掌握。

測(cè)試環(huán)境

以下給出MySQL的版本，同時(shí)使用root用戶測(cè)試，這樣可以避免一些權(quán)限問(wèn)題。

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

變量查詢與設(shè)置

全局變量

這些變量來(lái)源于軟件自編譯、配置文件中、以及啟動(dòng)參數(shù)中指定的變量，其中大部分是可以由root用戶通過(guò) SET 命令直接在運(yùn)行時(shí)來(lái)修改的，一旦 MySQL 服務(wù)器重新啟動(dòng)，所有修改都被還原。

如果修改了配置文件，想恢復(fù)最初的設(shè)置，只需要將配置文件還原，重新啟動(dòng) MySQL 服務(wù)器，一切都可以恢復(fù)原來(lái)的樣子。

查詢

查詢所有的全局變量：

show global variables;

一般不會(huì)這么用，這樣查簡(jiǎn)直太多了，大概有500多個(gè)，通常會(huì)加個(gè)like控制過(guò)濾條件：

mysql> show global variables like 'sql%';
+------------------------+----------------------------------------------------------------+
| Variable_name          | Value                                                          |
+------------------------+----------------------------------------------------------------+
| sql_auto_is_null       | OFF                                                            |
| sql_big_selects        | ON                                                             |
| sql_buffer_result      | OFF                                                            |
| sql_log_off            | OFF                                                            |
| sql_mode               | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| sql_notes              | ON                                                             |
| sql_quote_show_create  | ON                                                             |
| sql_safe_updates       | OFF                                                            |
| sql_select_limit       | 18446744073709551615                                           |
| sql_slave_skip_counter | 0                                                              |
| sql_warnings           | OFF                                                            |
+------------------------+----------------------------------------------------------------+
11 rows in set, 1 warning (0.00 sec)

mysql>

還有一種查詢方法就是通過(guò)select語(yǔ)句：

select @@global.sql_mode;

當(dāng)一個(gè)全局變量不存在會(huì)話變量副本時(shí)也可以這樣

select @@max_connections;

設(shè)置

設(shè)置全局變量也有兩種方式：

set global sql_mode='';

或者

set @@global.sql_mode='';

會(huì)話變量

這些變量基本來(lái)自于全局變量的復(fù)制，與客戶端連接有關(guān)，無(wú)論怎樣修改，當(dāng)連接斷開(kāi)后，一切都會(huì)還原，下次連接時(shí)又是一次新的開(kāi)始。

查詢

類(lèi)比全局變量，會(huì)話變量也有類(lèi)似的查詢方式，查詢所有會(huì)話變量

show session variables;

添加查詢匹配，只查一部分會(huì)話變量：

show session variables like 'sql%';

查詢特定的會(huì)話變量，以下三種都可以：

select @@session.sql_mode;
select @@local.sql_mode;
select @@sql_mode;

設(shè)置

會(huì)話變量的設(shè)置方法是最多的，以下的方式都可以：

set session sql_mode = '';
set local sql_mode = '';
set @@session.sql_mode = '';
set @@local.sql_mode = '';
set @@sql_mode = '';
set sql_mode = '';

用戶變量

用戶變量就是用戶自己定義的變量，也是在連接斷開(kāi)時(shí)失效，定義和使用相比會(huì)話變量來(lái)說(shuō)簡(jiǎn)單許多。

查詢

直接一個(gè)select語(yǔ)句就可以了：

select @count;

設(shè)置

設(shè)置也相對(duì)簡(jiǎn)單，可以直接使用set命令：

set @count=1;
set @sum:=0;

也可以使用select into語(yǔ)句來(lái)設(shè)置值，比如：

select count(id) into @count from items where price < 99;

局部變量

局部變量通常出現(xiàn)在存儲(chǔ)過(guò)程中，用于中間計(jì)算結(jié)果，交換數(shù)據(jù)等等，當(dāng)存儲(chǔ)過(guò)程執(zhí)行完，變量的生命周期也就結(jié)束了。

查詢

也是使用select語(yǔ)句：

declare count int(4);
select count;

設(shè)置

與用戶變量非常類(lèi)似：

declare count int(4);
declare sum int(4);
set count=1;
set sum:=0;

也可以使用select into語(yǔ)句來(lái)設(shè)置值，比如：

declare count int(4);
select count(id) into count from items where price < 99;

其實(shí)還有一種存儲(chǔ)過(guò)程參數(shù)，也就是C/C++中常說(shuō)的形參，使用方法與局部變量基本一致，就當(dāng)成局部變量來(lái)用就可以了

幾種變量的對(duì)比使用

相信看了這個(gè)對(duì)比的表格，之前的很多疑惑就應(yīng)該清楚了，如果發(fā)現(xiàn)其中有什么疑惑的地方可以給我留言，或者發(fā)現(xiàn)有什么錯(cuò)誤也可以一針見(jiàn)血的指出來(lái)，我會(huì)盡快改正的。

總結(jié)

1. MySQL 中的變量通常分為：全局變量、 會(huì)話變量、 用戶變量、 局部變量
2. 其實(shí)還有一個(gè)存儲(chǔ)過(guò)程和函數(shù)的參數(shù)，這種類(lèi)型和局部變量基本一致，當(dāng)成局部變量來(lái)使用就行了
3. 在表格中有一個(gè)容易疑惑的點(diǎn)就是無(wú)論是全局變量還是會(huì)話變量都有select@@變量名的形式。
4. select@@變量名這種形式默認(rèn)取的是會(huì)話變量，如果查詢的會(huì)話變量不存在就會(huì)獲取全局變量，比如@@max_connections
5. 但是SET操作的時(shí)候，set @@變量名=xxx 總是操作的會(huì)話變量，如果會(huì)話變量不存在就會(huì)報(bào)錯(cuò)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論