Mysql 函数concat、concat_ws和group_concat
本文介绍的是MySQL中3个函数的使用，主要是针对字符串的连接合并处理：
-
concat
-
concat_ws
-
group_concat
<!--MORE-->
concat
concat()函数是将多个字符串组合在一起，形成一个大的字符串；如果连接的字符串中存在一个为NULL，则输出的结果为NULL，语法格式为：
1
concat(str1,str2,....strn)
3个例子????说明具体使用，以下面这个表中的第一条记录为例：
1
-- 1、字符之间不加连接符
2
mysql> select concat("01","赵雷","男");
3
+-----------------------------+
4
| concat("01","赵雷","男") |
5
+-----------------------------+
6
| 01赵雷男 |
7
+-----------------------------+
8
1 row in set (0.00 sec)
9
10
-- 2、字符之间添加连接符
11
mysql> select concat("01-","赵雷-","男");
12
+-------------------------------+
13
| concat("01-","赵雷-","男") |
14
+-------------------------------+
15
| 01-赵雷-男 |
16
+-------------------------------+
17
1 row in set (0.00 sec)
18
19
-- 3、忽略空字符串
20
mysql> mysql> select concat("01","赵雷","","男");
21
+--------------------------------+
22
| concat("01","赵雷","","男") |
23
+--------------------------------+
24
| 01赵雷男 |
25
+--------------------------------+
26
1 row in set (0.00 sec)
27
28
29
-- 4、存在NULL的情况
30
mysql> select concat("01","赵雷",NULL,"男"); -- 结果直接显示为NULL
31
+----------------------------------+
32
| concat("01","赵雷",NULL,"男") |
33
+----------------------------------+
34
| NULL |
35
+----------------------------------+
36
1 row in set (0.01 sec)
上面的NULL是MySQL中NULL，如果NULL本身就是字符串，则结果不相同：
1
mysql> select concat("01","赵雷","NULL","男");
2
+------------------------------------+
3
| concat("01","赵雷","NULL","男") |
4
+------------------------------------+
5
| 01赵雷NULL男 |
6
+------------------------------------+
7
1 row in set (0.01 sec)
注意两种情况的不同：
concat_ws
concat_ws()函数相比较于concat()多了一个指定的连接符号，语法为：
1
concat_ws(separator, str1, str2, str3)
-
第一个参数是连接的符号
-
后面的参数是待连接的字符
连接符要放在待连接的字符之间；分隔符也可以是一个字符串，也可以是其他的参数，需要注意的是：
-
如果分隔符是NULL，结果为NULL
-
函数后忽略任何分割符参数后的NULL值（分隔符之后的NULL值）：连接的时候跳过NULL值
-
concat_ws不会忽略空字符串；concat会忽略空字符串
下面通过几个例子来说明使用方法：
1
-- 1、指定不同的连接符号：分别指定逗号和加号
2
3
mysql> select concat_ws(",","01","赵雷","男");
4
+------------------------------------+
5
| concat_ws(",","01","赵雷","男") |
6
+------------------------------------+
7
| 01,赵雷,男 |
8
+------------------------------------+
9
1 row in set (0.00 sec)
10
11
mysql> select concat_ws("+","01","赵雷","男");
12
+------------------------------------+
13
| concat_ws("+","01","赵雷","男") |
14
+------------------------------------+
15
| 01+赵雷+男 |
16
+------------------------------------+
17
1 row in set (0.00 sec)
18
19
-- 2、不忽略空字符串
20
mysql> select concat_ws("+","01","赵雷","","男");
21
+---------------------------------------+
22
| concat_ws("+","01","赵雷","","男") |
23
+---------------------------------------+
24
| 01+赵雷++男 |
25
+---------------------------------------+
26
1 row in set (0.00 sec)
27
28
-- 3、忽略NULL；不管几个NULL都会忽略
29
mysql> select concat_ws("+","01","赵雷",NULL,"男");
30
+-----------------------------------------+
31
| concat_ws("+","01","赵雷",NULL,"男") |
32
+-----------------------------------------+
33
| 01+赵雷+男 |
34
+-----------------------------------------+
35
1 row in set (0.00 sec)
36
37
-- 忽略两个NULL
38
mysql> select concat_ws("+","01",NULL,"赵雷",NULL,"男");
39
+----------------------------------------------+
40
| concat_ws("+","01",NULL,"赵雷",NULL,"男") |
41
+----------------------------------------------+
42
| 01+赵雷+男 |
43
+----------------------------------------------+
44
1 row in set (0.00 sec)
group_concat
group：分组的意思；concat：连接。合起来就是分组连接，具体语法为：
1
GROUP_CONCAT(DISTINCT expression ORDER BY expression SEPARATOR sep);
-
DISTINCT子句用于在连接分组之前消除组中的重复值
-
ORDER BY 连接之前按升序或者降序排列。默认是升序
-
SEPARATOR指定在组中的值之间插入的文字值。如果不指定分隔符，则GROUP_CONCAT函数使用逗号(，)作为默认分隔符
-
函数会自动忽略NULL值，如果所有的参数都是NULL，则结果返回NULL
-
GROUP_CONCAT函数返回二进制或非二进制字符串，取决于参数。 默认情况下，返回字符串的最大长度为1024。通过在SESSION或GLOBAL级别设置group_concat_max_len系统变量来扩展最大长度。
1
set session group_concat_max_len=18783847439738273; -- 防止超出范围数据被截掉
下面通过这张成绩表Score来讲解：
1
-- 1、将每个学生的成绩单独列出来
2
mysql> select s_id, group_concat(s_score) from Score group by s_id;
3
+------+-----------------------+
4
| s_id | group_concat(s_score) |
5
+------+-----------------------+
6
| 01 | 80,90,96 |
7
| 02 | 70,60,80 |
8
| 03 | 80,81,85 |
9
| 04 | 50,40,30 |
10
| 05 | 76,87 |
11
| 06 | 43,56 |
12
| 07 | 89,94 |
13
+------+-----------------------+
14
7 rows in set (0.01 sec)
15
16
-- 2、指定连接符+
17
mysql> select s_id, group_concat(s_score separator "+") from Score group by s_id;
18
+------+-------------------------------------+
19
| s_id | group_concat(s_score separator "+") |
20
+------+-------------------------------------+
21
| 01 | 80+90+96 |
22
| 02 | 70+60+80 |
23
| 03 | 80+81+85 |
24
| 04 | 50+40+30 |
25
| 05 | 76+87 |
26
| 06 | 43+56 |
27
| 07 | 89+94 |
28
+------+-------------------------------------+
29
7 rows in set (0.00 sec)
30
31
-- 3、指定排序的字段
32
-- 分数s_score已经完成了排序（指定了降序）；上面的结果不指定则默认是降序
33
mysql> select s_id, group_concat(distinct s_score order by s_score desc separator "+") from Score group by s_id;
34
+------+--------------------------------------------------------------------+
35
| s_id | group_concat(distinct s_score order by s_score desc separator "+") |
36
+------+--------------------------------------------------------------------+
37
| 01 | 96+90+80 |
38
| 02 | 80+70+60 |
39
| 03 | 85+81+80 |
40
| 04 | 50+40+30 |
41
| 05 | 87+76 |
42
| 06 | 56+43 |
43
| 07 | 94+89 |
44
+------+--------------------------------------------------------------------+
45
7 rows in set (0.00 sec)
46
47
48
-- 4、去重操作
49
-- distinct s_score表示对分数去重，取出每个学生的不同分数（表中每个学生的分数都不相同，结果同上）
50
mysql> select s_id, group_concat(distinct s_score order by s_score desc separator "+") from Score group by s_id;
51
+------+--------------------------------------------------------------------+
52
| s_id | group_concat(distinct s_score order by s_score desc separator "+") |
53
+------+--------------------------------------------------------------------+
54
| 01 | 96+90+80 |
55
| 02 | 80+70+60 |
56
| 03 | 85+81+80 |
57
| 04 | 50+40+30 |
58
| 05 | 87+76 |
59
| 06 | 56+43 |
60
| 07 | 94+89 |
61
+------+--------------------------------------------------------------------+
62
7 rows in set (0.00 sec)
distinct 和order by 后面的字段是相同的
