快捷導(dǎo)航

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

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

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

1 系統(tǒng)變量

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

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

SHOW GLOBAL VARIABLES;

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

@@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ù)運(yùn)行期間，使用 SET 命令重新設(shè)置系統(tǒng)變量的值。

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

更改全局變量，必須具有 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 語句也不影響）。也就是說，對于修改全局變量之前連接的客戶端只有在客戶端重新連接后，才會影響到客戶端。

客戶端連接時(shí)，當(dāng)前全局變量的值會對客戶端的會話變量進(jìn)行相應(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ù)庫的配置就又會重新初始化。一切按照配置文件進(jìn)行初始化，全局變量和會話變量的配置都會失效。

2 自定義變量

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

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

用戶自定義變量是一個(gè)用來存儲內(nèi)容的臨時(shí)容器，在連接MySQL的整個(gè)過程中都存在，可以使用下面的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 查看自定義變量

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

SELECT ... WHERE col <= @last_week;

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

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

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

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

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

自定義變量的類型是一個(gè)動(dòng)態(tài)類型。

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

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

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

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

2.3 自定義變量的運(yùn)用

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

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

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

這個(gè)例子的實(shí)際意義并不大，它只是實(shí)現(xiàn)了一個(gè)和該表主鍵一樣的列。不過，我們可以把這當(dāng)作一個(gè)排名?，F(xiàn)在我們來看一個(gè)更復(fù)雜的用法。我們先編寫一個(gè)查詢獲取演過最多電影的前10位演員，然后根據(jù)他們的出演電影次數(shù)做一個(gè)排名，如果出演的電影數(shù)量一樣，則排名相同。我們先編寫一個(gè)查詢，返回每個(gè)演員參演電影的數(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)在我們再把排名加上去，這里看到有四個(gè)演員都參演了35部電影，所以他們的排名應(yīng)該是相同的。我們使用三個(gè)變量來實(shí)現(xiàn)：一個(gè)用來記錄當(dāng)前的排名，一個(gè)用來記錄前一個(gè)演員的排名，還有一個(gè)用來記錄當(dāng)前演員參演的電影數(shù)量。只有當(dāng)前演員參演的電影的數(shù)量和前一個(gè)演員不同時(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我們看到將會使用臨時(shí)表和文件排序，所以可能是由于變量賦值的時(shí)間和我們預(yù)料的不同。

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

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

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ù)查詢，可以用變量巧妙的實(shí)現(xiàn)。例如，我們的一個(gè)客戶希望能夠更高效地更新一條記錄的時(shí)間戳，同時(shí)希望查詢當(dāng)前記錄中存放的時(shí)間戳是什么。簡單地，可以用下面的代碼來實(shí)現(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;

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

2.3.3統(tǒng)計(jì)更新和插入的數(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)致更新時(shí)對變量@x自增一次，然后表達(dá)式乘以0讓其不影響更新的內(nèi)容，另外，MySQL的協(xié)議會返回被更改的總行數(shù)，所以不需要單獨(dú)統(tǒng)計(jì)。

2.3.4確定取值的順序

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

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

因?yàn)閃HERE和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;

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

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

2.3.5 編寫偷懶的UNION

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

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

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

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;