每日看點(diǎn)！MySQL函數(shù)CONCAT、CONCAT_WS、GROUP_CONCAT用法詳解

目錄

MySQL函數(shù)CONCAT、CONCAT_WS、GROUP_CONCAT

1.concat()函數(shù)

CONCAT函數(shù)用于將兩個(gè)字符串連接為一個(gè)字符串，試一下下面這個(gè)例子：

SELECT CONCAT("FIRST ", "SECOND");
    +----------------------------+
    | CONCAT("FIRST ", "SECOND") |
    +----------------------------+
    | FIRST SECOND               |
    +----------------------------+

要對 CONCAT函數(shù)有更為深入的了解，請考慮 employee_tbl表，表中記錄如下所示：

(資料圖)

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 |
    +------+------+------------+--------------------+

現(xiàn)在，假設(shè)你想要將上表中所有的姓名（name）、id和工作日（work_date）連接在一起，那么可以通過如下的命令來達(dá)到目的：

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             |
    +-----------------------------+

還可以在字符中間添加符號

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()函數(shù)

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的 特殊形式。第一個(gè)參數(shù)是其它參數(shù)的分隔符。分隔符的位置放在要連接的兩個(gè)字符串之間。分隔符可以是 一個(gè)字符串，也可以是其它參數(shù)。

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()函數(shù)

我們可以使用group_concat()函數(shù)將一整列打印到一行中，并且可以指定分隔的符號，默認(rèn)為,

SELECT GROUP_CONCAT(id) AS ids FROM employee_tbl
    +------------------+
    | GROUP_CONCAT(id) |
    +------------------+
    | 1,2,3,3,4,5,5    |
    +------------------+

將分隔符改為；

select group_concat(id separator ";") from employee_tbl;
    +------------------+
    | GROUP_CONCAT(id) |
    +------------------+
    | 1;2;3;3;4;5;5    |
    +------------------+

補(bǔ)充：Mysql concat_ws、concat函數(shù)使用

一、概念 concat

功能介紹：將多個(gè)字符串進(jìn)行拼接。
語法：concat(字段,字段,字段,…)

concat實(shí)踐

select CONCAT(id,",",age,",",name) AS line_info from user

如果連接串中存在NULL，則返回結(jié)果為NULL（我沒遇到過）：

select CONCAT(id,",",age,",",null) AS line_info from user

Null解決方案：

加一個(gè) IFNULL()函數(shù)。

select CONCAT(id,",",age,",",IFNULL(name,"")) AS line_info from user

concat_ws

功能介紹：字符串拼接無需手動(dòng)添加分隔符（自己理解）。
語法：concat_ws(分隔符,字段,字段,…)

concat_ws實(shí)踐

select CONCAT_WS(",",id,age,name) AS line_info from user

二、混合使用函數(shù)實(shí)戰(zhàn)

select CONCAT_WS(",",concat(name),concat("年齡",age,"歲")) AS line_info from user

到此這篇關(guān)于MySQL函數(shù)CONCAT、CONCAT_WS、GROUP_CONCAT用法詳解的文章就介紹到這了,更多相關(guān)MySQL函數(shù)CONCAT CONCAT_WS、GROUP_CONCAT內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

免責(zé)聲明：本文不構(gòu)成任何商業(yè)建議，投資有風(fēng)險(xiǎn)，選擇需謹(jǐn)慎！本站發(fā)布的圖文一切為分享交流，傳播正能量，此文不保證數(shù)據(jù)的準(zhǔn)確性，內(nèi)容僅供參考

關(guān)鍵詞：