各種類型變量的定義以及指派

局部變量

局部變量一般用在sql語句塊中，比如存儲過程的begin/end。其作用域僅限于該語句塊，在該語句塊執行完畢後，局部變量就消失了。declare語句專門用于定義局部變量，可以使用default來說明預設值。set語句是設定不同類型的變量，包括會話變量和全局變量。  

局部變量定義文法形式

DECLARE var_name [, var_name]... data_type [ DEFAULT value ];      

例如在begin/end語句塊中添加如下一段語句，接收函數傳進來的a/b變量然後相加，通過set語句指派給c變量。  

set語句文法形式SET var_name=expr [, var_name=expr]...; set語句既可以用于局部變量的指派，也可以用于使用者變量的申明并指派。

DECLARE c int DEFAULT 0;
SET c=a+b; #指派，必須要有SET
SELECT c AS C;      

或者用select …. into…形式指派

select into 語句句式：SELECT col_name[,...] INTO var_name[,...] table_expr [WHERE...];#很有用

例子:

DECLARE v_employee_name VARCHAR(100);
DECLARE v_employee_salary DECIMAL(8,4);
 
SELECT employee_name, employee_salary
INTO v_employee_name, v_employee_salary
FROM employees
WHERE employee_id=1;      

使用者變量

參考官方文檔：

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

mysql中使用者變量不用事前申明，在用的時候直接用“@變量名”使用就可以了。  

第一種用法：set @num=1; 或set @num:=1; //這裡要使用set語句建立并初始化變量，直接使用@num變量  

第二種用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……，  

1)、select語句一般用來輸出使用者變量，比如select @變量名,用于輸出資料源不是表格的資料。對于局部變量（無@）則無效，而應該使用select into形式  

注意上面兩種指派符号，使用set時可以用“=”或“：=”，但是使用select時必須用“：=指派”。因為在select語句中，”=”号declare語句專門用于定義局部變量。set語句是設定不同類型的變量，包括會話變量和全局變量。

2)、使用者變量與資料庫連接配接有關，在連接配接中聲明的變量，在存儲過程中建立了使用者變量後一直到資料庫執行個體接斷開的時候，變量就會消失。

3)、在此連接配接中聲明的變量無法在另一連接配接中使用。

4)、使用者變量的變量名的形式為@varname的形式。

5)、名字必須以@開頭。

6)、聲明變量的時候需要使用set語句，比如下面的語句聲明了一個名為@a的變量。

set @a = 1;
# 聲明一個名為@a的變量，并将它指派為1，mysql裡面的變量是不嚴格限制資料類型的，它的資料類型根據你賦給它的值而随時變化 。      

7)、我們還可以使用select 語句為變量指派 。  

比如：

set @name = '';
select @name:=password from user limit 0,1;#從資料表中擷取一條記錄password字段的值給@name變量。在執行後輸出到查詢結果集上面。      

（注意等于号前面有一個冒号，後面的limit 0,1是用來限制傳回結果的，表示可以是0或1個。）  

如果直接寫：select @name:=password from user;

如果這個查詢傳回多個值的話，那@name變量的值就是最後一條記錄的password字段的值 。  

舉一個存儲過程中使用變量的例子：

BEGIN
#Routine body goes here...
#SELECT c AS c;
DECLARE c int DEFAULT 0;
SET @var1=143;  #定義一個使用者變量，并初始化為143
SET @var2=34;
SET c=a+b;
SET @d=c;
SELECT @sum:=(@[email protected]) AS sum, @dif:=(@[email protected]) AS dif, @d AS C;#使用使用者變量。@var1表示變量名
 
SET c=100;
SELECT c AS CA;
END      

在查詢中執行下面語句段

CALL `order`(12,13);  #執行上面定義的存儲過程

SELECT @var1;  #看定義的使用者變量在存儲過程執行完後，是否還可以輸出，結果是可以輸出使用者變量@var1,@var2兩個變量的。

SELECT @var2;

在執行完order存儲過程後，在存儲過程中建立的var1，var2使用者變量還是可以用select語句輸出的，但是存儲過程裡面定義的局部變量c不能識别。

系統變量

參考官方文檔：

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

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

會話變量

伺服器為每個連接配接的用戶端維護一系列會話變量。在用戶端連接配接資料庫執行個體時，使用相應全局變量的目前值對用戶端的會話變量進行初始化。設定會話變量不需要特殊權限，但用戶端隻能更改自己的會話變量，而不能更改其它用戶端的會話變量。會話變量的作用域與使用者變量一樣，僅限于目前連接配接。當目前連接配接斷開後，其設定的所有會話變量均失效。

設定會話變量有如下三種方式更改會話變量的值：

set session var_name = value;
 
set @@session.var_name = value;
 
set var_name = value;  #預設session關鍵字預設認為是session
 
檢視所有的會話變量
SHOW  SESSION VARIABLES;      

檢視一個會話變量也有如下三種方式：

select @@var_name;
 
select @@session.var_name;
 
show session variables like "%var%";      

全局變量

全局變量影響伺服器整體操作。當伺服器啟動時，它将所有全局變量初始化為預設值。這些預設值可以在選項檔案中或在指令行中指定的選項進行更改。要想更改全局變量，必須具有SUPER權限。全局變量作用于server的整個生命周期，但是不能跨重新開機。即重新開機後所有設定的全局變量均失效。要想讓全局變量重新開機後繼續生效，需要更改相應的配置檔案。

要設定一個全局變量，有如下兩種方式：

set global var_name = value; //注意：此處的global不能省略。根據手冊，set指令設定變量時若不指定GLOBAL、SESSION或者LOCAL，預設使用SESSION
set @@global.var_name = value; //同上      

檢視所有的全局變量  

show global variables;      

要想檢視一個全局變量，有如下兩種方式：

select @@global.var_name;
show global variables like '%var%';      

存儲過程中 declare 和 set 定義變量的差別

mysql存儲過程中，定義變量有兩種方式：

1.使用set或select直接指派，變量名以 @ 開頭.

例如:set @var=1;

可以在一個會話的任何地方聲明，作用域是整個會話，稱為會話變量。

2.以 DECLARE 關鍵字聲明的變量，隻能在存儲過程中使用，稱為存儲過程變量，例如：

DECLARE var1  INT DEFAULT 0;  

主要用在存儲過程中，或者是給存儲傳參數中。

兩者的差別是：

在調用存儲過程時，以DECLARE聲明的變量都會被初始化為 NULL。而會話變量（即@開頭的變量）則不會被再初始化，在一個會話内，隻須初始化一次，之後在會話内都是對上一次計算的結果，就相當于在是這個會話内的全局變量。

在存儲過程中，使用動态語句，預處理時，動态内容必須賦給一個會話變量。

例：

set @v_sql= sqltext;
PREPARE stmt FROM @v_sql;  
EXECUTE stmt;      
DEALLOCATE PREPARE stmt;      

sql語句預編譯

mysql> SET @c = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @s = CONCAT("SELECT ", @c, " FROM t");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql> EXECUTE stmt;
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)