mysql使用group_concat()连接构造json对象数组时长度受限

最新推荐文章于 2025-02-17 13:21:35 发布

YoungDream365

最新推荐文章于 2025-02-17 13:21:35 发布

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_36299991/article/details/118103783

数据库专栏收录该内容

5 篇文章

订阅专栏

使用MySQL的json_object()和group_concat()函数将tb_stu_class_rel表中的数据转换为JSON数组，通过groupby进行分组。遇到的问题是group_concat默认长度限制为1024，导致长数据被截断，解决方案是修改group_concat_max_len变量。通过设置全局或会话变量扩大限制，成功获取完整JSON内容。此外，还展示了如何对结果去重、排序和指定分隔符。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

记录record转换为json

1. 关键字：

json_object()

group_concat()

group by

2. 分组聚合构造json数组

使用分组聚合生成JSON数组；

SELECT T.class_id,CONCAT('[',GROUP_CONCAT(T.temp),']') AS CONTENT
FROM (
	SELECT class_id, JSON_OBJECT( 
		'id', id,
		'class_id', class_id,
		'class_name', class_name,
		'stu_id', stu_id,
		'stu_name', stu_name,
		'status', status
	) as temp
	FROM tb_stu_class_rel 
) as T
GROUP BY T.class_id

查询结果：

class_id	CONTENT
-------------------------------
20210701	[{"id": 183, "status": "1", "stu_id": 20210701001, "class_id": 20210701, "stu_name": "藏锐立", "class_name": "2021七年级一班"},{"id": 184, "status": "1", "stu_id": 20210701002, "class_id": 20210701, "stu_name": "第明明", "class_name": "2021七年级一班"},{"id": 185, "status": "1", "stu_id": 20210701003, "class_id": 20210701, "stu_name": "奚香彤", "class_name": "2021七年级一班"},{"id": 186, "status": "1", "stu_id": 20210701004, "class_id": 20210701, "stu_name": "罗荏", "class_name": "2021七年级一班"},{"id": 187, "status": "1", "stu_id": 20210701005, "class_id": 20210701, "stu_name": "汝曦晨", "class_name": "2021七年级一班"},{"id": 188, "status": "1", "stu_id": 20210701006, "class_id": 20210701, "stu_name": "悉雅静", "class_name": "2021七年级一班"},{"id": 189, "status": "1", "stu_id": 20210701007, "class_id": 20210701, "stu_name": "花锦程", "class_name": "2021七年级一班"},{"id": 190, "status": "1", "stu_id": 20210701008, "class_id": 20210701, "stu_name": "虎]

结果数据转为了json数组，但是发现并不完整，超过1024个字符外的被截掉了；

因为group_concat长度默认设置为1024，这需要要修改数据库设置：

# 两种方式：（长度最大值：32位：4294967295（2^32 - 1）， 64位：18446744073709551615(2^64 - 1)
# 1、修改mysql配置文件，添加 group_concat_max_len = 102400，然后重启mysql
# 2、使用SQL语句直接修改：（缺点：重启mysql后失效）
SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;

group_concat官方资料：

https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_group-concat

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len

在这里插入图片描述
结果：

class_id	CONTENT
-------------------------------
20210701	[{"id": 183, "status": "1", "stu_id": 20210701001, "class_id": 20210701, "stu_name": "藏锐立", "class_name": "2021七年级一班"},{"id": 184, "status": "1", "stu_id": 20210701002, "class_id": 20210701, "stu_name": "第明明", "class_name": "2021七年级一班"},{"id": 185, "status": "1", "stu_id": 20210701003, "class_id": 20210701, "stu_name": "奚香彤", "class_name": "2021七年级一班"},{"id": 186, "status": "1", "stu_id": 20210701004, "class_id": 20210701, "stu_name": "罗荏", "class_name": "2021七年级一班"},{"id": 187, "status": "1", "stu_id": 20210701005, "class_id": 20210701, "stu_name": "汝曦晨", "class_name": "2021七年级一班"},{"id": 188, "status": "1", "stu_id": 20210701006, "class_id": 20210701, "stu_name": "悉雅静", "class_name": "2021七年级一班"},{"id": 189, "status": "1", "stu_id": 20210701007, "class_id": 20210701, "stu_name": "花锦程", "class_name": "2021七年级一班"},{"id": 190, "status": "1", "stu_id": 20210701008, "class_id": 20210701, "stu_name": "虎痴梅", "class_name": "2021七年级一班"},{"id": 191, "status": "1", "stu_id": 20210701009, "class_id": 20210701, "stu_name": "钞琼思", "class_name": "2021七年级一班"},{"id": 192, "status": "1", "stu_id": 20210701010, "class_id": 20210701, "stu_name": "但康盛", "class_name": "2021七年级一班"},{"id": 193, "status": "1", "stu_id": 20210701011, "class_id": 20210701, "stu_name": "亓官语燕", "class_name": "2021七年级一班"},{"id": 194, "status": "1", "stu_id": 20210701012, "class_id": 20210701, "stu_name": "别青曼", "class_name": "2021七年级一班"},{"id": 195, "status": "1", "stu_id": 20210701013, "class_id": 20210701, "stu_name": "茅泽洋", "class_name": "2021七年级一班"},{"id": 196, "status": "1", "stu_id": 20210701014, "class_id": 20210701, "stu_name": "止诗珊", "class_name": "2021七年级一班"},{"id": 197, "status": "1", "stu_id": 20210701015, "class_id": 20210701, "stu_name": "干唱月", "class_name": "2021七年级一班"},{"id": 198, "status": "1", "stu_id": 20210701016, "class_id": 20210701, "stu_name": "仇夜春", "class_name": "2021七年级一班"},{"id": 199, "status": "1", "stu_id": 20210701017, "class_id": 20210701, "stu_name": "说颖馨", "class_name": "2021七年级一班"},{"id": 200, "status": "1", "stu_id": 20210701018, "class_id": 20210701, "stu_name": "睦运浩", "class_name": "2021七年级一班"},{"id": 201, "status": "1", "stu_id": 20210701019, "class_id": 20210701, "stu_name": "温平松", "class_name": "2021七年级一班"},{"id": 202, "status": "1", "stu_id": 20210701020, "class_id": 20210701, "stu_name": "图门俊民", "class_name": "2021七年级一班"},{"id": 203, "status": "1", "stu_id": 20210701021, "class_id": 20210701, "stu_name": "捷清秋", "class_name": "2021七年级一班"},{"id": 204, "status": "1", "stu_id": 20210701022, "class_id": 20210701, "stu_name": "殳鹏池", "class_name": "2021七年级一班"},{"id": 205, "status": "1", "stu_id": 20210701023, "class_id": 20210701, "stu_name": "夕建茗", "class_name": "2021七年级一班"},{"id": 206, "status": "1", "stu_id": 20210701024, "class_id": 20210701, "stu_name": "亓凌波", "class_name": "2021七年级一班"},{"id": 207, "status": "1", "stu_id": 20210701025, "class_id": 20210701, "stu_name": "初绿蝶", "class_name": "2021七年级一班"},{"id": 208, "status": "1", "stu_id": 20210701026, "class_id": 20210701, "stu_name": "潮昊苍", "class_name": "2021七年级一班"},{"id": 209, "status": "1", "stu_id": 20210701027, "class_id": 20210701, "stu_name": "柯翰学", "class_name": "2021七年级一班"},{"id": 210, "status": "1", "stu_id": 20210701028, "class_id": 20210701, "stu_name": "苑平彤", "class_name": "2021七年级一班"},{"id": 211, "status": "1", "stu_id": 20210701029, "class_id": 20210701, "stu_name": "史乐成", "class_name": "2021七年级一班"},{"id": 212, "status": "1", "stu_id": 20210701030, "class_id": 20210701, "stu_name": "丛霞赩", "class_name": "2021七年级一班"},{"id": 213, "status": "1", "stu_id": 20210701031, "class_id": 20210701, "stu_name": "典三春", "class_name": "2021七年级一班"},{"id": 214, "status": "1", "stu_id": 20210701032, "class_id": 20210701, "stu_name": "费婉静", "class_name": "2021七年级一班"},{"id": 215, "status": "1", "stu_id": 20210701033, "class_id": 20210701, "stu_name": "旁兴思", "class_name": "2021七年级一班"},{"id": 216, "status": "1", "stu_id": 20210701034, "class_id": 20210701, "stu_name": "夏雨旋", "class_name": "2021七年级一班"},{"id": 217, "status": "1", "stu_id": 20210701035, "class_id": 20210701, "stu_name": "宁又亦", "class_name": "2021七年级一班"},{"id": 218, "status": "1", "stu_id": 20210701036, "class_id": 20210701, "stu_name": "洛妙", "class_name": "2021七年级一班"},{"id": 219, "status": "1", "stu_id": 20210701037, "class_id": 20210701, "stu_name": "频晨轩", "class_name": "2021七年级一班"},{"id": 220, "status": "1", "stu_id": 20210701038, "class_id": 20210701, "stu_name": "革和怡", "class_name": "2021七年级一班"},{"id": 221, "status": "1", "stu_id": 20210701039, "class_id": 20210701, "stu_name": "敏奇水", "class_name": "2021七年级一班"},{"id": 222, "status": "1", "stu_id": 20210701040, "class_id": 20210701, "stu_name": "淡雪晴", "class_name": "2021七年级一班"},{"id": 223, "status": "1", "stu_id": 20210701041, "class_id": 20210701, "stu_name": "修凝静", "class_name": "2021七年级一班"},{"id": 224, "status": "1", "stu_id": 20210701042, "class_id": 20210701, "stu_name": "嵇恨", "class_name": "2021七年级一班"},{"id": 225, "status": "1", "stu_id": 20210701043, "class_id": 20210701, "stu_name": "疏涵润", "class_name": "2021七年级一班"},{"id": 226, "status": "1", "stu_id": 20210701044, "class_id": 20210701, "stu_name": "展宏恺", "class_name": "2021七年级一班"},{"id": 227, "status": "1", "stu_id": 20210701045, "class_id": 20210701, "stu_name": "惠嘉勋", "class_name": "2021七年级一班"},{"id": 228, "status": "1", "stu_id": 20210701046, "class_id": 20210701, "stu_name": "池新晴", "class_name": "2021七年级一班"},{"id": 229, "status": "1", "stu_id": 20210701047, "class_id": 20210701, "stu_name": "候乐荷", "class_name": "2021七年级一班"},{"id": 230, "status": "1", "stu_id": 20210701048, "class_id": 20210701, "stu_name": "佴佁然", "class_name": "2021七年级一班"},{"id": 231, "status": "1", "stu_id": 20210701049, "class_id": 20210701, "stu_name": "书雅逸", "class_name": "2021七年级一班"},{"id": 232, "status": "1", "stu_id": 20210701050, "class_id": 20210701, "stu_name": "纪皎", "class_name": "2021七年级一班"},{"id": 233, "status": "1", "stu_id": 20210701051, "class_id": 20210701, "stu_name": "慈梅风", "class_name": "2021七年级一班"},{"id": 234, "status": "1", "stu_id": 20210701052, "class_id": 20210701, "stu_name": "潭令燕", "class_name": "2021七年级一班"},{"id": 235, "status": "1", "stu_id": 20210701053, "class_id": 20210701, "stu_name": "丑蔓菁", "class_name": "2021七年级一班"},{"id": 236, "status": "1", "stu_id": 20210701054, "class_id": 20210701, "stu_name": "黎禄", "class_name": "2021七年级一班"},{"id": 237, "status": "1", "stu_id": 20210701055, "class_id": 20210701, "stu_name": "钟离良畴", "class_name": "2021七年级一班"},{"id": 238, "status": "1", "stu_id": 20210701056, "class_id": 20210701, "stu_name": "奈娟丽", "class_name": "2021七年级一班"},{"id": 239, "status": "1", "stu_id": 20210701057, "class_id": 20210701, "stu_name": "赫依波", "class_name": "2021七年级一班"},{"id": 240, "status": "1", "stu_id": 20210701058, "class_id": 20210701, "stu_name": "秦天元", "class_name": "2021七年级一班"},{"id": 241, "status": "1", "stu_id": 20210701059, "class_id": 20210701, "stu_name": "板羽彤", "class_name": "2021七年级一班"},{"id": 242, "status": "1", "stu_id": 20210701060, "class_id": 20210701, "stu_name": "顿幼枫", "class_name": "2021七年级一班"}]

另，参考官方例子，还可以对结果去重、排序和指定分隔符（默认分隔符使用’,’）；

SELECT student_name,GROUP_CONCAT(
    DISTINCT test_score 
    ORDER BY test_score 
    DESC SEPARATOR ' '
)
FROM student
GROUP BY student_name;

测试数据：

INSERT INTO tb_stu_class_rel 
(id, class_id, class_name, stu_id, stu_name, status) 
VALUES
(null,20210701, '2021七年级一班', 20210701001, '藏锐立', '1'),
(null,20210701, '2021七年级一班', 20210701002, '第明明', '1'),
(null,20210701, '2021七年级一班', 20210701003, '奚香彤', '1'),
(null,20210701, '2021七年级一班', 20210701004, '罗荏', '1'),
(null,20210701, '2021七年级一班', 20210701005, '汝曦晨', '1'),
(null,20210701, '2021七年级一班', 20210701006, '悉雅静', '1'),
(null,20210701, '2021七年级一班', 20210701007, '花锦程', '1'),
(null,20210701, '2021七年级一班', 20210701008, '虎痴梅', '1'),
(null,20210701, '2021七年级一班', 20210701009, '钞琼思', '1'),
(null,20210701, '2021七年级一班', 20210701010, '但康盛', '1'),
(null,20210701, '2021七年级一班', 20210701011, '亓官语燕', '1'),
(null,20210701, '2021七年级一班', 20210701012, '别青曼', '1'),
(null,20210701, '2021七年级一班', 20210701013, '茅泽洋', '1'),
(null,20210701, '2021七年级一班', 20210701014, '止诗珊', '1'),
(null,20210701, '2021七年级一班', 20210701015, '干唱月', '1'),
(null,20210701, '2021七年级一班', 20210701016, '仇夜春', '1'),
(null,20210701, '2021七年级一班', 20210701017, '说颖馨', '1'),
(null,20210701, '2021七年级一班', 20210701018, '睦运浩', '1'),
(null,20210701, '2021七年级一班', 20210701019, '温平松', '1'),
(null,20210701, '2021七年级一班', 20210701020, '图门俊民', '1'),
(null,20210701, '2021七年级一班', 20210701021, '捷清秋', '1'),
(null,20210701, '2021七年级一班', 20210701022, '殳鹏池', '1'),
(null,20210701, '2021七年级一班', 20210701023, '夕建茗', '1'),
(null,20210701, '2021七年级一班', 20210701024, '亓凌波', '1'),
(null,20210701, '2021七年级一班', 20210701025, '初绿蝶', '1'),
(null,20210701, '2021七年级一班', 20210701026, '潮昊苍', '1'),
(null,20210701, '2021七年级一班', 20210701027, '柯翰学', '1'),
(null,20210701, '2021七年级一班', 20210701028, '苑平彤', '1'),
(null,20210701, '2021七年级一班', 20210701029, '史乐成', '1'),
(null,20210701, '2021七年级一班', 20210701030, '丛霞赩', '1'),
(null,20210701, '2021七年级一班', 20210701031, '典三春', '1'),
(null,20210701, '2021七年级一班', 20210701032, '费婉静', '1'),
(null,20210701, '2021七年级一班', 20210701033, '旁兴思', '1'),
(null,20210701, '2021七年级一班', 20210701034, '夏雨旋', '1'),
(null,20210701, '2021七年级一班', 20210701035, '宁又亦', '1'),
(null,20210701, '2021七年级一班', 20210701036, '洛妙', '1'),
(null,20210701, '2021七年级一班', 20210701037, '频晨轩', '1'),
(null,20210701, '2021七年级一班', 20210701038, '革和怡', '1'),
(null,20210701, '2021七年级一班', 20210701039, '敏奇水', '1'),
(null,20210701, '2021七年级一班', 20210701040, '淡雪晴', '1'),
(null,20210701, '2021七年级一班', 20210701041, '修凝静', '1'),
(null,20210701, '2021七年级一班', 20210701042, '嵇恨', '1'),
(null,20210701, '2021七年级一班', 20210701043, '疏涵润', '1'),
(null,20210701, '2021七年级一班', 20210701044, '展宏恺', '1'),
(null,20210701, '2021七年级一班', 20210701045, '惠嘉勋', '1'),
(null,20210701, '2021七年级一班', 20210701046, '池新晴', '1'),
(null,20210701, '2021七年级一班', 20210701047, '候乐荷', '1'),
(null,20210701, '2021七年级一班', 20210701048, '佴佁然', '1'),
(null,20210701, '2021七年级一班', 20210701049, '书雅逸', '1'),
(null,20210701, '2021七年级一班', 20210701050, '纪皎', '1'),
(null,20210701, '2021七年级一班', 20210701051, '慈梅风', '1'),
(null,20210701, '2021七年级一班', 20210701052, '潭令燕', '1'),
(null,20210701, '2021七年级一班', 20210701053, '丑蔓菁', '1'),
(null,20210701, '2021七年级一班', 20210701054, '黎禄', '1'),
(null,20210701, '2021七年级一班', 20210701055, '钟离良畴', '1'),
(null,20210701, '2021七年级一班', 20210701056, '奈娟丽', '1'),
(null,20210701, '2021七年级一班', 20210701057, '赫依波', '1'),
(null,20210701, '2021七年级一班', 20210701058, '秦天元', '1'),
(null,20210701, '2021七年级一班', 20210701059, '板羽彤', '1'),
(null,20210701, '2021七年级一班', 20210701060, '顿幼枫', '1')