MySQL connection string function Concat,concat_ws,group_concat Usage Summary

In MySQL, two strings added cannot use the + sign, plus is used to add numbers, in MySQL to connect strings need to use the concat or CONCAT_WS function:

Mysql concat function

Grammar:

CONCAT (Str1,str2 ...)

For example:

Select Ocncat (' AB ', ' CD ')

Will output ABCD

It is important to note that:

SELECT CONCAT (' abc ', NULL) where a string is null when NULL is output

Mysql's Concat_ws function

The CONCAT_WS function () function, which represents concat with separator, is a delimited string connection

Select Concat_ws (', ', ' 11 ', ' 22 ', ' 33 ');

Output:

11,22,33

It is important to note that NULL is not returned in the parameters of Concat_ws, such as Concat_ws (', ', ' AB ', NULL) will output AB

MySQL's Group_concat () function

As you can see from the name, this function is an aggregate function that can be used in a group statement to combine multiple lines of strings into a single string.

Grammar:

Group_concat ([DISTINCT] expr [, expr ...] [ORDER by {unsigned_integer | col_name | expr}             [ASC | DESC] [, col_name ...] [SEPARATOR Str_val])

Assume that there is a table Userrole stores the user's corresponding role relationship, which has two columns of userid,rolecode, and we can remove the comma-delimited role by Group_concat

Select Userid,group_concat (Rolecode SEPARATOR ', ') from Userrole

It is important to note that the default maximum concatenated string length of the Group_concat function is 1024, which is truncated if the concatenated string is longer than 1024, but we can set the group_concat_max_len Value to modify the maximum length of the group_concat.

For example:

SET SESSION group_concat_max_len= 99999;select userid,group_concat (rolecode SEPARATOR ', ') from  userrole

MySQL connection string function Concat,concat_ws,group_concat Usage Summary