Mysql group_concat() function usage summary_MySQL

The example in this article describes the usage of mysql group_concat() function. Share it with everyone for your reference, the details are as follows:

group_concat(), the manual states: This function returns the string result with a non-NULL value from the concatenation of a group. Relatively abstract and difficult to understand.

To understand it in a simple way, it is actually like this: group_concat() will calculate which rows belong to the same group and display the columns belonging to the same group. Which columns to return are determined by the function parameters (field names). There must be a standard for grouping, which is to group based on the column specified by group by.

The group_concat function should execute the group by statement internally. This is my guess.

1. Test statement:

SELECT group_concat(town) FROM `players` group by town

As a result, search the town to find which values ​​are the same. If they are equal, list them all, separated by commas, as follows:

group_concat(town)

Beijing, Beijing
Changsha

2. Test:

SELECT group_concat( town )
FROM players

Result:

group_concat(town)

Changsha,Beijing,Beijing,

Can the above prove that group_concat can only have an effect when used together with the group by statement? The actual test is carried out below

3. The impact of test constants on the configuration of group_concat():

SET @@GROUP_CONCAT_MAX_LEN=4

The syntax mentioned in the manual is as follows:

SET [SESSION | GLOBAL] group_concat_max_len = val;

What’s the difference between the 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 result is:
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 required effect.

The reason can be understood like this: group_concat() gets all members belonging to group x (the column parameters in the function specify which fields need to be displayed). Where did group x come from? If there is no group by specified, then there is no way to know which group group_concat() will display the members according to. Therefore, when there is no group by clause like above, Changsha and Beijing are displayed.

When is this function actually needed?

Suppose the query result is as follows: the group name is displayed on the left, and all member information of the group is displayed on the right. Using this function, you can save a lot of things.

Also, if I use it like 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 multiple columns are specified. Then the displayed result is similar to this:

group_concat(name,sex)

Wang Tao, Wang Xiaoming male, Liu Hui female, Shu Ming female

Readers who are interested in more MySQL-related content can check out the special topics on this site: "A Complete Collection of MySQL Log Operation Skills", "A Summary of MySQL Transaction Operation Skills", "A Complete Collection of MySQL Stored Procedure Skills", and "A Summary of MySQL Database Lock Related Skills" 》and《Summary of commonly used functions in MySQL》

I hope this article will be helpful to everyone in MySQL database planning.