MySQL multiple field stitching

MySQL Query results row field stitching, can be implemented with the following two functions:

1. Concat function

Mysql> Select Concat (' 1 ', ' 2 ', ' 3 ') from test; +---------------------+| Concat (' 1 ', ' 2 ', ' 3 ') |+---------------------+| 123 |+---------------------+

Assuming null exists in the connection string, the returned result is null:

Mysql> Select Concat (' 1 ', ' 2 ', NULL, ' 3 ') from test; +--------------------------+| Concat (' 1 ', ' 2 ', NULL, ' 3 ') |+--------------------------+| NULL |+--------------------------+

2. Concat_ws function

Concat (Separator,str1,str2,...) represents concat with separator, which is a special form of concat (). The first parameter is a delimiter for the other parameters. The position of the delimiter is placed between the two strings to be concatenated. The delimiter can be a string, or it can be a different parameter.

Mysql> Select Concat_ws (': ', ' 1 ', ' 2 ', ' 3 ') from test; +----------------------------+| Concat_ws (': ', ' 1 ', ' 2 ', ' 3 ') |+----------------------------+| 1:2:3 |+----------------------------+

Delimiter is null, the returned result is null:

Mysql> Select Concat_ws (NULL, ' 1 ', ' 2 ', ' 3 ') from test; +-----------------------------+| Concat_ws (NULL, ' 1 ', ' 2 ', ' 3 ') |+-----------------------------+| NULL | +-----------------------------+

If NULL is present in the parameter, it is ignored:

Mysql> Select Concat_ws (': ', ' 1 ', ' 2 ', Null,null,null, ' 3 ') from test; +-------------------------------------------+ | Concat_ws (': ', ' 1 ', ' 2 ', Null,null,null, ' 3 ') |+-------------------------------------------+| 1:2:3 |+-------------------------------------------+

The ability to infer null and replace it with other values:

Mysql> Select Concat_ws (': ', ' 1 ', ' 2 ', ifnull (NULL, ' 0 '), ' 3 ') from bank limit 1; +---------------------------------------------+| Concat_ws (': ', ' 1 ', ' 2 ', ifnull (NULL, ' 0 '), ' 3 ') |+---------------------------------------------+| 1:2:0:3 | +---------------------------------------------+

MySQL multiple field stitching