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

This article introduces you how to use group_concat juxtaposition in MySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

test

Create a test table to test

Create table test_classes (std_name varchar, class_name varchar); insert into test_classes (std_name, class_name) value (Xiaoming, Class 2), (Xiao Hong, Class 2), (Xiao Gang, Class 2), (Xiao Qiang, Class 1), (Xiaobai, Class 3), (Xiao Yu, Class 3)

Requirements: find out who is in each of the three classes and display them using a string field.

At this point, it can be done directly by using a single statement of group_concat.

Select class_name, group_concat (std_name) As std_names from test_classes group by class_name

It needs to be displayed by class, so you need to use group_by to group.

+-+-+ | class_name | std_names | +-+-+ | Class 1 | Xiaoqiang | | Class 3 | Rookie Xiao Yu | | Class 2 | Xiao Ming, Xiao Hong, Xiao Gang | +-+-+

Sorting: sorting is also supported in group_concat.

Select class_name, group_concat (std_name order by std_name) As std_names from test_classes group by class_name

+-+-+ | class_name | std_names | +-+-+ | Class 1 | Xiaoqiang | | Class 3 | Rookie Xiaoyu | | Class two | Xiaogang, Xiaoming, Xiaohong | +-+-+

De-weight: you can use DISTINCT to remove weight.

Insert into test_classes (std_name, class_name) value ("Xiaoming", "Class two")

Insert into test_classes (std_name, class_name) value ("Xiaoming", "Class two")

Select class_name, group_concat (DISTINCT (std_name)) As std_names from test_classes group by class_name

+-+-+ | class_name | std_names | +-+-+ | Class 1 | Xiaoqiang | | Class 3 | Rookie Xiaoyu | | Class two | Xiaogang, Xiaoming, Xiaohong | +-+-+

Merge multiple columns: you can combine multiple columns into a single string with CONCAT_WS.

Select group_concat (CONCAT_WS (', class_name,std_name) SEPARATOR';') As std_names from test_classes SEPARATOR is the specified delimiter ```sql +- -+ | std_names | +-- -+ | Xiaoming of Class two Xiao Hong of Class 2; Xiaogang of Class 2; Xiao Qiang of Class 1; Xiaobai of Class 3; Xiao Yu of Class 3; Xiaoming of Class 2 Xiaoming of Class two | +- -- + length limit

The function GROUP_CONCAT has a maximum length limit. If this length limit is exceeded, the resulting string is the result of being intercepted and will not report an error, so you need to pay attention.

This length is controlled by the variable group_concat_max_len.

Show variables like "group_concat_max_len"

You can enlarge the limit by modifying this variable.

SET GLOBAL group_concat_max_len=102400; SET SESSION group_concat_max_len=102400

The configuration file needs to be configured in the quota section of mysqld

[mysqld] group_concat_max_len=102400 shares here on how to use group_concat juxtaposition in MySQL. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

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.