程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MYSQL入門知識 >> Mysql自定義變量的使用

Mysql自定義變量的使用

編輯：MYSQL入門知識

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

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

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

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

SELECT ... WHERE col <= @last_week;

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

使用自定義變量的查詢，無法使用查詢緩存
不能再使用常量或者標識符的地方使用自定義變量，例如表名、列名和LIMIT子句中。
用戶自定義變量的生命周期是在一個連接中有效，所以不能用它們來做連接間的通信。
如果使用連接池或者持久化連接，自定義變量可能讓看起來毫無關系的代碼發生交互。
自定義變量的類型是一個動態類型。
MySQL優化器在某些場景下可能會將這些變量優化掉，這可能導致代碼不按預想的方式運行。
賦值的順序和賦值的時間點並不總是固定的，這依賴於優化器的決定。
賦值符號 :=的優先級非常低，所以需要注意，賦值表達式應該使用明確的括號。
使用未定義變量不會產生任何語法錯誤，如果沒有意識到這一點，非常容易犯錯。

優化排名語句

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

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS rownum
FROM actor order by actor_id LIMIT 3;
    +----------+--------+
    | actor_id | rownum |
    +----------+--------+
    |        1 |      1 |
    |        2 |      2 |
    |        3 |      3 |
    +----------+--------+

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


mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
mysql> SELECT actor_id, COUNT(*) as cnt
    -> FROM film_actor
    -> GROUP BY actor_id
    -> ORDER BY cnt DESC
    -> LIMIT 10;
    +----------+-----+
    | actor_id | cnt |
    +----------+-----+
    |      107 |  42 |
    |      102 |  41 |
    |      198 |  40 |
    |      181 |  39 |
    |       23 |  37 |
    |       81 |  36 |
    |       37 |  35 |
    |      106 |  35 |
    |       60 |  35 |
    |       13 |  35 |
    +----------+-----+

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


mysql> 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;
    +----------+-----+------+-------+
    | actor_id | cnt | rank | dummy |
    +----------+-----+------+-------+
    |      107 |  42 |    0 |     0 |
    |      102 |  41 |    0 |     0 |
    |      198 |  40 |    0 |     0 |
    |      181 |  39 |    0 |     0 |
    |       23 |  37 |    0 |     0 |
    |       81 |  36 |    0 |     0 |
    |      106 |  35 |    0 |     0 |
    |       60 |  35 |    0 |     0 |
    |       13 |  35 |    0 |     0 |
    |       37 |  35 |    0 |     0 |
    +----------+-----+------+-------+

我們發現跟我們設想的不太一樣。這裡，通過EXPLAIN我們看到將會使用臨時表和文件排序，所以可能是由於變量賦值的時間和我們預料的不同。
使用SQL語句生成排名值通常需要做兩次計算，例如，需要額外計算一次出演過相同數量電影的演員有哪些。使用變量則可一次完成---這對性能是一個很大的提升。
針對這個案例，另一個簡單的方案是在FROM子句中使用子查詢生成的一個中間的臨時表：


mysql> 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;
+----------+-----+------+-------+
| actor_id | cnt | rank | dummy |
+----------+-----+------+-------+
|      107 |  42 |    1 |    42 |
|      102 |  41 |    2 |    41 |
|      198 |  40 |    3 |    40 |
|      181 |  39 |    4 |    39 |
|       23 |  37 |    5 |    37 |
|       81 |  36 |    6 |    36 |
|       37 |  35 |    7 |    35 |
|      106 |  35 |    7 |    35 |
|       60 |  35 |    7 |    35 |
|       13 |  35 |    7 |    35 |
+----------+-----+------+-------+

避免重復查詢剛剛更新的數據

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


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;

上面看起來仍然需要兩個查詢，需要兩次網絡來回，但是這裡第二個查詢無需訪問數據表，所以會快很多。

統計更新和插入的數量


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讓其不影響更新的內容，另外，MySQL的協議會返回被更改的總行數，所以不需要單獨統計。

確定取值的順序

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


mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
    -> FROM actor
    -> WHERE @rownum <= 1;
+----------+------+
| actor_id | cnt  |
+----------+------+
|       58 |    1 |
|       92 |    2 |
+----------+------+

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


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

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


mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum AS rownum
    -> FROM actor
    -> WHERE (@rownum := @rownum + 1) <= 1;
+----------+--------+
| actor_id | rownum |
+----------+--------+
|       58 |      1 |
+----------+--------+

編寫偷懶的UNION

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


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

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


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;