MySQL function Group_concat use method

Group_concat () is a function provided by the MySQL database, usually used with group by.

Grammar:

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 | +------------+---------+

There's no need to use PHP to 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 GRO Up by student_id "); $result = Explode (', ', $row [' courses ']);

2. Of course the separator can also be customized, by default, "," as the separator, to "| | |", then use separator to specify, 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 then concatenate them into strings, such as by courses_id 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 GR OUP by student_id; +------------+---------+ | student_id | Courses | +------------+---------+ | 2 | 5,4,3 | +------------+---------+

4. Attention should be paid to:

Connection traps for a.int fields

When you use Group_concat, please note that the connected fields, if they are int, must be converted to char and then spelled together.
Otherwise, after you execute (executescalar or any other method that performs SQL return results), the return will not be a comma-separated string,
But byte[].

This problem is not reflected in some of the tools such as SQLyog, so it is hard to find out.

Select Group_concat (IPAddress) from T_IP returns a comma-separated string
Select Group_concat (ID) from T_IP return byte[]
Select Group_concat (CAST (id as char)) from T_DEP returns a comma-separated string
Select Group_concat (Convert (ID, char)) from T_DEP returns a comma-separated string

Attached to the use of Cast,convert:
CAST (expr as type), convert (Expr,type), convert (expr USING transcoding_name)
The CAST () and CONVERT () functions can be used to get a value of one type and to produce a value of another type.

This type can be one of the following values:

binary[(N)]
char[(N)]
DATE
Datetime
DECIMAL
Signed [INTEGER]
Time
UNSIGNED [INTEGER]

B. Length Traps
After using the Group_concat, it does not work if the limit is used in the select.
When connecting fields with Group_concat, there is a length limit, not how much. But you can set it up a bit.

Using the Group_concat_max_len system variable, you can set the maximum allowable length.
The syntax for doing this in a 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 the maximum length.

After the set GLOBAL Group_concat_max_len = 10 is executed in SQLyog, the SQLyog is reopened and the setting takes effect