MariaDB/MySQL中的變數

駿馬金龍發表於2018-04-02
MySql變數

在MySQL/MariaDB中有好幾種變數型別：使用者自定義變數、系統變數、一般的臨時變數(即本地變數，或稱為區域性變數)。

1.使用者變數

使用者變數是基於會話的，也是基於使用者的，所以我覺得稱之為會話變數更合適，但會話變數一般用來表示系統會話變數(後面會說明)，所以還是稱之為使用者變數好了。

只有本使用者才能引用自身的使用者變數，其他使用者無法引用，且當使用者退出會話時，使用者變數自動銷燬。

使用者變數使用”@”開頭，使用者變數可以直接賦值，無需事先宣告。在引用未賦值的使用者變數時，該變數值為null。

有以下三種方式設定使用者變數：

  1. set語句，此時可以使用”=”或者”:=”操作符;
  2. select語句，此時只能使用”:=”格式賦值，因為除了set語句中，”=”都會被視為比較操作符。;
  3. select … into var_name from TABLE語句，此時要求select語句只能返回標量值，即單行資料。因此為了保險，select into var_name的時候，應儘量結合limit語句限制輸出。
set @a1=1,@a2=3,@a3:=2;
select @a4:=@a1+@a3;
select 33,`abc` into @a5,@a6 from dual;

檢視變數值可以使用select語句。

MariaDB [test]> select @a1,@a2,@a3,@a4,@a5,@a6,@a7;
+------+------+------+------+------+------+------+
| @a1  | @a2  | @a3  | @a4  | @a5  | @a6  | @a7  |
+------+------+------+------+------+------+------+
|    1 |    3 |    2 |    3 |   33 | abc  | NULL |
+------+------+------+------+------+------+------+

在mariadb 10.2.6中，引入了一張系統架構表information_schema.USER_VARIABLES，該表中記錄了當前使用者當前會話定義的使用者變數資訊。該資訊架構表在mysql中沒有。

MariaDB [test]> SELECT * FROM information_schema.USER_VARIABLES;           
+---------------+----------------+---------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| a6            | abc            | VARCHAR       | utf8               |
| i             | 2              | INT           | utf8               |
| a5            | 33             | INT           | utf8               |
| a1            | 1              | INT           | utf8               |
| a4            | 3              | INT           | utf8               |
| a2            | 3              | INT           | utf8               |
| a3            | 2              | INT           | utf8               |
+---------------+----------------+---------------+--------------------+

2.系統變數

在MySQL/mariadb中維護兩種系統變數：全域性系統變數和會話系統變數。系統變數是用來設定MySQL服務執行屬性和狀態的。

全域性系統變數使用global或者”@@global.”關鍵字來設定。會話系統變數使用session或者”@@session.”關鍵字來設定，其中session可以替換為Local，它們是同義詞。如果省略這些關鍵字，則預設為session系統變數。設定global系統變數要求具有super許可權。

-- 設定全域性系統變數
set global sort_buffer_size=32M;
set @@global.sort_buffer_size=32M;
-- 設定會話系統變數
set session sort_buffer_size=32M;
set @@session.sort_buffer_size=32M;
set sort_buffer_size=32M;
-- 檢視全域性系統變數值
select @@global.sort_buffer_size;
show global variables like "sort_buffer%";
-- 檢視會話系統變數，不能使用select sort_buffer_size
select @@session.sort_buffer_size;
select @@sort_buffer_size;
show [session] variables like "sort_buffer%";
-- 一次性設定多個變數，包括會話變數、全域性變數以及使用者變數
SET @x = 1, SESSION sql_mode = ``;
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;

全域性系統變數對全域性有效，當有新的會話開啟時，新會話會繼承全域性系統變數的值，所以設定全域性系統變數之後新開啟的會話都會繼承設定後的值。設定全域性系統變數對已經開啟的連線無效，但是其他已經開啟的連線可以檢視到設定後的全域性系統變數值。

系統變數按照是否允許在執行時修改，還分為動態變數和靜態變數。能在執行過程中修改的變數稱為動態變數，只能在資料庫例項關閉狀態下修改的變數稱為靜態變數或只讀變數。動態變數使用set修改。如果在資料庫例項執行狀態下修改靜態變數，則會給出錯誤。如：

set @@innodb_undo_tablespaces=3;
ERROR 1238 (HY000): Variable `innodb_undo_tablespaces` is a read only variable

系統變數除了可以在執行中的環境下設定，還可以在配置檔案中或者mysqld/mysqld_safe這樣的命令列中設定，甚至mysql客戶端命令列也可以傳遞。在配置檔案中設定系統變數時，下劃線或者短橫線都允許，它們表示同一個意思。例如下面的兩行配置是等價的：

innodb_file_per_table=1
innodb-file-per-table=1

3.區域性變數

區域性變數也稱為本地變數，只能在begin…and語句塊中生效。它不像使用者變數，本地變數必須使用declare事先宣告，所以declare也必須在begin…end中使用。

區域性變數無論是宣告還是呼叫的時候都不需要任何多餘的符號(即不需要@符號)，直接使用其名稱var_name即可。

使用declare宣告變數，可以一次性宣告多個同型別的變數，需要時可有直接為其指定預設值，不指定時預設為null。

decalre var_name,... type [default value];

使用set為變數賦值。MySQL/mariadb中set支援一次性賦值多個變數。

在begin…end中的set是一般set語句的擴充套件版本，它既可以設定系統變數、使用者變數，也可以設定此處的本地變數。

set var_name=expr,[var_name=expr1,...]

或者使用select…into語句從表中獲取值來賦值給變數，但是這樣的賦值行為要求表的返回結果必須是單列且單行的標量結果。例如下面的語句將col的列值賦值給var_name變數。

select col into var_name from table_name;

因為區域性變數只能在begin…end中使用，所以此處使用儲存過程的例子來演示。

DROP PROCEDURE IF EXISTS haha;
DELIMITER $$
CREATE PROCEDURE haha()
BEGIN
    DECLARE a INT;
    SET a=1;
    SET @i:=2;
    SELECT a,@i;
END$$
DELIMITER ;

CALL haha();
     a        @i
------  --------
     1         2

在MySQL中，begin…end只能定義在儲存程式中，所以declare也只能定義在儲存程式內。但在mariadb中，begin…end是允許定義在儲存程式(儲存函式，儲存過程，觸發器，事件)之外的，所以decalre也算是能夠定義在儲存程式之外吧。需要定義在儲存程式之外時，使用 begin not atomic 關鍵字即可。例如：

delimiter $$ 
begin not atomic
    declare a int;
    set a=3;
    select a;
end$$

3.1 declare錨定其他物件的資料型別

在mariadb 10.3中(注意版本號，目前10.3版本還在測試中)，declare語句允許在儲存程式中使用TYPE OF和ROW TYPE OF 關鍵字基於表或遊標來錨定資料型別。在mysql中不支援資料型別的錨定功能。

例如：

DECLARE tmp TYPE OF t1.a; -- 基於表t1中的a列獲取資料型別
DECLARE rec1 ROW TYPE OF t1; -- 錨定表t1中行資料型別
DECLARE rec2 ROW TYPE OF cur1; -- 基於遊標cur1獲取行資料型別

通過其他物件來錨定本地變數的資料型別時，如果物件的資料型別改變，則本地資料型別也隨之改變。這在某些時候非常有利於維護儲存程式。

在定義儲存程式時，不會檢查declare錨定的物件是否存在。但在呼叫儲存程式時，會先檢查錨定物件是否存在。

當declare語句的錨定是基於表物件(不是遊標)時，在呼叫儲存程式的瞬間就會檢查錨定的表是否存在，然後立刻宣告該變數。因此:

  • (1).帶有錨定功能的decalre語句可以定義在儲存程式的任意位置；
  • (2).在儲存程式中刪除錨定的表物件，或者修改了錨定的表結構，都不會改變儲存程式呼叫時宣告的變數型別；
  • (3).所有帶錨定功能的declare都是在儲存程式呼叫之初被賦值的。

當declare語句的錨定是基於遊標物件時，變數的資料型別是在執行變數宣告語句時才獲取到的。資料型別僅只錨定一次，之後不再改變。如果遊標中的ROW TYPE OF變數是定義在一個迴圈之中，則資料型別在迴圈的開頭就已經獲取，且之後的迴圈不再改變。

示例：

create table t1(a int,b char(20));
drop procedure if exists haha;
delimiter $$
create procedure haha()
begin
    declare x type of t1.a;
    set x=1;
    select x;
end$$
delimiter ;

call haha();

相關文章