SQL 變數 Variables

測試環境為 CentOS 7 x86_64 (虛擬機)

這邊來看一下 MySQL / MariaDB SQL 變數 Variables 的種類.

先建立一個資料庫 (testdb) , 與 tables (employee) 格式為 Name char(20), Dept char(20), jobTitle char(20) 各 20 個字元以及 Salary int(11).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (Name char(20),Dept char(20),JobTitle char(20),Salary int(11));
Query OK, 0 rows affected (0.009 sec)

MariaDB [testdb]> DESCRIBE employee; 
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| JobTitle | char(20) | YES  |     | NULL    |       |
| Salary   | int(11)  | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.004 sec)

MariaDB [testdb]> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| employee         |
+------------------+
1 row in set (0.001 sec)

透過 INSERT 新增加了四筆資料.

MariaDB [testdb]> INSERT INTO employee VALUES ('Ben','Testing','Engineer','45000') , ('Afa','Power','Engineer','48000') , ('Boss','Testing','Manager','75000') , ('Cars','Testing','Senior Engineer','42000');
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM employee;
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
4 rows in set (0.001 sec)

MySQL (MariaDB) 變數的種類如下:

User-defined variables (以 @ 開頭):

User-defined variables 不需要事先宣告或是設定初始值 (預設為字串型態 String 且值為 NULL).

MariaDB [testdb]> SELECT @var_any_var_name;
+-------------------+
| @var_any_var_name |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> SELECT @SalaryTemp := Salary , @NameTemp := Name FROM employee WHERE NAME='Ben';
+-----------------------+-------------------+
| @SalaryTemp := Salary | @NameTemp := Name |
+-----------------------+-------------------+
|                 45000 | Ben               |
+-----------------------+-------------------+
1 row in set (0.001 sec)

Local Variables (no prefix) :

需事先宣告,用於 Stored Routines ( Procedure 以及 function), 下面例子來看一下 User-defined / Local Variables 的差別.

MariaDB [testdb]> DROP PROCEDURE IF EXISTS var_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [testdb]> DELIMITER //
MariaDB [testdb]> CREATE PROCEDURE var_test ()
    -> BEGIN
    ->     DECLARE var2 INT DEFAULT 1;
    ->     SET var2 = var2 + 1;
    ->     SET @var2 = @var2 + 1;
    ->     SELECT  var2, @var2;
    -> END;//
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> DELIMITER ;

• DELIMITER // //
  mysql 透過分號 “;” 來表示分隔(一個敘述完成),但函數裡面可能會有多個 分隔,這時候可以使用 DELIMITER // … // ,來表示裡面是一整個敘述.
• DELIMITER ;
  把結束符號修改回為 分號 “;” .
• BEGIN END
  程式宣告必須包含在裡面.

可以發現 User-defined Variables 變數值會一直保留下來, Local Variables 值只保留在 PROCEDURE BEGIN … END 區塊內.

MariaDB [testdb]> SET @var2 = 1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb]> CALL var_test();
+------+-------+
| var2 | @var2 |
+------+-------+
|    2 |     2 |
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> CALL var_test();
+------+-------+
| var2 | @var2 |
+------+-------+
|    2 |     3 |
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Server System Variables ( 以 @@ 開頭 ) :

看 MySQL / MariaDB 的系統變數有兩種方式

MariaDB [testdb]> SHOW VARIABLES LIKE 'character_set_client';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_client | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)

MariaDB [testdb]> SELECT @@character_set_client;
+------------------------+
| @@character_set_client |
+------------------------+
| utf8                   |
+------------------------+
1 row in set (0.00 sec)