Instructions on the use of mysql functions concat and group

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);

Copy after login

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)

Copy after login

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)

Copy after login

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)

Copy after login

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)

Copy after login

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)

Copy after login

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;

Copy after login

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');

Copy after login

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 | +----+------------+-------------+

Copy after login

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)

Copy after login

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 | +----------------------+-------+

Copy after login

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 | +----------------------+-------+

Copy after login

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!