Use the MySQL function to group_concat a little bit of attention

This function is a very useful function, for example, we can use in many-to-many association tables for batch queries.

For example, students and teachers are many-to-many relationships, if we want to query the specific teachers of some of the students have what, the general situation, we may traverse teacher_id, to get the corresponding student collection, so that the efficiency is very poor, but with the Group_concat function , you can use the following methods to get

Select Teacher_id,group_concat (student_id) from teache_student_mapping where teacher_id in (Teacher_id1,teacher_id2, TEACHER_ID3) GROUP BY teacher_id

In this way, each teacher's corresponding student collection is acquired.

For example, in the use of Lucene and other full-text search search engine is not support table association, so to store this association, one way is to connect the data with some specific characters, and our data source is often a database, this time, Group_concat again come in handy, Usage or the same as above, I'm not going to say much.

But here is a hidden problem, that is, the string composed of Group_concat has a length limit, has not known this problem, the specific can be

Show VARIABLES like ' Group_concat_max_len ' to query

If you want to modify this information, you can

SET Group_concat_max_len = 20000

Modify current session This parameter value

or modify the configuration file

Group_concat_max_len = 102400

Make permanent settings

After changing the configuration file, remember to restart the database.