1 系統(tǒng)變量

1.1 查看系統(tǒng)變量

可以使用以下命令查看 MySQL 中所有的全局變量信息。

SHOW GLOBAL VARIABLES; 

MySQL 中的系統(tǒng)變量以兩個“@”開頭。

• @@global 僅僅用于標記全局變量；
• @@session 僅僅用于標記會話變量；
• @@首先標記會話變量，如果會話變量不存在，則標記全局變量。

1.2 設置系統(tǒng)變量

可以通過以下方法設置系統(tǒng)變量：

• 修改 MySQL 源代碼，然后對 MySQL 源代碼重新編譯（該方法適用于 MySQL 高級用戶，這里不做闡述）。
• 在 MySQL 配置文件（mysql.ini 或 mysql.cnf）中修改 MySQL 系統(tǒng)變量的值（需要重啟 MySQL 服務才會生效）。
• 在 MySQL 服務運行期間，使用 SET 命令重新設置系統(tǒng)變量的值。

服務器啟動時，會將所有的全局變量賦予默認值。這些默認值可以在選項文件中或在命令行中對執(zhí)行的選項進行更改。

更改全局變量，必須具有 SUPER 權(quán)限。設置全局變量的值的方法如下：

• SET @@global.innodb_file_per_table=default;
• SET @@global.innodb_file_per_table=ON;
• SET global innodb_file_per_table=ON;

需要注意的是，更改全局變量只影響更改后連接客戶端的相應會話變量，而不會影響目前已經(jīng)連接的客戶端的會話變量（即使客戶端執(zhí)行 SET GLOBAL 語句也不影響）。也就是說，對于修改全局變量之前連接的客戶端只有在客戶端重新連接后，才會影響到客戶端。

客戶端連接時，當前全局變量的值會對客戶端的會話變量進行相應初始化。設置會話變量不需要特殊權(quán)限，但客戶端只能更改自己的會話變量，而不能更改其它客戶端的會話變量。設置會話變量的值的方法如下：

• SET @@session.pseudo_thread_id=5;
• SET session pseudo_thread_id=5;
• SET @@pseudo_thread_id=5;
• SET pseudo_thread_id = 5;

如果沒有指定修改全局變量還是會話變量，服務器會當作會話變量來處理。比如：

SET @@sort_buffer_size = 50000;

上面語句沒有指定是 GLOBAL 還是 SESSION，服務器會當做 SESSION 處理。

使用 SET 設置全局變量或會話變量成功后，如果 MySQL 服務重啟，數(shù)據(jù)庫的配置就又會重新初始化。一切按照配置文件進行初始化，全局變量和會話變量的配置都會失效。

2 自定義變量

用戶自定義變量是一個容易被遺忘的MySQL特性，但是如果能用的好，發(fā)揮其潛力，在某些場景可以寫出非常高效的查詢語句。在查詢中混合使用過程化和關系化邏輯的時候，自定義變量可能會非常有用。單純的關系查詢將所有的東西都當成無序的數(shù)據(jù)集合，并且一次性操作它們。MySQL則采用了更加程序化的處理方式。MySQL的這種方式有它的弱點，但如果能夠熟練地掌握，則會發(fā)現(xiàn)其強大之處，而用戶自定義變量也可以給這種方式帶來很大的幫助

2.1 設置自定義變量

用戶自定義變量是一個用來存儲內(nèi)容的臨時容器，在連接MySQL的整個過程中都存在，可以使用下面的SET和SELECT語句來定義它們：

SET @one := 1;
SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);
SET @last_week := CURRENT_DATE - INTERVAL 1 WEEK;

2.2 查看自定義變量

然后可以在任何可以使用表達式的地方使用這些自定義變量：

SELECT ... WHERE col <= @last_week;

在了解自定義變量的強大之前，我們先來看看它自身的一些屬性和限制，看看在哪些場景下我們不能使用用戶自定義變量：

使用自定義變量的查詢，無法使用查詢緩存

不能再使用常量或者標識符的地方使用自定義變量，例如表名、列名和LIMIT子句中。

用戶自定義變量的生命周期是在一個連接中有效，所以不能用它們來做連接間的通信。

如果使用連接池或者持久化連接，自定義變量可能讓看起來毫無關系的代碼發(fā)生交互。

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

MySQL優(yōu)化器在某些場景下可能會將這些變量優(yōu)化掉，這可能導致代碼不按預想的方式運行。

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

賦值符號 :=的優(yōu)先級非常低，所以需要注意，賦值表達式應該使用明確的括號。

使用未定義變量不會產(chǎn)生任何語法錯誤，如果沒有意識到這一點，非常容易犯錯。

2.3 自定義變量的運用

2.3.1優(yōu)化排名語句

使用自定義變量的一個特性是你可以在給一個變量賦值的同時使用這個變量，即“左值”特性。例如：

SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 AS rownum
FROM actor order by actor_id LIMIT 3;

這個例子的實際意義并不大，它只是實現(xiàn)了一個和該表主鍵一樣的列。不過，我們可以把這當作一個排名?，F(xiàn)在我們來看一個更復雜的用法。我們先編寫一個查詢獲取演過最多電影的前10位演員，然后根據(jù)他們的出演電影次數(shù)做一個排名，如果出演的電影數(shù)量一樣，則排名相同。我們先編寫一個查詢，返回每個演員參演電影的數(shù)量。

SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
SELECT actor_id, COUNT(*) as cnt
FROM film_actor
GROUP BY actor_id
ORDER BY cnt DESC
LIMIT 10;

現(xiàn)在我們再把排名加上去，這里看到有四個演員都參演了35部電影，所以他們的排名應該是相同的。我們使用三個變量來實現(xiàn)：一個用來記錄當前的排名，一個用來記錄前一個演員的排名，還有一個用來記錄當前演員參演的電影數(shù)量。只有當前演員參演的電影的數(shù)量和前一個演員不同時，排名才變化。我們試試下面的寫法：

SELECT actor_id,
@curr_cnt := COUNT(*) AS cnt,
@rank     := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
@prev_cnt := @curr_cnt AS dummy
FROM film_actor
GROUP BY actor_id
ORDER BY cnt DESC
LIMIT 10;

我們發(fā)現(xiàn)跟我們設想的不太一樣。這里，通過EXPLAIN我們看到將會使用臨時表和文件排序，所以可能是由于變量賦值的時間和我們預料的不同。

使用SQL語句生成排名值通常需要做兩次計算，例如，需要額外計算一次出演過相同數(shù)量電影的演員有哪些。使用變量則可一次完成---這對性能是一個很大的提升。

針對這個案例，另一個簡單的方案是在FROM子句中使用子查詢生成的一個中間的臨時表：

SELECT actor_id,
@curr_cnt := cnt AS cnt,
@rank     := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
@prev_cnt := @curr_cnt AS dummy
FROM (
SELECT actor_id, COUNT(*) AS cnt
FROM film_actor
GROUP BY actor_id
ORDER BY cnt DESC
LIMIT 10
) as der;

2.3.2避免重復查詢剛剛更新的數(shù)據(jù)

如果在更新行的同學又希望獲得該行的信息，避免重復查詢，可以用變量巧妙的實現(xiàn)。例如，我們的一個客戶希望能夠更高效地更新一條記錄的時間戳，同時希望查詢當前記錄中存放的時間戳是什么。簡單地，可以用下面的代碼來實現(xiàn)：

UPDATE t1 SET lastUpdated = NOW() WHERE id = 1;
SELECT lastUpdated FROM t1 WHERE id = 1;

使用變量，我們可以按如下方式重寫查詢:

UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now;

上面看起來仍然需要兩個查詢，需要兩次網(wǎng)絡來回，但是這里第二個查詢無需訪問數(shù)據(jù)表，所以會快很多。

2.3.3統(tǒng)計更新和插入的數(shù)量

INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1)
ON DUPLICATE KEY UPDATE
    c1 = VALUES(c1) + (0 * (@x := @x + 1));

當每次由于沖突導致更新時對變量@x自增一次，然后表達式乘以0讓其不影響更新的內(nèi)容，另外，MySQL的協(xié)議會返回被更改的總行數(shù)，所以不需要單獨統(tǒng)計。

2.3.4確定取值的順序

使用用戶自定義變量的一個最常見的問題就是沒有注意到在賦值和讀取變量的時候可能是在查詢的不同階段。例如，在SELECT子句中進行賦值然后再WHERE子句中讀取變量，則可能變量取值并不如你所想：

SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 AS cnt
FROM actor
WHERE @rownum <= 1;

因為WHERE和SELECT是在查詢執(zhí)行的不同階段被執(zhí)行的。如果在查詢中再加入ORDER BY的話，結(jié)果可能會更不同；

SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 AS cnt
FROM actor
WHERE @rownum <= 1
ORDER BY first_name;

這是因為ORDER BY 引入了文件排序，而WHERE條件是在文件排序操作之前取值的，所以這條查詢會返回表中的全部記錄。解決這個問題的辦法是讓變量的賦值和取值發(fā)生在執(zhí)行查詢的同一階段：

SET @rownum := 0;
SELECT actor_id, @rownum AS rownum
FROM actor
WHERE (@rownum := @rownum + 1) <= 1;

2.3.5 編寫偷懶的UNION

假設需要編寫一個UNION查詢，其第一個子查詢作為分支條件先執(zhí)行，如果找到了匹配的行，則跳過第二個分支。例如先在一個頻繁訪問的表查找熱數(shù)據(jù)，找不到再去另外一個較少訪問的表查找冷數(shù)據(jù)。

SELECT id FROM users WHERE id = 123;
UNION ALL
SELECT id FROM users_archived WHERE id = 123;

上面的查詢可以工作，但是無論第一個表找沒找到，都會在第二個表再找一次，如果使用變量的話可以很好地規(guī)避這個問題。

SELECT GREATEST(@found := -1, id) AS id, 'users' AS which_tbl
FROM users WHERE id = 1
UNION ALL
    SELECT id, 'users_archived'
    FROM users_archived WHERE id = 1 AND @found IS NULL
UNION ALL   
    SELECT 1, 'reset' FROM DUAL WHERE (@found := NULL) IS NOT NULL;

2.3.6用戶自定義變量的其他用處

通過一些實踐，可以了解所有用戶自定義變量能夠做的有趣的事情，例如下面這些用法：

• 查詢運行時計算總數(shù)和平均值
• 模擬GROUP語句中的函數(shù)FIRST()和LAST()
• S對大量數(shù)據(jù)做一些數(shù)據(jù)計算
• 計算一個大表的MD5散列值
• 編寫一個樣本處理函數(shù)
• 模擬讀/寫游標
• 在SHOW語句的WHERE子句中加入變量值

到此這篇關于MySQL系統(tǒng)變量和自定義變量的實現(xiàn)示例的文章就介紹到這了,更多相關MySQL系統(tǒng)變量和自定義變量內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！