Instructions on the use of mysql functions concat and group_concat

1.concat() function

Usage method: concat(str1,str2,…)
The result returned is the string generated by the connection parameter. If there is any If the parameter is null, the return value is null

Note:
If all parameters are non-binary strings, the result is a non-binary string
If the independent variable Contains any binary string, the result is a binary string
A numeric parameter is converted into an equivalent binary string format. To avoid this, use an explicit type cast

For example:

select concat(cast(int_col as char), char_col);

Usage example:
1. Add ',' at both ends of the field

mysql> select concat(',',name,',') from `user`;
+--------------------------+| concat(',',fdipzone,',') |
+--------------------------+| ,fdipzone,               |
+--------------------------+1 row in set (0.00 sec)

2. One of the parameters is null

mysql> select concat(null,name) from `user`;
+-------------------+| concat(null,name) |
+-------------------+| NULL              |
+-------------------+1 row in set (0.00 sec)

2.concat_ws() function

Usage method:concat_ws(separator,str1,str2,…)

The concat_ws() function is a special form of the concat() function. The first parameter is the separator for other parameters. The position of the delimiter is between the two strings to be concatenated. The delimiter can be a string or other parameters.
If the delimiter is null, the result is null.
The function will ignore any null value after the delimiter parameter, but concat_ws() will not ignore any empty string.

Usage examples:
1. Use ',' to separate multiple fields

mysql> select concat_ws(',',country_code,phone,region) from `user`;
+------------------------------------------+| concat_ws(',',country_code,phone,region) |
+------------------------------------------+| 86,13794830550,GZ                        |
+------------------------------------------+1 row in set (0.00 sec)

2. Separate The symbol is null

mysql> select concat_ws(null,country_code,phone,region) from `user`;
+-------------------------------------------+| concat_ws(null,country_code,phone,region) |
+-------------------------------------------+| NULL                                      |
+-------------------------------------------+1 row in set (0.00 sec)

3. There are null and empty strings in the parameters

mysql> select concat_ws(',',country_code,phone,null,region,'',grade) from `user`;
+--------------------------------------------------------+| concat_ws(',',country_code,phone,null,region,'',grade) |
+--------------------------------------------------------+| 86,13794830550,GZ,,200                                 |
+--------------------------------------------------------+1 row in set (0.00 sec)

3.group_concat() function

Usage: GROUP_CONCAT([DISTINCT] expr [,expr …]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col …]]
[SEPARATOR str_val])

group_concat can get the connection value of the expression combination, and use distinct to exclude duplicate values. Sorting can be done using the order by clause.
separator is a string used to separate each element in the result set. The default is comma, this separator can be completely removed by specifying separator "".

Usage example:

Table structure

CREATE TABLE `article_in_category` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `article_id` int(11) unsigned NOT NULL, `category_id` int(11) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `article_id_INDEX` (`article_id`), KEY `category_id_INDEX` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert data:

INSERT INTO `article_in_category` (`id`, `article_id`, `category_id`) VALUES (NULL, '1', '1'),
(NULL, '1', '2'),(NULL, '1', '3'),(NULL, '2', '4'),(NULL, '2', '3'),(NULL, '2', '5'),(NULL, '3', '1'),
(NULL, '3', '5'),(NULL, '3', '6'),(NULL, '4', '8');

mysql> select * from `article_in_category`;
+----+------------+-------------+| id | article_id | category_id |
+----+------------+-------------+|  1 |          1 |           1 |
|  2 |          1 |           2 |
|  3 |          1 |           3 |
|  4 |          2 |           4 |
|  5 |          2 |           3 |
|  6 |          2 |           5 |
|  7 |          3 |           1 |
|  8 |          3 |           5 |
|  9 |          3 |           6 || 10 |          4 |           8 |
+----+------------+-------------+

Get the id of the article and all category ids

mysql> select article_id,group_concat(category_id order by category_id asc) from `article_in_category` group by article_id;
+------------+----------------------------------------------------+| article_id | group_concat(category_id order by category_id asc) |
+------------+----------------------------------------------------+|          1 | 1,2,3                                              |
|          2 | 3,4,5                                              |
|          3 | 1,5,6                                              ||          4 | 8                                                  |
+------------+----------------------------------------------------+4 rows in set (0.00 sec)

Note: The group_concat() function has a length limit on the returned result, the default is 1024 bytes

View the maximum return value of group_concat Length

mysql> show global variables like '%group_concat_max_len%';
+----------------------+-------+| Variable_name        | Value |
+----------------------+-------+| group_concat_max_len | 1024  |
+----------------------+-------+

Modify the maximum length of group_concat return value

mysql> set global group_concat_max_len=2048;
Query OK, 0 rows affected (0.03 sec)mysql> show global variables like '%group_concat_max_len%';
+----------------------+-------+| Variable_name        | Value |
+----------------------+-------+| group_concat_max_len | 2048  |
+----------------------+-------+

This article explains the usage instructions of mysql function concat and group_concat. For more related content, please pay attention to php Chinese website .
Related recommendations:

Explanation on how to handle mysql innodb startup failure and cannot be restarted

Explanation of related content of PHP obtaining the specified date

Detailed explanation of PHP generating unique RequestID class

The above is the detailed content of Instructions on the use of mysql functions concat and group_concat. For more information, please follow other related articles on the PHP Chinese website!