Mysql group_concat () function usage summary, mysqlgroup_concat

Mysql group_concat () function usage summary, mysqlgroup_concat

This example describes the usage of the mysql group_concat () function. We will share this with you for your reference. The details are as follows:

Group_concat (), which is described in the Manual:This function returns a string with a non-NULL value from a group of connections.. It is abstract and hard to understand.

Generally, group_concat () calculates which rows belong to the same group and displays the columns belonging to the same group. The columns to be returned are determined by the function parameter (that is, the field name. The group must have a standard, that is, grouping based on the columns specified by group.

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 find out which values are the same in the town. If the values are equal, all values are listed and separated by commas, as shown below:

Group_concat (town)

Beijing, Beijing
Changsha

2. test:

SELECT group_concat( town )FROM players

Result:

Group_concat (town)

Changsha, Beijing, Beijing,

Does the above prove that group_concat can produce results only when used together with the group by statement? The following is a practical test

3. Test the configuration impact of Constants on 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 are the differences between the two types?

SET @@global.GROUP_CONCAT_MAX_LEN=4;

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

4. Use statements

SELECT group_concat(town) FROM `players`

The result is as follows:
Group_concat (town)

Changsha, Beijing, Changsha, and Beijing

Conclusion: The group_concat () function must be used with the group by statement to obtain the desired effect.

The reason can be understood as follows: group_concat () obtains all the members of Group x (the column parameters in the function specify the fields to be displayed ). Where does group x come from? If you do not specify group by, you do not know which group group_concat () displays the members. Therefore, when there is no group by clause above, Changsha and Beijing are displayed.

When does this function actually need to be used?

If the query result is as follows: the group name is displayed on the left and all members in the group are displayed on the right. With this function, you can save a lot of things.

In addition, let me use: SELECT group_concat (name, sex) FROM 'players' town. Not significant. Group_concat () specifies a column, which is the best case. If multiple columns are specified. The displayed result is similar to the following:

group_concat(name,sex)

Wang Yu, Wang Xiaoming, Liu Hui, Shu Ming