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)
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)
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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
