MySQL: A brief introduction to the use of concat and group_concat

php是最好的语言

Release： 2018-07-26 16:57:21

Original

2856 people have browsed it

The function of the concat() function: concatenate multiple strings into one string. Syntax: concat(str1, str2,...) returns the string generated by the connection parameters. If any parameter is null, the return value is null.

group_concat turns multiple rows of values into one row after grouping. The original values in each row are separated by commas

Note:

The examples used in this article are all below Execute under database table tt2:

MySQL: A brief introduction to the use of concat and group_concat

1. concat() function

1. Function: Concatenate multiple strings into one string.

2. Syntax: concat(str1, str2,...)

The return result is the string generated by the connection parameters. If any parameter is null, the return value is null.

3. Example:

Example 1: select concat (id, name, score) as info from tt2;

MySQL: A brief introduction to the use of concat and group_concat

There is one in the middle The behavior is null because there is a row in the tt2 table with a score value of null.

Example 2: In the result of Example 1, the combination of the three fields id, name, and score has no separator. We can add a comma as the separator:

MySQL: A brief introduction to the use of concat and group_concat

This seems to be a lot more pleasing to the eye~~

But entering the sql statement is a lot of trouble. Three fields need to enter commas twice. If there are 10 fields, commas have to be entered nine times...It's so troublesome Okay, is there any easy way? ——Then concat_ws(), which can specify the separator between parameters, is here! ! !

2. concat_ws() function

1. Function: Same as concat(), concatenate multiple strings into A string, but you can specify the separator at once ~ (concat_ws is concat with separator)

2. Syntax: concat_ws(separator, str1, str2, ...)

Description: Chapter One parameter specifies the delimiter. It should be noted that the separator cannot be null. If it is null, the return result will be null.

3. Example:

Example 3: We use concat_ws() to specify the separator as a comma to achieve the same effect as Example 2:

MySQL: A brief introduction to the use of concat and group_concat

Example 4: Specify the separator as null, and all the results become null:

MySQL: A brief introduction to the use of concat and group_concat

3. group_concat() function

Preface: In the query statement with group by, the field specified by select is either included after the group by statement as the basis for grouping, or is included in the aggregate function. (For knowledge about group by, please click: A brief analysis of the use of Group By in SQL).

Example 5:

MySQL: A brief introduction to the use of concat and group_concat

This example queries the smallest ID among people with the same name. What if we want to query all the IDs of people with the same name?

Of course we can query like this:

Example 6:

MySQL: A brief introduction to the use of concat and group_concat

But if the same name appears multiple times, it seems very unintuitive. . Is there a more intuitive way that allows each name to appear only once, while also displaying the IDs of all people with the same name? ——Use group_concat()

1. Function: Connect the values in the same group generated by group by and return a string result.

2. Syntax: group_concat( [distinct] Field to be connected [order by sorting field asc/desc ] [separator 'separator'] )

Description: Duplicates can be excluded by using distinct Value; if you want to sort the values in the result, you can use the order by clause; separator is a string value, which defaults to a comma.

3. Example:

Example 7: Use group_concat() and group by to display the ID numbers of people with the same name:

MySQL: A brief introduction to the use of concat and group_concat