【MySQL】：CONCAT()、CONCAT_WS()、GROUP_CONCAT() 函数

目录
1. CONCAT() 
2. CONCAT_WS()
3. GROUP_CONCAT()

‍

1. CONCAT()

API：

CONCAT(str1,str2,...)

Desc：

• Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.
  • CONCAT() returns NULL if any argument is NULL.

Example1：

SELECT
  CONCAT( t.id, ',', t.NAME, ',', t.score ) AS info 
FROM
  tt2 t

2. CONCAT_WS()

API：

CONCAT_WS(separator,str1,str2,...)

Desc：

• CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments.
  • If the separator is NULL, the result is NULL.
  • CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

Example1：

SELECT
  CONCAT_WS(',', t.id, t.NAME, t.score ) AS info 
FROM
  tt2 t

Example2：

SELECT
  CONCAT_WS(NULL, t.id, t.NAME, t.score ) AS info 
FROM
  tt2 t

3. GROUP_CONCAT()

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:

API：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

Desc：

• In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the DISTINCT clause. To sort values in the result, use the ORDER BY clause. To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword. The default separator between values in a group is comma (,). To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''.
  • The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Example1：

SELECT
  t.NAME,
  GROUP_CONCAT( t.SUBJECT, t.score ORDER BY score ) AS info 
FROM
  `tt2` t 
GROUP BY
  t.NAME

Example2：

SELECT
  t.NAME,
  GROUP_CONCAT( CONCAT_WS( ":", t.SUBJECT, t.score ) ORDER BY score SEPARATOR "; " ) AS info 
FROM
  `tt2` t 
GROUP BY
  t.NAME

Example3：

SET SESSION group_concat_max_len = 999999;

参考：

concat()： https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat concat_ws()： https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat group_concat()： https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat