快捷導(dǎo)航

MySQL系統(tǒng)變量和自定義變量的實現(xiàn)示例

更新時間：2025年12月23日 09:07:08 作者：吳名氏.

本文詳細介紹了MySQL中的系統(tǒng)變量,包括如何查看和設(shè)置全局及會話變量,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

1 系統(tǒng)變量

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

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

SHOW GLOBAL VARIABLES;

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

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

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

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

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

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

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

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

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

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

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

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

SET @@sort_buffer_size = 50000;

上面語句沒有指定是 GLOBAL 還是 SESSION，服務(wù)器會當(dāng)做 SESSION 處理。

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

2 自定義變量

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

2.1 設(shè)置自定義變量

用戶自定義變量是一個用來存儲內(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;

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

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

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

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

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

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

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

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

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

使用未定義變量不會產(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)了一個和該表主鍵一樣的列。不過，我們可以把這當(dāng)作一個排名。現(xiàn)在我們來看一個更復(fù)雜的用法。我們先編寫一個查詢獲取演過最多電影的前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部電影，所以他們的排名應(yīng)該是相同的。我們使用三個變量來實現(xiàn)：一個用來記錄當(dāng)前的排名，一個用來記錄前一個演員的排名，還有一個用來記錄當(dāng)前演員參演的電影數(shù)量。只有當(dāng)前演員參演的電影的數(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)跟我們設(shè)想的不太一樣。這里，通過EXPLAIN我們看到將會使用臨時表和文件排序，所以可能是由于變量賦值的時間和我們預(yù)料的不同。

使用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避免重復(fù)查詢剛剛更新的數(shù)據(jù)

如果在更新行的同學(xué)又希望獲得該行的信息，避免重復(fù)查詢，可以用變量巧妙的實現(xiàn)。例如，我們的一個客戶希望能夠更高效地更新一條記錄的時間戳，同時希望查詢當(dāng)前記錄中存放的時間戳是什么。簡單地，可以用下面的代碼來實現(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)絡(luò)來回，但是這里第二個查詢無需訪問數(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));

當(dāng)每次由于沖突導(dǎo)致更新時對變量@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

假設(shè)需要編寫一個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;