Useful group_concat functions in MySQL

See colleagues today's code, and learned a useful function, just look at the time do not understand, the search for the next use, see the following this article is very detailed on the direct reprint, I also write not so good, hehe, thank the author's selfless dedication.

Http://blog.sina.com.cn/s/blog_4e808acf01009qna.html Miky

Group_concat () is a function provided by the MySQL database, usually in conjunction with group by, depending on the official MySQL text block: http://dev.mysql.com/doc/refman/5.0/en/ Group-by-functions.html#function_group-concat.

Grammar:

Group_concat([DISTINCT] Expr[, expr ...][ORDERBy{Unsigned_integer | col_name | expr}[ASC |DESC][, Col_name ...]][SEPARATOR Str_val])

1. For example:
SELECT student_id, Group_concat(courses_id) As CoursesFrom student_coursesWHERE student_id=2GROUPby student_id;
+------------+---------+
| student_id | Courses |
+------------+---------+
|2 |3,4,5 | 
 +------------+---------+ 
 This does not require PHP looping 
  $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 [ ' courses ' ]); 
 
                 /span>  

2. Of course, the delimiter can also be customized, the default is "," as the delimiter, to change to "| | |", then use separator to specify, for example:

SELECT student_id, Group_concat(courses_id SEPARATOR' | | | ') As CoursesFrom student_coursesWHERE student_id=2GROUPby student_id;
+------------+---------+
| student_id | Courses |
+------------+---------+
|2 |3| | |4| | |5 |
+------------+---------+

3. In addition, you can sort the values of this group and concatenate them into strings, for example, by courses_id descending:
SELECT student_id, Group_concat (courses_id order by courses_id descfrom Student_courses where student_id= 2 group by student_id; 
 +------------+---------+ 
 | student_id | courses | 
 +------------+---------+ 
 | 2 | 5,4,3 | 
 +------------+---------+ 
 
 4. To be aware of: 
          

Connection traps for a.int fields

When you use the Group_concat, please note that the connected field if it is of type int, it must be converted to char and then put together,
Otherwise, a comma-separated string will not be returned after you execute (executescalar or any other method that executes SQL to return the result).
But byte[].

This problem is difficult to find when you are in some tools such as SQLyog.

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

The use of the attached Cast,convert:
CAST (expr as type), convert (Expr,type), convert (expr usingtranscoding_name)
The CAST () and CONVERT () functions can be used to get a value of one type and 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 Group_concat, the Select has no effect if the limit is used.
There is a length limit for connecting fields with Group_concat, not how many are connected. 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 up to this maximum length.

After you execute set GLOBAL Group_concat_max_len = 10 in SQLyog, the settings take effect when you reopen the SQLyog.

Useful group_concat functions in MySQL