MySQL's group_concat function

As the business continues to grow, the most recent need for a large number of historical data, and in the process of a table of data need to be one of the table in one of the columns of a unique field in the new table and the associated fields are all stored in the same record, just began to think for a while, and then learned that group The _concat function can be resolved by using this function when using an aggregate function to return a string result with a value in a set of non-null values, and return NULL if there is no non-null value, which is really good for yourself, here is a brief description of the record, and Group_ Concat function is also very simple to use

Group_concat ([DISTINCT] expr [, expr ...] [ORDER by {unsigned_integer | col_name | expr} [ASC |             DESC] [, col_name ...] [SEPARATOR Str_val])

To cite an example, the following:

drop table if exists  ' Employee_tbl '; create table  ' Employee_tbl '   (   ' id '  int (one)  NOT NULL,   ' name ' '  char  NOT NULL DEFAULT  ',   ' Date '  datetime not null,    ' Singin '  tinyint (4)  NOT NULL DEFAULT  ' 0 '  COMMENT  ' login count ',   PRIMARY KEY  (' id '))  ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO  ' Employee_tbl '  VALUES  (' 1 ',  ' Xiao Ming ',  ' 2016-04-22 15:25:33 ',  ' 1 '); Insert into   ' employee_tbl '  VALUES  (' 2 ',  ' Xiao Wang ',  ' 2016-04-20 15:25:47 ',  ' 3 ');insert  into  ' employee_tbl '  VALUES  (' 3 ',  ' Xiao Li ',  ' 2016-04-19 15:26:02 ',  ' 2 '); INSERT  INTO  ' employee_tbl '  VALUES  (' 4 ',  ' Xiao Wang ',  ' 2016-04-07 15:26:14 ',  ' 4 '); nsert into  ' employee_tbl '  VALUES  (' 5 ',  ' Xiao Ming ',  ‘ 2016-04-11 15:26:40 ',  ' 4 ');insert into  ' employee_tbl '  VALUES  (' 6 ',  ' xiaoming ',   ' 2016-04-04 15:26:54 ',  ' 2 ');

In this table you need to record the name, date 2 fields in the new table, where the Name field is unique, the date multiple records are separated by commas into the same field, the use of the Group_concat function can be very simple to query out the required data

mysql> select  ' name ',  group_concat (date)   ' Date '  from employee_tbl group  BY  ' name '; +--------+-------------------------------------------------------------+| name    | date                                                           |+--------+-------------------------------------------------------------+|   Little Li    | 2016-04-19 15:26:02                                           | |   Xiao Ming    | 2016-04-22 15:25:33,2016-04-11 15:26:40,2016-04-04 15:26:54 | |   Xiao Wang    | 2016-04-20 15:25:47,2016-04-07 15:26:14                      |+------ --+-------------------------------------------------------------+3 rows in set  (0.01  Sec

The

Then projects the data, where the default delimiter is a comma, and if you need to specify a different delimiter, such as '; '

mysql> select  ' name ',  group_concat (date separator  '; ')   ' Date '  FROM employee_tbl GROUP BY  ' name '; +--------+---------------------------- ---------------------------------+| name   | date                                                           |+--------+--------------- ----------------------------------------------+|  Xiao Li    | 2016-04-19 15:26:02                                            | |   Xiao Ming     | 2016-04-22 15:25:33;2016-04-11 15:26:40;2016-04-04 15:26:54 | |   Xiao Wang    | 2016-04-20 15:25:47;2016-04-07 15:26:14                      |+------ --+-------------------------------------------------------------+3 rows in set  (0.01  Sec

In short, the use of the GROUP_CONCAT function can solve many of the problems of replication, and in the process of using a few things to note:

1, in the results of the findings in which you must use group by to do one of the associated fields to do the redo so that one of the fields into the column's unique values to do the aggregation

2, when using the Group_concat function in MySQL in the maximum length of the result of processing (character) limit, with the Group_concat function, the limit statement in the SELECT statement does not play any role, the default string length limit is 1024 (characters), If you need to modify a system variable if you are working with a field that exceeds 1024 characters

Mysql> SHOW GLOBAL VARIABLES like ' group_concat_max_len '; +----------------------+-------+| variable_name | Value |+----------------------+-------+| Group_concat_max_len | 1024x768 |+----------------------+-------+1 row in Set (0.01 sec)

3. When the concatenated field is of type int, the lower version or the result returned is not a comma-delimited string, but byte[]. At this point, you need to convert with the cast or convert function.

This article from "Jim's Technical Essay" blog, declined to reprint!

MySQL's group_concat function