concat,concat_ws,group_concat

一、concat函数

1.语法：concat(str1,str2,str3,...)

2.concat ()方法用于连接两个或多个数组。该方法不会改变现有的数组，而仅仅会返回被连接数组的一个副本。

3.concat函数在连接字符串时，如有任何一个参数为空，返回值则为空。

4.示例：

• 普通示例

mysql> select concat(11,22,33);
+------------------+
| concat(11,22,33) |
+------------------+
| 112233 |
+------------------+
1 row in set

 

mysql> select concat('11','22','33');
+------------------------+
| concat('11','22','33') |
+------------------------+
| 112233 |
+------------------------+
1 row in set

• NULL示例

mysql> select concat('11','22',NULL);
+------------------------+
| concat('11','22',NULL) |
+------------------------+
| NULL |
+------------------------+
1 row in set

 

二、concat_ws函数

1.语法：contcat_ws(separator,str1,str2,...)

2.contcat_ws() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
3.如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

4.示例：

• 普通示例

mysql> select concat_ws(',','11','22','33');
+-------------------------------+
| concat_ws(',','11','22','33') |
+-------------------------------+
| 11,22,33 |
+-------------------------------+
1 row in set

 

mysql> select concat_ws(',',11,22,33);
+-------------------------+
| concat_ws(',',11,22,33) |
+-------------------------+
| 11,22,33 |
+-------------------------+
1 row in set

• 字符串为NULL示例

mysql> select concat_ws(',',NULL,22,33);
+---------------------------+
| concat_ws(',',NULL,22,33) |
+---------------------------+
| 22,33 |
+---------------------------+
1 row in set

• 分隔符为NULL示例

mysql> select concat_ws(NULL,11,22,33);
+--------------------------+
| concat_ws(NULL,11,22,33) |
+--------------------------+
| NULL |
+--------------------------+
1 row in set

 

三、group_concat函数

1.语法：group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])   from table_name group by column_name

2.函数会从字段中连接所有非 NULL 的字符串。如果没有非 NULL 的字符串，那么它就会返回 NULL。

3.示例:

mysql> select * from concat_demo;
+------+----+
| name | id |
+------+----+
| 100 | 1 |
| 200 | 1 |
| 200 | 1 |
| 300 | 1 |
| NULL | 2 |
| 100 | 2 |
| 200 | 2 |
| 300 | 2 |
| 100 | 3 |
| 200 | 3 |
| 300 | 3 |
+------+----+
11 rows in set

• 普通示例

mysql> select id,group_concat(name) from concat_demo;
+----+-----------------------------------------+
| id | group_concat(name) |
+----+-----------------------------------------+
| 1 | 100,200,200,300,100,200,300,100,200,300 |
+----+-----------------------------------------+
1 row in set

 

mysql> select id,group_concat(name) from concat_demo group by id;
+----+--------------------+
| id | group_concat(name) |
+----+--------------------+
| 1 | 100,200,200,300 |
| 2 | 100,200,300 |
| 3 | 100,200,300 |
+----+--------------------+
3 rows in set

• 含有distinct

mysql> select id,group_concat(distinct name) from concat_demo;
+----+-----------------------------+
| id | group_concat(distinct name) |
+----+-----------------------------+
| 1 | 100,200,300 |
+----+-----------------------------+
1 row in set

 

mysql> select id,group_concat(distinct name) from concat_demo group by id;
+----+-----------------------------+
| id | group_concat(distinct name) |
+----+-----------------------------+
| 1 | 100,200,300 |
| 2 | 100,200,300 |
| 3 | 100,200,300 |
+----+-----------------------------+
3 rows in set

• 根据name,进行倒序排列

mysql> select id,group_concat(name order by name desc) from concat_demo group by id;
+----+---------------------------------------+
| id | group_concat(name order by name desc) |
+----+---------------------------------------+
| 1 | 300,200,200,100 |
| 2 | 300,200,100 |
| 3 | 300,200,100 |
+----+---------------------------------------+
3 rows in set

• 以分隔符    ;   ,   '  NULL    将字符分开

mysql> select id,group_concat(name separator ',') from concat_demo group by id;
+----+----------------------------------+
| id | group_concat(name separator ',') |
+----+----------------------------------+
| 1 | 100,200,200,300 |
| 2 | 100,200,300 |
| 3 | 100,200,300 |
+----+----------------------------------+
3 rows in set

 

mysql> select id,group_concat(name separator ';
') from concat_demo group by id;
+----+----------------------------------+
| id | group_concat(name separator ';') |
+----+----------------------------------+
| 1 | 100;200;200;300 |
| 2 | 100;200;300 |
| 3 | 100;200;300 |
+----+----------------------------------+
3 rows in set

 

mysql> select id,group_concat(name separator 'v
') from concat_demo group by id;
+----+----------------------------------+
| id | group_concat(name separator 'v') |
+----+----------------------------------+
| 1 | 100v200v200v300 |
| 2 | 100v200v300 |
| 3 | 100v200v300 |
+----+----------------------------------+
3 rows in set

 

mysql> select id,group_concat(name separator '
') from concat_demo group by id;
+----+---------------------------------+
| id | group_concat(name separator '') |
+----+---------------------------------+
| 1 | 100200200300 |
| 2 | 100200300 |
| 3 | 100200300 |
+----+---------------------------------+
3 rows in set

 

mysql> select id,group_concat(name separator NULL) from concat_demo group by id;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL) from concat_demo group by id' at line 1