亲宝软件园·资讯

展开

MySQL函数CONCAT、CONCAT_WS、GROUP_CONCAT用法详解

楚生辉 人气:0

MySQL函数CONCAT、CONCAT_WS、GROUP_CONCAT

1.concat()函数

CONCAT 函数用于将两个字符串连接为一个字符串,试一下下面这个例子:

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

要对 CONCAT 函数有更为深入的了解,请考虑 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 |
    +------+------+------------+--------------------+

现在,假设你想要将上表中所有的姓名(name)、id和工作日(work_date)连接在一起,那么可以通过如下的命令来达到目的:

    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()函数

CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的 特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是 一个字符串,也可以是其它参数。

 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()函数将一整列打印到一行中,并且可以指定分隔的符号,默认为,

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

补充:Mysql concat_ws、concat函数使用

一、概念 concat

功能介绍: 将多个字符串进行拼接。
语法: concat(字段,字段,字段,…)

concat实践

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

如果连接串中存在NULL,则返回结果为NULL(我没遇到过):

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

Null解决方案:

加一个 IFNULL()函数。

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

concat_ws

功能介绍: 字符串拼接无需手动添加分隔符(自己理解)。
语法:concat_ws(分隔符,字段,字段,…)

concat_ws实践

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

二、混合使用函数实战

select CONCAT_WS(',',concat(name),concat('年龄',age,'岁')) AS line_info from user

加载全部内容

相关教程
猜你喜欢
用户评论