MySQL String connection function

Yi, 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.

Select Concat (s_id, "--", s_bar_code) from ' T_storage_order_detail ' WHERE ' s_sn ' like '%r2016091200002% ' LIMIT 0, 1000;

+--------------------------------+
| Concat (s_id, "--", S_bar_code) |
+--------------------------------+
| 204--6930000003111 |
| 205--6930000003128 |
| 206--6930000003135 |
| 207--6930000003142 |
| 208--6930000003159 |
| 209--6930000003166 |
| 210--6930000003173 |
| 211--6930000003180 |
| 212--6930000003197 |
| 213--6930000003203 |
| 214--6930000003210 |
| 215--6930000003227 |
+--------------------------------+

Second,MySQL in the CONCAT_WS function

How to use:
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.

Mysql> Select Concat_ws ("--", s_id,s_bar_code) from ' T_storage_order_detail ' WHERE ' s_sn ' like '%r2016091200002% ' LIMIT 0, 1000;
+---------------------------------+
| Concat_ws ("---", S_id,s_bar_code) |
+---------------------------------+
| 204--6930000003111 |
| 205--6930000003128 |
| 206--6930000003135 |
| 207--6930000003142 |
| 208--6930000003159 |
| 209--6930000003166 |
| 210--6930000003173 |
| 211--6930000003180 |
| 212--6930000003197 |
| 213--6930000003203 |
+---------------------------------+
Rows in Set

Third,MySQL in the Group_concat function
The complete syntax is as follows:
Group_concat ([DISTINCT] field to connect [order by Asc/desc sort field] [Separator ' delimiter '])

Mysql> Select Group_concat ("'", S_bar_code, "'") from ' t_storage_order_detail ' WHERE ' s_sn ' like '%r2016091200002% ' LIMIT 0, 1000;
+-------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------+
| Group_concat ("'", S_bar_code, "'") |
+-------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------+
| ' 6930000003111 ', ' 6930000003128 ', ' 6930000003135 ', ' 6930000003142 ', ' 6930000003159 ', ' 6930000003166 ', ' 6930000003173 ', ' 6930000003180 ', ' 6930000003197 ', ' 6930000003203 ', ' 6930000003210 ', ' 6930000003227 ', ' 6930000003234 ', ' 6930000003241 ', ' 6930000003258 ', ' 6930000003265 ', ' 6930000003272 ', ' 6930000003289 ', ' 6930000003296 ', ' 6930000003302 ' , ' 6930000003319 ', ' 6930000003326 ', ' 6930000003333 ', ' 6930000003340 ', ' 6930000003357 ', ' 6930000003364 ', ' 6930000003371 ', ' 6930000003388 ' |
+-------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------+
1 row in Set

Basic Query

Mysql> select * from AA;
+------+------+
| 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)

Connection traps for 1.int fields when you use Group_concat, be aware that if you are connecting a field with an int type, be sure to convert it to char and spell it again, otherwise after you execute (executescalar or any other method that executes SQL to return the result) The returned will not be a comma-separated string, but byte[]. This problem is difficult to find when you are in some tools such as SQLyog.

Select Group_concat (IPAddress) from T_IP returns a comma-separated string

Select Group_concat (ID) from T_IP return byte[]

Select Group_concat (CAST (id as char)) from T_DEP returns a comma-separated string

Select Group_concat (Convert (ID, char)) from T_DEP returns a comma-separated string

The use of the attached Cast,convert:

CAST (expr as type), convert (Expr,type), convert (expr USING transcoding_name)

The CAST () and CONVERT () functions can be used to get a value of one type and produce a value of another type. This type can be one of the following values: binary[(n)] char[(n)] DATE DATETIME DECIMAL signed [integer] Time UNSIGNED [integer]

2. Length traps when connecting fields with Group_concat, there is a length limit, not how many are connected. But you can set it up a bit.

Using the Group_concat_max_len system variable, you can set the maximum allowable length.

The syntax for doing this in a program is as follows, where Val is an unsigned integer: SET [SESSION | GLOBAL] Group_concat_max_len = val;

If the maximum length has been set, the result is up to this maximum length. After performing SET GLOBAL Group_concat_max_len = 10 in SQLyog,

When you reopen SQLyog, the settings will take effect.

MySQL String connection function