SQL 語法 SET , := 以及 SELECT INO

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

SELECT 後的資料有辦法儲存到變數嗎? 可以透過幾種方式.

SELECT 查詢需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – https://benjr.tw/102156 ).

先建立一個資料庫 (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');
Query OK, 1 row affected (0.002 sec)

MariaDB [testdb]> INSERT INTO employee VALUES ('Afa','Power','Engineer','48000');
Query OK, 1 row affected (0.002 sec)

MariaDB [testdb]> INSERT INTO employee VALUES ('Boss','Testing','Manager','75000');
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> INSERT INTO employee VALUES ('Cars','Testing','Senior Engineer','42000');
Query OK, 1 row affected (0.002 sec)

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)

需使用 User-defined variables (以 @ 開頭) , 請參考 SQL 變數 Variables 說明 – https://benjr.tw/102436

set

一次只能儲存一個變數.

MariaDB [testdb]> SET @SalaryTemp =(SELECT Salary FROM employee WHERE NAME='Ben');
Query OK, 0 rows affected (0.001 sec)

MariaDB [testdb]> SELECT @SalaryTemp;
+-------------+
| @SalaryTemp |
+-------------+
|       45000 |
+-------------+
1 row in set (0.000 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)

MariaDB [testdb]> SELECT @SalaryTemp , @NameTemp;
+-------------+-----------+
| @SalaryTemp | @NameTemp |
+-------------+-----------+
|       45000 | Ben       |
+-------------+-----------+
1 row in set (0.000 sec)

SELECT INO

MariaDB [testdb]> SELECT Salary , Name INTO @SalaryTemp , @NameTemp FROM employee WHERE NAME='Ben';
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> SELECT @SalaryTemp , @NameTemp;
+-------------+-----------+
| @SalaryTemp | @NameTemp |
+-------------+-----------+
|       45000 | Ben       |
+-------------+-----------+
1 row in set (0.000 sec)

遇過的錯誤

• SET 無法存儲多筆資料,如果 SELECT 回傳值多於一筆會出現以下的錯誤訊息.

  MariaDB [testdb]> SET @SalaryTemp =(SELECT Salary FROM employee WHERE JobTitle='Engineer');
  ERROR 1242 (21000): Subquery returns more than 1 row
  

• := 同樣無法存儲多筆資料,如果 SELECT 回傳值多於一筆只會保留最後一筆資料.

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

• SELECT INO 同樣無法存儲多筆資料,如果 SELECT 回傳值多於一筆會出現以下的錯誤訊息.

  MariaDB [testdb]> SELECT Salary , Name INTO @SalaryTemp , @NameTemp FROM employee WHERE JobTitle='Engineer';
  ERROR 1172 (42000): Result consisted of more than one row