MySQL 之變數

魏晉秋發表於2018-12-26
MySql變數

MySQL 之變數

MySQL 的變數分為四種： 區域性變數 、 使用者變數 、 會話變數 和 全域性變數 ，其中區域性變數只存在於函式和儲存過程，這裡不多瞭解。其中 會話變數 和 全域性變數 在 MySQL 中統稱為 系統變數 。

使用者變數

基本

顧名思義，就是使用者定義的變數。如何定義變數呢？有兩種方法：

  • SET 方式

    # 兩種方式都可以
SET @variable = expr
SET @variable := expr

  • SELECT 方式

    # 必須 :=
SELECT @variable := expr

使用者變數定義備註：

  1. 未定義變數的初始值為 null（可不定義變數直接使用，不會報錯）
  2. 變數名對大小寫不敏感
  3. 變數不能在要求字面值的地方使用，比如 select 中的 limit 語句等。
  4. 呼叫使用者變數的表示式的計算順序實際上是未定義的，如 SELECT @a = 0, @a := @a + 1;，兩列都可能是 0 。

  5. 為使用者變數賦值時，會先確定表示式的值。如何理解，請看如下程式碼：

    SET @m = 0;
SET @m = 3, @n = @m;
SELECT @n; # 0
  6. 雖然使用者變數的型別可以動態修改，但不建議這麼操作，因為在交接程式碼的時候你可能會有生命危險:p。

作為變數，都是有作用域的，使用者變數的作用是整個會話，即整個會話間都是有效的。這看起來不錯，但要注意，當使用了連線池，自定義的使用者變數又沒有正確初始化，容易出現意想不到的問題。因為它實際上並沒有被銷燬，依舊記錄者上一次的結果。

示例

我們來一個簡單的示例，實現一個序號的功能，表和資料如下：

CREATE TABLE employee (
   id int primary key,
   salary int not null
);

INSERT INTO employee VALUES(1, 100);
INSERT INTO employee VALUES(2, 200);
INSERT INTO employee VALUES(3, 300);

根據之前學習的內容，我們可以很快的寫出如下 SQL：

SELECT salary, (@rowno := @rowno + 1) AS `rowno`
FROM employee, (SELECT @rowno := 0) r;

沒有問題，一切都和預期一樣，然後我們加一個 WHERE 條件試試：

SELECT salary, (@rowno := @rowno + 1) AS `rowno`
FROM employee, (SELECT @rowno := 0) r
WHERE @rowno = 0;

理論上來說，這是不應該返回資料的，但是它還就是返回了一條資料，就是 id 為 1 的那條。
為什麼呢？ WHERE 條件使用的 @rowno 一直都是同一個值 0 ，它不會因為 SELECT 上修改了就實時響應 。要實現
WHERE 的功能需要改寫成如下：

SELECT salary, rowno
FROM (
    SELECT salary, (@rowno := @rowno + 1) AS `rowno`
    FROM employee, (SELECT @rowno := 0) r
) m
WHERE rowno = 2;

實際上在 SELECT 的 WHERE 、 GROUP BY 和 ORDER BY 中使用者變數都不會按預期操作，它使用的是舊值，不會實時修改。

系統變數

會話變數

會話變數為伺服器為每個客戶端連線維護的變數。在客戶端連線時，使用相應全域性變數的當前值對客戶端的會話變數進行初始化。

顧名思義，會話變數的作用域就是一個會話 Session 咯。如何為會話變數設定值呢？如下：

set session var_name = value;
set @@session.var_name = value;
set var_name = value;

注意，只能為現有的會話變數設定值，不能建立新的會話變數。那如何獲取會話變數呢？如下：

show session variables;
# 以上程式碼會把所有會話變數羅列出來，可通過 like 進行過濾
show session variables LIKE "%var%";

全域性變數

全域性變數會影響伺服器整體操作。但是一旦重啟，這些設定會被重置。注意要想更改全域性變數，必須具有SUPER許可權。

它的設定和會話變數的設定是類似的：

set global var_name = value;
set @@global.var_name = value;

全域性變數也不能新增變數，只能修改已有的。而獲取全域性變數的操作也是和會話變數類似：

show session variables;
show global variables like "%var%";

文章若有問題，歡迎在評論區中指正。

參考：

相關文章