Shulou(Shulou.com)06/01 Report--

This article mainly introduces the process of using concat and group_concat functions, which involves things, learned from the theoretical knowledge, there are many books, literature for your reference, from the perspective of practical significance, accumulated years of practical experience can be shared with you.

1.concat () function

Usage: concat (str1,str2, …)

The returned result is the string generated by the connection parameter. If any 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 argument contains any binary string, the result is a binary string

A numeric parameter is converted to its equivalent binary string format, which can be avoided by using the explicit type cast

For example:

Select concat (cast (int_col as char), char_col)

Examples of use:

1. Add','at both ends of the field

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

two。 One of the parameters is null

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

The concat_ws () function is a special form of the concat () function, and the first argument is the delimiter of the 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 ignores the null value after any delimiter arguments, but concat_ws () does not ignore any empty strings.

Examples of use:

1. Use','to separate multiple fields

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

two。 The separator 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 string in the parameter

Mysql > select concat_ws (',', country_code,phone,null,region,'',grade) from `user` +-- + | concat_ws (',', country_code,phone,null,region,'' Grade) | +-- + | 86GZ 13794830550Magi 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 join value of the combination of expressions, and distinct can be used to exclude duplicate values. You can sort using the order by clause.

Separator is a string that separates each element in the result set. The default is a comma, and you can completely remove this delimiter by specifying separator ".

Examples of use:

Table structure

CREATE TABLE `article_in_ indey` (`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_ roomy` (`id`, `article_ id`, `category_ id`) VALUES (NULL, '1levels,' 1'), (NULL, '1levels,' 2'), (NULL, '1levels,' 3'), (NULL, '2levels,' 4'), (NULL, '2levels,' 3'), (NULL, '2levels,' 5'), (NULL, '3miles,' 1'), (NULL, '3bands,' 5') (NULL, '3percent,' 6'), (NULL, '4percent,' 8') Mysql > select * from `article_in_ roomy` +-+ | id | article_id | category_id | +-+ | 1 | 1 | 2 | 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 the classified id

Mysql > select article_id,group_concat (category_id order by category_id asc) from `article_in_ roomy` group by article_id +-+-+ | article_id | group_concat (category_id order by category_id asc) | +-+- -- + | 1 | 1pr 2 3 | | 2 | 3Person4Magol 5 | | 3 | 1Phen5 6 | | 4 | 8 | +-+-- + 4 rows in set (0.00 sec)

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

Check the maximum length of the returned value of group_concat

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

These are the details of the instructions for the use of mysql functions concat and group_concat. Please pay more attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.