Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use the group_concat function in MYSQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

one。 Test data preparation

> use test

Database changed

Mysql > select * from t_kenyon

+-+

| | id |

+-+

| | 1 |

| | 123 |

| | 789 |

| | 345 |

| | 78 |

+-+

5 rows in set (0.00 sec)

two。 Use the www.2cto.com

1. Use the default comma as the delimiter

Mysql > select group_concat (id) from t_kenyon

+-+

| | group_concat (id) |

+-+

| | 1pr 123 pr 789 pr 345 pr 78 | |

+-+

1 row in set (0.00 sec)

two。 Row-to-column after sorting ID value

Mysql > select group_concat (id order by id) from t_kenyon

+-+

| | group_concat (id order by id) |

+-+

| | 1pr 78123345789 | |

+-+

1 row in set (0.00 sec)

3. Use other separators, such as * and;, etc.

Mysql > select group_concat (id separator'*') from t_kenyon

+-+

| | group_concat (id separator'*') |

+-+

| | 1 "123" 789 "345" 78 |

+-+

1 row in set (0.00 sec) www.2cto.com

4. Delimiters are used in combination with sorting

Mysql > select group_concat (id order by id separator'_') from t_kenyon

+-- +

| | group_concat (id order by id separator'_') |

+-- +

| | 1 / 78 / 123 / 345 / 789 | |

+-- +

1 row in set (0.00 sec)

5. Group the same values

Mysql > insert into t_kenyon values (78)

Query OK, 1 row affected (0.00 sec)

Mysql > select group_concat (id) from t_kenyon group by id

+-+

| | group_concat (id) |

+-+

| | 1 |

| | 78, 78 |

| | 123 |

| | 345 |

| | 789 |

+-+

5 rows in set (0.00 sec)

three。 Description of parameter settings and restrictions

1. View the www.2cto.com settings in the server

Mysql > show variables like'% group_concat%'

+-+ +

| | Variable_name | Value |

+-+ +

| | group_concat_max_len | 1024 | |

+-+ +

1 row in set (0.00 sec)

The values set above indicate that the default length 1KB is currently

two。 Change the parameter value

Method 1: modify the parameters in the configuration file and add group_concat_max_len = 10240

Method 2: implement in the session, globally or in the current session

SET GLOBAL group_concat_max_len=10240

SET SESSION group_concat_max_len=10240

This is how the group_concat function in MYSQL is used, and if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.