SQL 語法 UPDATE (保留原資料)

SQL 語法 UPDATE 是用來更新原紀錄的資料,但有辦法可以保留原資料+新資料嗎? 可以透過 SQL 函數 CONCAT 與 CONCAT_WS .

測試環境為 CentOS 7 x86_64 虛擬機.

• CONCAT
  CONTACT 函數可以將兩個或更多個字串連接在一起.

  CONCAT(expression1, expression2, expression3,...)
  

  直接來看範例.

  MariaDB [(none)]> SELECT CONCAT("APPLE", ", Banana", ", Cherry") AS Fruit; 
  +-----------------------+
  | Fruit                 |
  +-----------------------+
  | APPLE, Banana, Cherry |
  +-----------------------+
  1 row in set (0.00 sec)
  

  要注意使用 CONTACT 函數中有 NULL 時跟任何字串連接在一起還是 NULL.

  MariaDB [(none)]> SELECT CONCAT(NULL, ", Banana", ", Cherry") AS Fruit; 
  +-------+
  | Fruit |
  +-------+
  | NULL  |
  +-------+
  1 row in set (0.001 sec)
  

• CONCAT_WS
  相較於 CONTACT 函數 CONCAT_WS 可以加上固定的分隔字元.

  CONCAT_WS(separator, expression1, expression2, expression3,...)
  

  直接來看範例.

  MariaDB [(none)]> SELECT CONCAT_WS(", ", "APPLE", "Banana", "Cherry") AS Fruit; 
  +-----------------------+
  | Fruit                 |
  +-----------------------+
  | APPLE, Banana, Cherry |
  +-----------------------+
  1 row in set (0.01 sec)
  

回到 UPDATE 時保留原資料+新資料,先來建立一個測試用的資料庫.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-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.01 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE numtrack ( id int(11) AUTO_INCREMENT Primary key, nums char(20) , uptime timestamp); 
Query OK, 0 rows affected (0.03 sec)

MariaDB [testdb]> DESCRIBE numtrack;
+--------+-----------+------+-----+-------------------+-----------------------------+
| Field  | Type      | Null | Key | Default           | Extra                       |
+--------+-----------+------+-----+-------------------+-----------------------------+
| id     | int(11)   | NO   | PRI | NULL              | auto_increment              |
| nums   | char(20)  | YES  |     | NULL              |                             |
| uptime | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.03 sec)

建立一筆測試用的資料.

MariaDB [testdb]> INSERT INTO numtrack(nums) VALUES('1');
Query OK, 1 row affected (0.02 sec)

MariaDB [testdb]> SELECT * FROM numtrack;
+----+------+---------------------+
| id | nums | uptime              |
+----+------+---------------------+
|  1 | 1    | 2019-08-01 11:08:08 |
+----+------+---------------------+
1 row in set (0.01 sec)

利用函數 CONCAT 把舊資料 “1” 與要新增的資料 “, 2” 儲存回原欄位 nums=CONCAT(nums, ‘, 2’)

MariaDB [testdb]> UPDATE numtrack SET nums=CONCAT(nums, ', 2') WHERE id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM numtrack;
+----+------+---------------------+
| id | nums | uptime              |
+----+------+---------------------+
|  1 | 1, 2 | 2019-08-01 11:08:29 |
+----+------+---------------------+
1 row in set (0.00 sec)

或是利用函數 CONCAT_WS 把舊資料 “1, 2” 與要新增的資料 “3” 與分隔字元 “, ” 儲存回原欄位 nums=CONCAT_WS(‘, ‘ , nums, ‘3’)

MariaDB [testdb]> UPDATE numtrack SET nums=CONCAT_WS(', ' , nums, '3') WHERE id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM numtrack;
+----+---------+---------------------+
| id | nums    | uptime              |
+----+---------+---------------------+
|  1 | 1, 2, 3 | 2019-08-01 11:22:28 |
+----+---------+---------------------+
1 row in set (0.01 sec)