歐亞在線開戶——SKSKFC
MySQL函數CONCAT、CONCAT_WS、GROUP_CONCAT
1.concat()函數
CONCAT?函數用于將兩個字符串連接為一個字符串，試一下下面這個例子：
1
2
3
4
5
6
SELECT CONCAT('FIRST ', 'SECOND');+----------------------------+| CONCAT('FIRST ', 'SECOND') |+----------------------------+| FIRST SECOND?????????????? |+----------------------------+要對?CONCAT?函數有更為深入的了解，請考慮?employee_tbl?表，表中記錄如下所示：
1
2
3
4
5
6
7
8
9
10
11
12
SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id?? | name | work_date? | daily_typing_pages |+------+------+------------+--------------------+|??? 1 | John | 2022-08-24 |??????????????? 250 ||??? 2 | Ramr | 2022-08-27 |??????????????? 220 ||??? 3 | Jack | 2022-08-06 |??????????????? 170 ||??? 3 | Jack | 2022-08-06 |??????????????? 100 ||??? 4 | Jill | 2022-08-06 |??????????????? 220 ||??? 5 | Zara | 2022-08-06 |??????????????? 300 ||??? 5 | Zara | 2022-08-06 |??????????????? 350 |+------+------+------------+--------------------+現在，假設你想要將上表中所有的姓名（name）、id和工作日（work_date）連接在一起，那么可以通過如下的命令來達到目的：
1
2
3
4
5
6
7
8
9
10
11
12
SELECT CONCAT(id, name, work_date) FROM employee_tbl;+-----------------------------+| CONCAT(id, name, work_date) |+-----------------------------+| 1John2022-08-24???????????? || 2Ramr2022-08-27???????????? || 3Jack2022-08-06???????????? || 3Jack2022-08-06???????????? || 4Jill2022-08-06???????????? || 5Zara2022-08-06???????????? || 5Zara2022-08-06???????????? |+-----------------------------+還可以在字符中間添加符號
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT CONCAT(id,",",name,",",work_date) FROM employee_tbl;???+-----------------------------+???| CONCAT(id, name, work_date) |???+-----------------------------+???| 1,John,2022-08-24?????????? |???| 2,Ramr,2022-08-27?????????? |???| 3,Jack,2022-08-06?????????? |???| 3,Jack,2022-08-06?????????? |???| 4,Jill,2022-08-06?????????? |???| 5,Zara,2022-08-06?????????? |???| 5,Zara,2022-08-06?????????? |???+-----------------------------+?
SELECT CONCAT(id,",",name,",",work_date) FROM employee_tbl;???+-----------------------------+???| CONCAT(id, name, work_date) |???+-----------------------------+???| 1,John,2022-08-24?????????? |???| 2,Ramr,2022-08-27?????????? |???| 3,Jack,2022-08-06?????????? |???| 3,Jack,2022-08-06?????????? |???| 4,Jill,2022-08-06?????????? |???| 5,Zara,2022-08-06?????????? |???| 5,Zara,2022-08-06?????????? |???+-----------------------------+2.concat_ws()函數
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的 特殊形式。第一個參數是其它參數的分隔符。分隔符的位置放在要連接的兩個字符串之間。分隔符可以是 一個字符串，也可以是其它參數。
1
2
3
4
5
6
7
8
9
10
11
12
SELECT CONCAT(",",id,name,work_date) FROM employee_tbl;???+-----------------------------+???| CONCAT(id, name, work_date) |???+-----------------------------+???| 1,John,2022-08-24?????????? |???| 2,Ramr,2022-08-27?????????? |???| 3,Jack,2022-08-06?????????? |???| 3,Jack,2022-08-06?????????? |???| 4,Jill,2022-08-06?????????? |???| 5,Zara,2022-08-06?????????? |???| 5,Zara,2022-08-06?????????? |???+-----------------------------+3.group_concat()函數
我們可以使用group_concat()函數將一整列打印到一行中，并且可以指定分隔的符號，默認為
,
1
2
3
4
5
6
SELECT GROUP_CONCAT(id) AS ids FROM employee_tbl+------------------+| GROUP_CONCAT(id) |+------------------+| 1,2,3,3,4,5,5??? |+------------------+將分隔符改為；
1
2
3
4
5
6
select group_concat(id separator ";") from employee_tbl;+------------------+| GROUP_CONCAT(id) |+------------------+| 1;2;3;3;4;5;5??? |+------------------+補充：Mysql concat_ws、concat函數使用
一、概念 concat
功能介紹：?將多個字符串進行拼接。
語法：?concat(字段,字段,字段,…)
concat實踐
1
select CONCAT(id,',',age,',',name) AS line_info from user如果連接串中存在NULL，則返回結果為NULL（我沒遇到過）：
1
select CONCAT(id,',',age,',',null) AS line_info from userNull解決方案：
加一個 IFNULL()函數。
1
select CONCAT(id,',',age,',',IFNULL(name,'')) AS line_info from userconcat_ws
功能介紹：?字符串拼接無需手動添加分隔符（自己理解）。
語法：concat_ws(分隔符,字段,字段,…)
concat_ws實踐
1
select CONCAT_WS(',',id,age,name) AS line_info from user二、混合使用函數實戰(zhàn)
1
select CONCAT_WS(',',concat(name),concat('年齡',age,'歲')) AS line_info from user標簽：
