Detailed description of the GROUP_CONCAT () function in MySql

The GROUP_CONCAT () function can be called a connection field in mysql. However, when we use the GROUP_CONCAT () function, we find that this function is not that simple. Next we will introduce the usage of GROUP_CONCAT.

Syntax:

The Code is as follows:	Copy code
GROUP_CONCAT ([DISTINCT] expr [, expr...] [Order by {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name...] [SEPARATOR str_val])

1. For example:

The Code is as follows:	Copy code
SELECT student_id, GROUP_CONCAT (courses_id) AS courses FROM student_courses WHERE student_id = 2 group by student_id; + ---- + --- + | Student_id | courses | + ---- + --- + | 2 | 3, 4, 5 | + ---- + --- +

This requires no php loop.

The Code is as follows:	Copy code
$ Row = $ pdo-> query ("SELECT student_id, GROUP_CONCAT (courses_id) AS courses FROM student_courses WHERE student_id = 2 group by student_id "); $ Result = explode (',', $ row ['Course']);

2. Of course, the SEPARATOR can also be customized. The default Delimiter is ",". To change it to "|", use SEPARATOR. For example:

 

The Code is as follows:	Copy code
SELECT student_id, GROUP_CONCAT (courses_id SEPARATOR '|') AS courses FROM student_courses WHERE student_id = 2 group by student_id; + ---- + --- + | Student_id | courses | + ---- + --- + | 2 | 3 | 4 | 5 | + ---- + --- +

3. In addition, you can sort the values of this group and connect them to strings. For example, sort the values by courses_id in descending order:

The Code is as follows:	Copy code
SELECT student_id, GROUP_CONCAT (courses_id order by courses_id DESC) AS courses FROM student_courses WHERE student_id = 2 group by student_id; + ---- + --- + | Student_id | courses | + ---- + --- + | 2 | 5, 4, 3 | + ---- + --- +

PHP code

The Code is as follows:	Copy code
$ Row = $ pdo-> query ("SELECT student_id, GROUP_CONCAT (courses_id) AS courses FROM student_courses WHERE student_id = 2 group by student_id "); $ Result = explode (',', $ row ['Course']);

The SEPARATOR can also be customized. The default Delimiter is ",". To change it to "|", use SEPARATOR to specify it. For example:

4. Notes:

A.int field connection trap

When using group_concat, note that if the connected field is of the int type, it must be converted to char and then combined,
Otherwise, after you execute the statement (ExecuteScalar or any other method that executes the SQL return result), the returned result will not be a comma-separated string,
Instead, it is byte [].

This problem cannot be found in SQLyog or other tools.

The Code is as follows:	Copy code
Select group_concat (ipaddress) from t_ip returns comma-separated strings Select group_concat (id) from t_ip return byte [] Select group_concat (CAST (id as char) from t_dep returns comma-separated strings Select group_concat (Convert (id, char) from t_dep returns comma-separated strings

The following shows the function. First, create a student_courses table and fill in some test data.

SQL code

The Code is as follows:	Copy code
Create table student_courses ( Student_id int unsigned not null, Courses_id int unsigned not null, KEY (student_id) ); Insert into student_courses VALUES (1, 1), (1, 2), (2, 3), (2, 4), (2, 5 );

To find the course selected by student ID 2, use the following SQL statement:

SQL code

The Code is as follows:	Copy code
Mysql> SELECT student_id, courses_id FROM student_courses WHERE student_id = 2; + ------------ + | Student_id | courses_id | + ------------ + | 2 | 3 | | 2 | 4 | | 2 | 5 | + ------------ + 3 rows in set (0.00 sec)

 

The output result contains three records, indicating that students with the student ID 2 have chosen the courses 3, 4, and 5.
Put it in PHP, you must use a loop to get the three records, as shown below:

The Code is as follows:	Copy code
Foreach ($ pdo-> query ("SELECT student_id, courses_id FROM student_courses WHERE student_id = 2") as $ row ){ $ Result [] = $ row ['courses _ id']; }

However, using the GROUP_CONCAT () function and the group by statement is very simple, as shown below:

SQL code

The Code is as follows:	Copy code
Mysql> SELECT student_id, GROUP_CONCAT (courses_id) AS courses FROM student_courses WHERE student_id = 2 group by student_id; + ------------ + --------- + | Student_id | courses | + ------------ + --------- + | 2 | 3, 4, 5 | + ------------ + --------- + 1 row in set (0.00 sec)

In this way, php processing is simple:
 

The Code is as follows:	Copy code
$ Row = $ pdo-> query ("SELECT student_id, GROUP_CONCAT (courses_id) AS courses FROM student_courses WHERE student_id = 2 group by student_id "); $ Result = explode (',', $ row ['Course']);

Attached Cast and convert usage:

CAST (expr AS type), CONVERT (expr, type), CONVERT (expr USING transcoding_name)
CAST () and CONVERT () functions can be used to obtain values of one type and generate values of another type.

This type can be one of the following values:

The Code is as follows:	Copy code
BINARY [(N)] CHAR [(N)] DATE DATETIME DECIMAL SIGNED [INTEGER] TIME UNSIGNED [INTEGER]

B. Length trap

After group_concat is used, it does not work if limit is used in select.
When using group_concat to connect a field, there is a limit on the length, not the number of connections. But you can set it.

Using group_concat_max_len system variables, you can set the maximum allowed length.
The syntax for this operation in the program is as follows, where val is an unsigned integer:
SET [SESSION | GLOBAL] group_concat_max_len = val;
If the maximum length has been set, the result is ended with the maximum length.

Run set global group_concat_max_len = 10 in SQLyog and re-open SQLyog. The setting will take effect.

Parameter settings and restrictions

1. View settings on the server

The Code is as follows:	Copy code
Mysql> show variables like '% group_concat % '; + ---------------------- + ------- + | Variable_name | Value | + ---------------------- + ------- + | Group_concat_max_len| 1024 | + ---------------------- + ------- +

1 row in set (0.00 sec) the value above indicates that the current default length is 1 kb.

2. Change the parameter value

Method 1: Modify the parameters in the configuration file and add group_concat_max_len = 10240.
Method 2: Implement in session, global or current session

The Code is as follows:	Copy code
Set global group_concat_max_len = 10240; Set session group_concat_max_len = 10240;