MySQL的GROUP_CONCAT函数

365次阅读

共计 2949 个字符，预计需要花费 8 分钟才能阅读完成。

随着业务的不断增长，最近一段需要对大量的历史数据整理，而在整理过程中有一张表的数据需要把其中一张表中一个不唯一的字段的列作为唯一字段在新表中而其中相关联的字段全部存入同一条记录中，刚刚开始想了一会，到后来在得知 GROUP_CONCAT 函数就可以解决，作用是在使用聚合函数时使用该函数返回一个字符串结果，并带有非 NULL 值一组中的值，如果没有非 NULL 值，则返回 NULL，自己用了下确实是不错，在这里就简单的记录说明一下，而 GROUP_CONCAT 函数使用也很简单

GROUP_CONCAT([DISTINCT] expr [,expr ...] 
             [ORDER BY {unsigned_integer | col_name | expr} 
                 [ASC | DESC] [,col_name ...]] 
             [SEPARATOR str_val])

举一个例子，如下：

DROP TABLE IF EXISTS `employee_tbl`; 
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL, 
  `name` char(10) NOT NULL DEFAULT '', 
  `date` datetime NOT NULL, 
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数', 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'); 
INSERT INTO `employee_tbl` VALUES ('2', '小王', '2016-04-20 15:25:47', '3'); 
INSERT INTO `employee_tbl` VALUES ('3', '小丽', '2016-04-19 15:26:02', '2'); 
INSERT INTO `employee_tbl` VALUES ('4', '小王', '2016-04-07 15:26:14', '4'); 
INSERT INTO `employee_tbl` VALUES ('5', '小明', '2016-04-11 15:26:40', '4'); 
INSERT INTO `employee_tbl` VALUES ('6', '小明', '2016-04-04 15:26:54', '2');

在这张表中需要在新表中记录 name、date 这 2 个字段的内容，其中需要 name 字段唯一，date 多条记录用逗号隔开存入同一字段, 在使用 GROUP_CONCAT 函数就可以很简单的查询出所需要的数据

MySQL> SELECT `name`, GROUP_CONCAT(date) 'date' FROM employee_tbl GROUP BY `name`; 
+--------+-------------------------------------------------------------+ 
| name   | date                                                        | 
+--------+-------------------------------------------------------------+ 
| 小丽   | 2016-04-19 15:26:02                                         | 
| 小明   | 2016-04-22 15:25:33,2016-04-11 15:26:40,2016-04-04 15:26:54 | 
| 小王   | 2016-04-20 15:25:47,2016-04-07 15:26:14                     | 
+--------+-------------------------------------------------------------+ 
3 rows in set (0.01 sec)

如此就把数据投影出来了，其中默认的分隔符是逗号，如果需要可以指定其他分隔符，如 ’;’

mysql> SELECT `name`, GROUP_CONCAT(date SEPARATOR ';') 'date' FROM employee_tbl GROUP BY `name`; 
+--------+-------------------------------------------------------------+ 
| name   | date                                                        | 
+--------+-------------------------------------------------------------+ 
| 小丽   | 2016-04-19 15:26:02                                         | 
| 小明   | 2016-04-22 15:25:33;2016-04-11 15:26:40;2016-04-04 15:26:54 | 
| 小王   | 2016-04-20 15:25:47;2016-04-07 15:26:14                     | 
+--------+-------------------------------------------------------------+ 
3 rows in set (0.01 sec)

总之在使用 GROUP_CONCAT 函数可以解决很多复制的问题，而在使用的过程中有几个需要注意的地方：

1、在查出来的结果在其中必须使用 GROUP BY 做其中一个关联字段做去重使得其中一个字段变成该列的唯一值做聚合

2、在使用 GROUP_CONCAT 函数时在 MySQL 中对其处理的结果最大长度（字符）限制，用了 GROUP_CONCAT 函数，SELECT 语句中的 LIMIT 语句起不了任何作用，缺省的字符串长度限制是 1024（个字符），如果处理的字段超过 1024 个字符时需要修改系统变量

mysql> SHOW GLOBAL VARIABLES LIKE 'group_concat_max_len'; 
+----------------------+-------+ 
| Variable_name        | Value | 
+----------------------+-------+ 
| group_concat_max_len | 1024  | 
+----------------------+-------+ 
1 row in set (0.01 sec)