MySQL變量介紹和用法簡介

2019 年 10 月 7 日
筆記

本文鏈接：https://blog.csdn.net/u014427391/article/details/100889674

本博客介紹一下MySQL中變量的用法和注意細節

文章目錄

一、用戶變量

1.1、用戶變量定義

1.2、用戶變量用法

a）、設置用戶變量

b）、查詢用戶變量

二、系統變量

2.1 系統變量簡單介紹

2.2 系統變量用法簡介

一、用戶變量

1.1、用戶變量定義

MySQL官方手冊里是將變量分為系統變量和用戶變量的，用戶變量就是在一個語句里加在用戶自定義的變量，然後這個變量可以賦值給其它變量，或者在另外一個語句里調用等，本博客基於MySQL5.7版本，其它版本的還是具體參考官方手冊

【拓展】： MySQL官方手冊里是將變量分為系統變量和用戶變量的，不過有些地方也將變量按照用法分為：1、臨時變量(@符號的情況，也就是mysql手冊介紹的用戶變量)；2、局部變量(declare方式)；3、會話變量；4、全局變量(也就是系統變量)，其實分類只是為了方便記錄學習，重點是掌握用法原理就可以，因為局部變量和會話變量用的比較少，所以簡單介紹一下一、declare聲明變量(局部變量) declare聲明變量：declare聲明關鍵字可以用於定義變量，一般用於存儲過程或者自定義函數里 a）、declare聲明變量用法：聲明一個v1變量，定義為int類型，默認值為0； declare v1 INT default 0; b）、declare變量使用聲明後變量一般是在存儲過程或者自定義里的，所以是寫在begin和end關鍵字之間的，外面的不能直接定義，然後調用，所以declare也被稱之為局部變量二、session會話變量 session會話變量用的比較少，不過有個特性是和@臨時變量是一致的，就是關閉會話，或者說關閉數據庫連接的時候，變量是會失效的

1.2、用戶變量用法

a）、設置用戶變量

SET @var_name = expr [, @var_name = expr] ... 其實就是直接用關鍵字set就可以，例子

set @v1 =: 'test';

或者直接省略冒號也是可以的，不過建議還是加上，以免有些情況獲取不到數據

set @v1 ='test';

其實，可以不用set關鍵字

select @v1 := 'test';

查詢時候直接賦值也是可以的，看起來@符合的用法比較靈活，並沒有那麼多約束。所以有些地方是將用這種用法稱之為臨時變量

b）、查詢用戶變量

查詢變量：

select @[變量名];

比如剛才的@v1臨時變量

select @v1;

備註：使用@符號的這種用法，在數據庫連接的時候是有效的，當你關閉數據庫連接的時候，變量值是會被置為Null值的，所以也是這種@符號用法set的變量被稱之為臨時變量的原因

【用法注意事項】：

變量名稱用戶變量名稱不區分大小寫。名稱的最大長度為64個字符，假如超過會報錯：

Error Code: 3061  User variable name ${變量名} is illegal

變量分配值用戶變量分配值：整數，十進制，浮點，二進制或非二進制字符串或NULL值，如果為日期類型是不支持的
分配給用戶變量的十六進制或位值被視為二進制字符串。要將十六進制或位值作為數字分配給用戶變量，可以使用加0或使用CAST(… AS UNSIGNED)的方式例子來自mysql官方手冊

mysql> SET @v1 = X'41';  mysql> SET @v2 = X'41'+0;  mysql> SET @v3 = CAST(X'41' AS UNSIGNED);  mysql> SELECT @v1, @v2, @v3;  +------+------+------+  | @v1  | @v2  | @v3  |  +------+------+------+  | A    |   65 |   65 |  +------+------+------+  mysql> SET @v1 = b'1000001';  mysql> SET @v2 = b'1000001'+0;  mysql> SET @v3 = CAST(b'1000001' AS UNSIGNED);  mysql> SELECT @v1, @v2, @v3;  +------+------+------+  | @v1  | @v2  | @v3  |  +------+------+------+  | A    |   65 |   65 |  +------+------+------+

變量不要連續使用這種情況在手冊已經指出來了，如圖腳本：

mysql> SET @a:='test';  mysql> SELECT @a,(@a:=20)

可以查詢出來，發現第一個變量還是』test』字符串，而第二個字符串卻是另外一個值

【用戶變量案例】：

介紹一下，變量來做類似oracle rownum的用法

oracle原版寫法：

select * from (select id,name from t) where rownum <![CDATA[<=]]> to_number(num);

mysql改寫後的SQL：

SELECT    *  FROM    (SELECT      tb.*,      @rownum := @rownum + 1 AS rownum    FROM      (SELECT        id,        NAME      FROM        t) tb,      (SELECT        @rownum := 0) r) AS t  WHERE rownum <= CAST(num AS SIGNED INTEGER) ;

二、系統變量

2.1 系統變量簡單介紹

系統變量也稱之為全局變量，系統變量是mysql的一些系統變量參數，一般不要修改，特別是生產環境

2.2 系統變量用法簡介

用戶變量的用法是用@符號，系統變量查詢是用@@符號，參數有很多，比如：

SELECT @@basedir;

select @@datadir;