mysql GROUP_CONCAT函数详解

悠悠 2024-03-16 14:40 30阅读 0赞

#### 文章目录 ####

*   *  概要
     *  使用技巧
     *   *  1. 建表、插入数据
         *  2.以id分组，把age字段的值拼成一行，逗号分隔(默认)
         *  3.以id分组，把age字段的值拼成 一行，分号分隔
         *  4.以id分组，把去冗余的age字段的值打印在一行
         *  5.以id分组，把age字段的值打印在一行，逗号分隔，以age排倒序
     *  发现问题
     *  解决问题

### 概要 ###

\` group\_concat函数是mysql中非常实用的函数，它可以将同一个分组下的行拼接在一起。其完整语法：  
GROUP\_CONCAT(\[DISTINCT\] 要连接的字段 \[Order BY ASC/DESC 排序字段\] \[Separator ‘分隔符’\])

### 使用技巧 ###

#### 1. 建表、插入数据 ####

#建表语句
    CREATE TABLE `test_group_concat` (
      `id` int(11) NOT NULL COMMENT 'id',
      `age` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    #插入测试数据
    INSERT INTO `test`.`test_group_concat` (`id`, `age`) VALUES (1, 20);
    INSERT INTO `test`.`test_group_concat` (`id`, `age`) VALUES (1, 20);
    INSERT INTO `test`.`test_group_concat` (`id`, `age`) VALUES (1, 10);
    INSERT INTO `test`.`test_group_concat` (`id`, `age`) VALUES (3, 30);
    INSERT INTO `test`.`test_group_concat` (`id`, `age`) VALUES (3, 40);
    INSERT INTO `test`.`test_group_concat` (`id`, `age`) VALUES (3, 40);
    INSERT INTO `test`.`test_group_concat` (`id`, `age`) VALUES (4, 50);
    INSERT INTO `test`.`test_group_concat` (`id`, `age`) VALUES (4, 60);
    
    
    #基本查询
    SELECT * FROM test_group_concat

![在这里插入图片描述][9a658ea973c045e7887886b1231f0e13.png]

#### 2.以id分组，把age字段的值拼成一行，逗号分隔(默认) ####

select id,group_concat(age) from test_group_concat group by id;

![在这里插入图片描述][d9a0abdb8fc84d1087d38ef945c14994.png]

#### 3.以id分组，把age字段的值拼成 一行，分号分隔 ####

select id,group_concat(age separator ';') from test_group_concat group by id;

![在这里插入图片描述][b3ac44c8637c4828b19236f874d2fb68.png]

#### 4.以id分组，把去冗余的age字段的值打印在一行 ####

select id,group_concat(distinct age) from test_group_concat group by id;

![在这里插入图片描述][6ea0349fbdd249e8979d31428a0b1006.png]

#### 5.以id分组，把age字段的值打印在一行，逗号分隔，以age排倒序 ####

select id,group_concat(age order by age desc) from test_group_concat group by id;

![在这里插入图片描述][d321f17e87cb409d85fbddcc3e11d4c4.png]

### 发现问题 ###

在项目用到如下sql

SELECT
    	GROUP_CONCAT( c.goods_detail_id ) 
    FROM
    	air_out_order a
    	LEFT JOIN air_out_order_detail b ON b.order_id = a.id
    	LEFT JOIN air_out_good_record c ON c.order_detail_id = b.id;

查询结果：

713278402284617792,722329309878140931,722329309878140949,722329309878140973,722329309878140939,715342356376936480,715342356376936482,715342356381130806,715342356376936484,715342356381130810,713278240841662516,713278240841662476,713278240841662520,715342356381130876,715342356381130940,715342356385325066,715342356385325132,715342356385325196,715342356385325260,715342356376936526,715342356381130812,715342356381130890,715342356381130964,744788201669005316,744788201669005380,744788201673199622,744788201669005330,744788201669005394,744788201752891404,744788201664811012,744788201669005344,744788201669005408,744788201752891418,744788201669005338,744788201669005410,744788201664811016,744788201669005354,744788201669005424,744788201664811030,744788201669005368,744788201673199616,744788201752891400,744788201669005312,745061736895352916,745061736891158544,745061736895352852,745061736895352930,745061736891158558,745061736895352866,745061736895352944,745061736891158572,745061736895352880,745061736895352958,74506173689115858

而实际上

SELECT
    c.goods_detail_id
    FROM
    	air_out_order a
    	LEFT JOIN air_out_order_detail b ON b.order_id = a.id
    	LEFT JOIN air_out_good_record c ON c.order_detail_id = b.id;

的查询结果为：

713278402284617792
    722329309878140931,722329309878140949,722329309878140973,722329309878140939
    715342356376936480
    715342356376936482
    715342356381130806
    715342356376936484
    715342356381130810
    713278240841662516
    713278240841662476
    713278240841662520
    715342356381130876,715342356381130940,715342356385325066,715342356385325132,715342356385325196,715342356385325260,715342356376936526,715342356381130812,715342356381130890,715342356381130964
    744788201669005316,744788201669005380,744788201673199622,744788201669005330,744788201669005394,744788201752891404,744788201664811012,744788201669005344,744788201669005408,744788201752891418
    744788201669005338,744788201669005410,744788201664811016,744788201669005354,744788201669005424,744788201664811030,744788201669005368,744788201673199616,744788201752891400,744788201669005312
    745061736895352916,745061736891158544,745061736895352852,745061736895352930,745061736891158558,745061736895352866,745061736895352944,745061736891158572,745061736895352880,745061736895352958,745061736891158586,745061736895352894,745061736891158536,745061736895352844,745061736895352908,745061736895352922,745061736891158550,745061736895352858,745061736895352936,745061736891158564,745061736895352872,745061736895352950,745061736891158578,745061736895352886,745061736895352964,745061736891158528,745061736895352836,745061736895352900,745061736895352914,745061736891158542
    744788201669005318,744788201669005396,744788201669005334,744788201669005414,744788201669005352,744788201669005430,744788201664811022,744788201669005370,744788201752891394,744788201664811038
    744788201664811020,744788201669005382,744788201752891416,744788201664811040,744788201669005400,744788201669005328,744788201669005420,744788201669005350,744788201673199618,744788201664811008
    746171271546650637
    746171271550844964
    745061736895352842,745061736895352934,745061736895352862,745061736895352954,745061736891158548,745061736895352882,745061736891158568,745061736895352902,745061736895352832,745061736895352924
    747357984740646912,747357984912613376,747357984782589952,747357984824532992,747357984652566528
    747357984778395648
    747357984753229824
    745898989121159210,745898989108576268,745898989125353485,745898989116964869,745898989121159170,745898989121159184
    745898989112770564,745898989121159168,745898989121159186,745898989121159202,745898989108576272,745898989125353481
    745898989108576274,745898989116964877,745898989121159182,745898989121159206,745898989108576258,745898989116964865
    745898989116964879
    745898989112770562
    745898989116964871,745898989121159180,745898989121159208,745898989108576260,745898989121159172,745898989121159194
    745898989121159188,745898989125353477,745898989116964875,745898989121159198,745898989108576264,745898989121159178
    747357984694509568,747357984904224768,747357984749035520,747357984807755776,747357984849698816,747357984644177920,747357984883253248,747357984715481088,747357984770007040,747357984828727296
    747357984707092480,747357984795172864,747357984853893120,747357984895836160,747357984665149440,747357984736452608,747357984820338688,747357984866476032,747357984509960192,747357984916807680
    745898989121159196
    745898989121159200
    762832940033040386
    715342356385325082

要多得多，出现group\_concat把数据给截断了，什么原因呢？

### 解决问题 ###

由于group\_concat有长度限制,默认1024个字符，  
所以解决的办法也就是将group\_concat有长度限制设置足够大即可。  
1）临时解决方案：  
在命令行运行

SET SESSION group_concat_max_len = 18446744073709551615;
    set global max_allowed_packet = 2*1024*1024*10;

2）永久解决方案：  
首先找到mysql的配置文件，设置如下配置

max_allowed_packet = 20M
    group_concat_max_len = 18446744073709551615

[9a658ea973c045e7887886b1231f0e13.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/4401dd431d10452aacadb754d1469302.png
[d9a0abdb8fc84d1087d38ef945c14994.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/42b7cc683f5146acb40c607010f3180d.png
[b3ac44c8637c4828b19236f874d2fb68.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/1ffb01386eb548618c634f024d73b0b0.png
[6ea0349fbdd249e8979d31428a0b1006.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/1b780235f7e748b3bd5aaf0e1bf86cbd.png
[d321f17e87cb409d85fbddcc3e11d4c4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/4e2f734e050b4715be2499a48ade3c41.png