MySQL系統變量、自定義變量和存儲程式變量使用小結

     MySQL一共有三種變量：系統變量（system variable）、自定義變量（user-defined variable）和存儲程式變量（stored program variable）。系統變量是MySQL内置的；自定義變量是使用者自己定義；存儲程式變量包括存儲程式建立時定義的參數和内部的本地變量。普通使用者隻是僅僅寫寫簡單SQL的話，可能僅僅會接觸到自定義變量；系統變量和存儲程式變量對于MySQL管理者和運維人員來說，就會經常接觸了。有鑒于此，并且因為自定義變量最簡單易述，具體寫的時候就從自定義變量開始寫起。三種變量都遵循一個通用的定義形式：SET

variable_assignment

[,

variable_assignment

] ...

①指派指令

     SET

②指派符

     “=”或者“:=”（兩者皆可，個人習慣使用後者，在某本書上作者推薦的，不過官方文檔并沒有刻意的推薦說明）

③指派内容

     符合變量作用環境即可

一、自定義變量

     自定義變量就是簡單的資料容器，需要注意的是，變量名需要以一個“@”符号開頭，少了“@”不會識别，多了“@”可能會報錯（為什麼是“可能”留待系統變量再說），定義形式如下，其中“expr”從簡單到複雜可以是普通字元值也可以是查詢語句的傳回值。

SET @var_name = expr;
           

兩個簡單的栗子：

SET @name = 43;
SET @total_tax = (SELECT SUM(tax) FROM taxable_transactions);
           

     自定義變量即定義即使用，基本上沒有其他額外限制，但作用範圍是session級别的，也就是會話關閉後就不能再引用，除非再次定義。

二、系統變量

     系統變量就是MySQL自帶的那一堆服務運作控制選項，隻要MySQL在運作中，這些變量就在工作着，并且隻要你願意，随時可以拿出來看看（可以簡單地通過“

SHOW VARIABLES

”檢視）。這是一個龐大的部分，是以一點一點分開來說吧。

①很多系統變量都有預設值，是以指派為“DEFAULT”是有效的。

SET @@session.max_join_size=DEFAULT;
           

②系統變量有全局（GLOBAL）和會話（SESSION / LOCAL）之分，修改全局是服務級别的而修改會話隻是修改目前的連接配接會話。

SET GLOBAL sort_buffer_size = 1000000;
SET SESSION sort_buffer_size = 1000000;
           

③系統變量有多種修改方式：修改配置檔案、帶系統變量選項啟動MySQL以及MySQL運作中的指令修改。其中，修改配置檔案是最直截了當的。

④系統變量名有多種引用方式：直接變量名引用或者“@@”修飾符引用（對比前文自定義變量是一個“@”）。是以，在修改session級别的系統變量時，以下指令是等價的。

SET SESSION sql_mode = 'TRADITIONAL';
SET @@session.sql_mode = 'TRADITIONAL';
           

⑤修改全局變量需要“

SUPER

”超級權限，并且指令中的"GLOBAL"不可省略。

SET GLOBAL max_connections = 1000;
SET @@global.max_connections = 1000;
           

⑥修改會話級别的系統變量一般不需要特殊權限，并且指令中的“SESSION”是可以省略的（預設會話級别）。

SET sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';
           

⑦“LOCAL”=“SESSION”以及“@@local.”=“@@session.”。是以以下全部等價。

SET sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';
SET SESSION sql_mode = 'TRADITIONAL';
SET @@session.sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';
SET @@local.sql_mode = 'TRADITIONAL';
           

⑧混合設定多個系統變量時，最近的一個“GLOBAL”或“SESSION”修飾語作用于接下來的所有未作聲明的變量。

SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@global.sort_buffer_size = 1000000, @@local.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;
           

三、存儲程式變量

     存儲程式變量的作用範圍僅僅局限在定義它們的那個存儲程式中。存儲程式變量包括存儲函數和存儲過程定義的參數以及在存儲程式（存儲函數、存儲過程、事件以及觸發器等）中定義的本地變量。

①存儲程式變量需要事先聲明：參數變量在存儲程式定義語句頭部聲明；本地變量在存儲程式體中通過“DECLARE”聲明。

DECLARE var_name [, var_name] ... type [DEFAULT value]
           

②定義的存儲程式變量資料類型需要與事先聲明保持一緻。

③存儲程式變量聲明需要置于“CURSOR”和“HANDLE”聲明之前。

     以上内容簡單總結了MySQL的三種變量類型。實際使用中，三種變量并非是獨立的，也可以有互相的關聯關系，甚至于一條“SET”語句可以進行多變量類型操作。

SET @seed = 1024, GLOBAL max_connections = 1024;
SET @seed = @@global.max_connections + 9527;
           

參考MySQL官方文檔：

https://dev.mysql.com/doc/refman/5.7/en/set-variable.html

https://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

https://dev.mysql.com/doc/refman/5.7/en/stored-program-variables.html