Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to use the GROUP_CONCAT function in mysql, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

GROUP_CONCAT () is a function provided by the MySQL database, usually with GROUP

BY is used together. For more information, please refer to MySQL official document: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat.

Syntax:

GROUP_CONCAT ([DISTINCT] expr [, expr...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name...]] [SEPARATOR str_val]) 1. For example: 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 row 4 query 5 | +-+-+ this eliminates the need for a php loop $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'])

two。 Of course, the delimiter can also be customized. By default, the delimiter is ",". If you want to change it to "|", use SEPARATOR to specify it, for example:

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, the values of this group can be sorted and concatenated into strings, such as 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, in descending courses_id order. +-+-+ | student_id | courses | +-+-+ | 2 | 5pr 4pr 3 | +-+-+ 4. What to pay attention to:

Connection traps for the a.int field

When you use group_concat, please note that if the connected fields are int, be sure to convert them to char and then put them together.

Otherwise, after you execute (ExecuteScalar or any other method that executes SQL to return the result), the return will not be a comma-separated string

It's byte [].

This problem is not reflected in some tools such as SQLyog, so it is difficult to find it.

Select group_concat (ipaddress) from t_ip

Returns a comma-separated string

Select group_concat (id) from t_ip returns 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

With the usage of Cast,convert:

CAST (expr AS type), CONVERT (expr,type), CONVERT (expr USING)

Transcoding_name)

CAST () and CONVERT ()

Function 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 trap

After using group_concat, it will not work if limit is used in select.

There is a length limit when connecting fields with group_concat, not how many connections there are. But you can set it up.

Using the group_concat_max_len system variable, you can set the maximum length allowed.

The syntax for doing this 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

Then the result is up to this maximum length.

Execute SET GLOBAL group_concat_max_len = 10 in SQLyog

After that, reopen SQLyog and the settings will take effect.

The above is how to use the GROUP_CONCAT function in mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.