MySQL group_concat () function Usage Summary _mysql

This example describes the use of the MySQL Group_concat () function. Share to everyone for your reference, specific as follows:

Group_concat (), the manual explains: This function returns a string result with a non-null value of a connection from a group . More abstract, difficult to understand.

The popular point of understanding, in fact, is this:Group_concat () calculates which rows belong to the same group and displays the columns that belong to the same group. Which columns to return is determined by the function argument (that is, the field name). Groupings must have a standard, grouped by the columns specified by group BY.

The Group_concat function should have executed the group by statement internally, which is my guess.

1. Test statement:

SELECT Group_concat (town) from ' players ' group by town

The result is to look up town to find out which values are the same, and if they are equal, list them all, listed as comma-separated, as follows:

Group_concat (town)

BEIJING, Beijing
Changsha

2. Test:

SELECT Group_concat (town) from
players

Results:

Group_concat (town)

Changsha, Beijing, Beijing,

Does the above prove that Group_concat only works with the group by statement to be effective? The following is a practical quiz

3. Test the configuration effect of constants on Group_concat ():

SET @ @GROUP_CONCAT_MAX_LEN =4

The syntax for setting is mentioned in the manual:

SET [Session | GLOBAL] Group_concat_max_len = val;

What's the difference between these two?

SET @ @global. group_concat_max_len=4;

Global can be omitted, then it becomes: SET @ @GROUP_CONCAT_MAX_LEN = 4;

4. Use statements

SELECT Group_concat (town) from ' players '

The results are as follows:
Group_concat (town)

Changsha, Beijing, Changsha, Beijing

Conclusion: the Group_concat () function needs to be used together with the group by statement to get the desired effect.

The reason can be understood as follows: Group_concat () gets all the members that belong to the X group (the column parameters in the function specify which fields need to be displayed). Where does the X group come from? If there is no group by to specify, then there is no known group_concat () according to which group to display the members. So, like there is no GROUP BY clause above, it shows Changsha and Beijing.

When do you actually need to use this function?

If you need to query the result is this: Display the group name on the left, the right side want to display all members of the information under this group. With this function, you can save a lot of things.

Also, if I use this: SELECT group_concat (name, sex) from ' players ' town. It doesn't make much sense. Group_concat () Specifying a column is the best case. If more than one column is specified. The result looks like this:

Group_concat (Name,sex)

Wang Yu, Wang Xiaoming male, Liu Huiju, Shuming

More information about MySQL interested readers can view the site topics: "MySQL Log operation skills Daquan", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL commonly used function large summary"

I hope this article will help you with the MySQL database meter.