记录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')