MySQL CONCAT() 函數詳解：字符串拼接實戰與員工表演示

函數語法

CONCAT(str1, str2, ...)

返回拼接後的字符串，任一參數為 NULL 則結果為 NULL。使用 CONCAT_WS(sep, str1, str2...) 可指定分隔符並自動跳過 NULL。

基礎示例

mysql> SELECT CONCAT('FIRST ', 'SECOND');
+----------------------------+
| CONCAT('FIRST ', 'SECOND') |
+----------------------------+
| FIRST SECOND               |
+----------------------------+

員工表實戰數據

+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
| 1    | John | 2007-01-24 | 250                |
| 2    | Ram  | 2007-05-27 | 220                |
| 3    | Jack | 2007-05-06 | 170                |
| 3    | Jack | 2007-04-06 | 100                |
| 4    | Jill | 2007-04-06 | 220                |
| 5    | Zara | 2007-06-06 | 300                |
| 5    | Zara | 2007-02-06 | 350                |
+------+------+------------+--------------------+

拼接多列

mysql> SELECT CONCAT(id, name, work_date) FROM employee_tbl;
+-----------------------------+
| CONCAT(id, name, work_date) |
+-----------------------------+
| 1John2007-01-24             |
| 2Ram2007-05-27              |
| 3Jack2007-05-06             |
| 3Jack2007-04-06             |
| 4Jill2007-04-06             |
| 5Zara2007-06-06             |
| 5Zara2007-02-06             |
+-----------------------------+

進階用法

• 帶分隔符：CONCAT_WS(' - ', id, name, work_date) → 1 - John - 2007-01-24
• 處理 NULL：CONCAT(IFNULL(col,''), ...) 或 CONCAT_WS
• 生成 JSON 片段：CONCAT('{"id":', id, ',"name":"', name, '"}')
• 構建動態 SQL：SET @sql = CONCAT('SELECT * FROM ', @table, ' WHERE id=', @id); PREPARE stmt FROM @sql; EXECUTE stmt;

性能提示

• 大量拼接考慮 GROUP_CONCAT() 聚合
• 避免在 WHERE/JOIN 條件中對列使用 CONCAT（導致索引失效）