A brief analysis of the use of concat and group_concat in MySQL

The examples used in this article are all executed under the following database table tt2:

##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;

There is a row in the middle that is null because the score value of a row in the tt2 table is 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:

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 need to be entered nine times.. .It’s so troublesome. 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:

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

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 It's 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:

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:

But if the same name appears multiple times, it looks like 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:

Example 8: Sort the above ID numbers from large to small, and use '_' as the separator:

Example 9: The above query Shows all the ids in each group grouped by name. Next we need to query the id and score of all groups grouped by name:

This article briefly analyzes the use of concat and group_concat in MySQL. For more related content, please pay attention to php Chinese net.

Related recommendations:

Introduction to the MySQL graphical management tool

Related explanations of the basic functions of MySQL stored procedures

Usage of EXISTS in SQL

The above is the detailed content of A brief analysis of the use of concat and group_concat in MySQL. For more information, please follow other related articles on the PHP Chinese website!