mysql GROUP_CONCAT函数详解
mysql
GROUP_CONCAT
函数详解
概要
`
group_concat
函数是
mysql
中非常实用的函数,它可以将同一个分组下的行拼接在一起。其完整语法:
GROUP_CONCAT([DISTINCT] 要连接的字段 [OrderBYASC/DESC 排序字段][Separator ‘分隔符’])
使用技巧
1. 建表、插入数据
#建表语句CREATETABLE`test_group_concat`(`id`int(11)NOTNULLCOMMENT'id',`age`int(11)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
#插入测试数据INSERTINTO`test`.`test_group_concat`(`id`,`age`)VALUES(1,20);INSERTINTO`test`.`test_group_concat`(`id`,`age`)VALUES(1,20);INSERTINTO`test`.`test_group_concat`(`id`,`age`)VALUES(1,10);INSERTINTO`test`.`test_group_concat`(`id`,`age`)VALUES(3,30);INSERTINTO`test`.`test_group_concat`(`id`,`age`)VALUES(3,40);INSERTINTO`test`.`test_group_concat`(`id`,`age`)VALUES(3,40);INSERTINTO`test`.`test_group_concat`(`id`,`age`)VALUES(4,50);INSERTINTO`test`.`test_group_concat`(`id`,`age`)VALUES(4,60);
#基本查询SELECT*FROM test_group_concat
2.以
id
分组,把
age
字段的值拼成一行,逗号分隔(默认)
select id,group_concat(age)from test_group_concat groupby id;
3.以
id
分组,把
age
字段的值拼成 一行,分号分隔
select id,group_concat(age separator ';')from test_group_concat groupby id;
4.以
id
分组,把去冗余的
age
字段的值打印在一行
select id,group_concat(distinct age)from test_group_concat groupby id;
5.以
id
分组,把
age
字段的值打印在一行,逗号分隔,以
age
排倒序
select id,group_concat(age orderby age desc)from test_group_concat groupby id;
发现问题
在项目用到如下sql
SELECT
GROUP_CONCAT( c.goods_detail_id )FROM
air_out_order a
LEFTJOIN air_out_order_detail b ON b.order_id = a.id
LEFTJOIN 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
LEFTJOIN air_out_order_detail b ON b.order_id = a.id
LEFTJOIN air_out_good_record c ON c.order_detail_id = b.id;
的查询结果为:
713278402284617792722329309878140931,722329309878140949,722329309878140973,722329309878140939715342356376936480715342356376936482715342356381130806715342356376936484715342356381130810713278240841662516713278240841662476713278240841662520715342356381130876,715342356381130940,715342356385325066,715342356385325132,715342356385325196,715342356385325260,715342356376936526,715342356381130812,715342356381130890,715342356381130964744788201669005316,744788201669005380,744788201673199622,744788201669005330,744788201669005394,744788201752891404,744788201664811012,744788201669005344,744788201669005408,744788201752891418744788201669005338,744788201669005410,744788201664811016,744788201669005354,744788201669005424,744788201664811030,744788201669005368,744788201673199616,744788201752891400,744788201669005312745061736895352916,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,745061736891158542744788201669005318,744788201669005396,744788201669005334,744788201669005414,744788201669005352,744788201669005430,744788201664811022,744788201669005370,744788201752891394,744788201664811038744788201664811020,744788201669005382,744788201752891416,744788201664811040,744788201669005400,744788201669005328,744788201669005420,744788201669005350,744788201673199618,744788201664811008746171271546650637746171271550844964745061736895352842,745061736895352934,745061736895352862,745061736895352954,745061736891158548,745061736895352882,745061736891158568,745061736895352902,745061736895352832,745061736895352924747357984740646912,747357984912613376,747357984782589952,747357984824532992,747357984652566528747357984778395648747357984753229824745898989121159210,745898989108576268,745898989125353485,745898989116964869,745898989121159170,745898989121159184745898989112770564,745898989121159168,745898989121159186,745898989121159202,745898989108576272,745898989125353481745898989108576274,745898989116964877,745898989121159182,745898989121159206,745898989108576258,745898989116964865745898989116964879745898989112770562745898989116964871,745898989121159180,745898989121159208,745898989108576260,745898989121159172,745898989121159194745898989121159188,745898989125353477,745898989116964875,745898989121159198,745898989108576264,745898989121159178747357984694509568,747357984904224768,747357984749035520,747357984807755776,747357984849698816,747357984644177920,747357984883253248,747357984715481088,747357984770007040,747357984828727296747357984707092480,747357984795172864,747357984853893120,747357984895836160,747357984665149440,747357984736452608,747357984820338688,747357984866476032,747357984509960192,747357984916807680745898989121159196745898989121159200762832940033040386715342356385325082
要多得多,出现
group_concat
把数据给截断了,什么原因呢
解决问题
由于
group_concat
有长度限制,默认
1024
个字符,
所以解决的办法也就是将
group_concat
有长度限制设置足够大即可。
1)临时解决方案:
在命令行运行
SETSESSION group_concat_max_len =18446744073709551615;setglobal max_allowed_packet =2*1024*1024*10;
2)永久解决方案:
首先找到
mysql
的配置文件,设置如下配置
max_allowed_packet =20M
group_concat_max_len =18446744073709551615
消码哥
mysql GROUP_CONCAT函数详解
版权归原作者 坦笑&&life 所有, 如有侵权,请联系我们删除。