MySQL String Stitching Detailed

There are two types of string connection operations in the MySQL database.
A. Syntax:
1. CONCAT (string1,string2,...) Description: String1,string2 represents a string, and the CONCAT function will return NULL if one is null when the string is concatenated.
Example 1:

Example 2:

2. Concat_ws (SEPARATOR,STR1,STR2,...)
Description: String1,string2 represents a String, Concat_ws represents concat with separator, and the first parameter is a delimiter for 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 another parameter. If the delimiter is null, the result is null. The function ignores NULL values after any delimiter parameters.
Example 1:
Mysql> Select Concat_ws (' # ', ' dbdh= ', ' Northeasttrcoon ', null) as Dbdh_name_three;
+-----------------------+
| Dbdh_name_three |
+-----------------------+
| Dbdh= #NorthEastTrcoon |
+-----------------------+
1 row in Set (0.00 sec)
Example 2:
mysql> Select CONCAT_WS (null, ' dbdh= ', ' Northeasttrcoon ', null) as Dbdh_name_fourth;
+------------------+
| Dbdh_name_fourth |
+------------------+
| NULL |
+------------------+
1 row in Set (0.00 sec)
Example 3:
Mysql> Select Concat_ws (' * ', ' dbdh= ', ' Northeasttrcoon ', null) as dbdh_name_fifth;
+-----------------------+
| dbdh_name_fifth |
+-----------------------+
| Dbdh=*northeasttrcoon |
+-----------------------+
1 row in Set (0.00 sec)
3. group_concat function in MySQL
The complete syntax is as follows:
Group_concat ([DISTINCT] field to connect [order by Asc/desc sort field] [Separator ' delimiter '])
Basic Query
Mysql> select * from STU1;
+------+------+
| id| name |
+------+------+
| 10|
| 20|
| 20|
| 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in Set (0.00 sec)

Group by ID, print the value of the Name field on one line, comma separated (default)

Mysql> Select Id,group_concat (name) from the AA group by ID;
+------+--------------------+
| id| Group_concat (name) |
+------+--------------------+
| 10,20,20|
| 20 |
|3 | 200,500|
+------+--------------------+
3 Rows in Set (0.00 sec)

Group by ID, print the value of the Name field on one line, semicolon delimited

Mysql> Select ID,GROUP_CONCAT (name separator '; ') from the AA group by ID;
+------+----------------------------------+
| id| Group_concat (name separator '; ') |
+------+----------------------------------+
| 10;20;20 |
| 20|
|3 | 200;500 |
+------+----------------------------------+
3 Rows in Set (0.00 sec)

Group by ID, print the value of the redundant Name field to a line,
Comma delimited
Mysql> Select Id,group_concat (distinct name) from the AA group by ID;
+------+-----------------------------+
| id| Group_concat (distinct name) |
+------+-----------------------------+
| 10,20|
| 20 |
|3 | 200,500 |
+------+-----------------------------+
3 Rows in Set (0.00 sec)
Group by ID, print the value of the Name field in one line, comma separated, and reverse by name
Mysql> Select Id,group_concat (name order BY name Desc) from the AA group by ID;
+------+---------------------------------------+
| id| Group_concat (name order BY name Desc) |
+------+---------------------------------------+
| 20,20,10 |
| 20|
|3 | 500,200|
+------+---------------------------------------+
3 Rows in Set (0.00 sec)
There is also a simple way to connect: | |
Mysql> select * from student;
+----+------+-------+----------+------------+
| ID | Age | Score | name | Birth |
+----+------+-------+----------+------------+
|   1 |    23 | 78 | John Doe | 2017-10-10 |
|   2 |    24 | 78 | Zhangsan | 2017-10-10 |
|   3 |    25 | 99 | Harry | 2016-05-17 |
+----+------+-------+----------+------------+
3 Rows in Set (0.00 sec)
Mysql> Select id+999,name,name+99,name+ ' 999 ' from student;
+--------+----------+---------+------------+
| id+999 | name | name+99 | name+ ' 999 ' |
+--------+----------+---------+------------+
| 1000 |      John Doe |        99 | 999 |
| 1001 |      Zhangsan |        99 | 999 |
| 1002 |      Harry |        99 | 999 |
+--------+----------+---------+------------+
3 Rows in set, 6 warnings (0.00 sec)

MySQL String Stitching Detailed