Home > Database > Mysql Tutorial > body text

Summary of how to use the group_concat() function in mysql

怪我咯
Release: 2017-03-30 10:13:31
Original
1534 people have browsed it


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
Copy after login

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
Copy after login

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
Copy after login

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


SET [SESSION | GLOBAL] group_concat_max_len = val;
Copy after login

What is the difference between the two?


SET @@global.GROUP_CONCAT_MAX_LEN=4;
Copy after login

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


4. Use the statement

SELECT group_concat(town) FROM `players`
Copy after login

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)
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template