Mysql自定義變量的使用

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

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

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

<code>SELECT ... WHERE col &lt;= @last_week;</code>

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

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

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

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

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

自定義變量的類型是一個動态類型。

MySQL優化器在某些場景下可能會将這些變量優化掉，這可能導緻代碼不按預想的方式運作。

指派的順序和指派的時間點并不總是固定的，這依賴于優化器的決定。

指派符号 :=的優先級非常低，是以需要注意，指派表達式應該使用明确的括号。

使用未定義變量不會産生任何文法錯誤，如果沒有意識到這一點，非常容易犯錯。

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

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

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

我們發現跟我們設想的不太一樣。這裡，通過EXPLAIN我們看到将會使用臨時表和檔案排序，是以可能是由于變量指派的時間和我們預料的不同。

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

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

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

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

上面看起來仍然需要兩個查詢，需要兩次網絡來回，但是這裡第二個查詢無需通路資料表，是以會快很多。

當每次由于沖突導緻更新時對變量@x自增一次，然後表達式乘以0讓其不影響更新的内容，另外，MySQL的協定會傳回被更改的總行數，是以不需要單獨統計。

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

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

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

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

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

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

查詢運作時計算總數和平均值

模拟GROUP語句中的函數FIRST()和LAST()

S對大量資料做一些資料計算

計算一個大表的MD5散列值

編寫一個樣本處理函數

模拟讀/寫遊标

在SHOW語句的WHERE子句中加入變量值