Summary of how to use the group_concat() function in 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 a ## with a non-NULL value of the connection from a group #StringResult. 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 parameter (which is the field name). GroupingThere must be a standard, which is to group according to 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

The result is to find which values ​​in town are the same. If they are equal, list them all, separated by commas. Divide and list 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 be used with the group by statement? Can it be effective if used at the same time? The following is an actual test


3. TestConstantThe impact on the configuration of group_concat():

SET @@GROUP_CONCAT_MAX_LEN=4

The syntax for setting mentioned in the manual is as follows:

SET [SESSION | GLOBAL] group_concat_max_len = val;

What is 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 the statement

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 do you need to use this function in practice?
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.

In addition, if I use it like this: SELECT group_concat( name, sex ) FROM `players` town. It is not meaningful. group_concat() specifying a column is the best case. If multiple columns are specified. Then the display result is similar to this:

group_concat(name,sex)

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

The above is the detailed content of Summary of how to use the group_concat() function in mysql. For more information, please follow other related articles on the PHP Chinese website!