Usage of concat and Group_concat () in MySQL

One, CONCAT () function
The CONCAT () function is used to concatenate multiple strings into a single string.
Use data table info as an example, where select Id,name from Info LIMIT 1, and the returned result is
+----+--------+
| ID | name |
+----+--------+
| 1 | BioCyc |
+----+--------+
1, grammar and use characteristics:
CONCAT (STR1,STR2,...)
Returns the string that results from the connection parameter. If any one of the arguments is NULL, the return value is null. can have one or more parameters.

2. Examples of use:
SELECT CONCAT (ID, ', ', name) as con from Info LIMIT 1; return result is
+----------+
| Con |
+----------+
| 1,BIOCYC |
+----------+

SELECT CONCAT (' My ', NULL, ' QL '); return result is
+--------------------------+
| CONCAT (' My ', NULL, ' QL ') |
+--------------------------+
| NULL |
+--------------------------+

3. How to specify separators between parameters
Use the function Concat_ws (). Use syntax for: CONCAT_WS (separator,str1,str2,...)
Concat_ws () represents CONCAT with Separator, which is a special form of the CONCAT (). The first parameter is the delimiter for the other parameter. 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. However, CONCAT_WS () does not ignore any empty strings. (All NULL is ignored, however).

such as Select Concat_ws (' _ ', Id,name) as Con_ws from info LIMIT 1; return result is
+----------+
| Con_ws |
+----------+
| 1_BIOCYC |
+----------+

SELECT concat_ws (', ', ' First name ', NULL, ' last Name '), and the returned result is
+----------------------------------------------+
| Concat_ws (', ', ' First name ', NULL, ' last Name ') |
+----------------------------------------------+
| First Name,last name |
+----------------------------------------------+

Second, Group_concat () function
The Group_concat function returns a string result that is composed of concatenated values in the grouping.
Using the table info as an example, where the statement select Locus,id,journal from info where locus in (' AB086827 ', ' AF040764 '), the returned result is
+----------+----+--------------------------+
| Locus | ID | Journal |
+----------+----+--------------------------+
|  AB086827 | 1 | Unpublished |
|  AB086827 | 2 | Submitted (20-jun-2002) |
| AF040764 | 23 | Unpublished |
| AF040764 | 24 | Submitted (31-dec-1997) |
+----------+----+--------------------------+

1, the use of grammar and characteristics:
Group_concat ([DISTINCT] expr [, expr ...]
[ORDER by {Unsigned_integer | col_name | formula} [ASC | DESC] [col ...]
[SEPARATOR Str_val])
In MySQL, you can get the link value of the expression binding. You can exclude duplicate values by using DISTINCT. If you want to sort the values in the results, you can use the ORDER BY clause.
SEPARATOR is a string value that is used to insert into the result value. The default is a comma (","), which can be completely removed by specifying SEPARATOR "".
You can set a maximum length by using the variable Group_concat_max_len. The syntax to execute at run time is as follows: SET [SESSION | GLOBAL] Group_concat_max_len = Unsigned_integer;
If the maximum length is set, the resulting value is clipped to this maximum length. If the grouped characters are too long, the system parameters can be set: SET @ @global. group_concat_max_len=40000;

2. Examples of use:
Statement SELECT Locus,group_concat (ID) from Info WHERE locus in (' AB086827 ', ' AF040764 ') GROUP by locus; The return result is
+----------+------------------+
| Locus | Group_concat (ID) |
+----------+------------------+
| AB086827 | |
| AF040764 | 23,24 |
+----------+------------------+

Statement SELECT LOCUS,GROUP_CONCAT (DISTINCT ID ORDER by ID DESC SEPARATOR ' _ ') from Info WHERE locus in (' AB086827 ', ' AF040764 ') G Roup by locus; The return result is
+----------+----------------------------------------------------------+
| Locus | GROUP_CONCAT (DISTINCT ID ORDER by ID DESC SEPARATOR ' _ ') |
+----------+----------------------------------------------------------+
| AB086827 | 2_1 |
| AF040764 | 24_23 |
+----------+----------------------------------------------------------+

Statement Select Locus,group_concat (Concat_ws (', ', id,journal) ORDER by id DESC SEPARATOR '. ') from Info WHERE locus in (' AB086827 ', ' AF040764 ') GROUP by locus; The returned result is
+----------+--------------------------------------------------------------------------+
| Locus | Group_concat (Concat_ws (', ', id,journal) ORDER by id DESC SEPARATOR '. ') |
+----------+--------------------------------------------------------------------------+
| AB086827 | 2, submitted (20-jun-2002). 1, Unpublished |
| AF040764 | Submitted (31-dec-1997). Unpublished |
+----------+--------------------------------------------------------------------------+

Usage of concat and Group_concat () in MySQL