MySQL 聚合成 json 格式化输出

Posted on In Views:
MySQL 聚合成 json 格式化输出

Json 格式化输出

MySQL 实现

  • 将我们的数据库按照 json 的格式，格式化输出
  • 管理员模式开启 MySQL 服务
1
2
# 名字和安装时相关
net start MYSQL80
  • 进入命令行模式
1
2
# 得输入密码
mysql -u root -p

建表

  • 新建数据库并使用
1
2
create database json_format;
use json_format;
  • 导入数据
    • 代码
    • 一些修改
      • 约束条件在插入数据之后
      • 一些数据补全，缺省的补为 null
      • 第 3 个约束条件笔误，最后面是 tno
  • 查看有哪些表
1
show tables;

输出为 json

  • 新建一个表，用于输出结果
1
2
3
create table stu_info (
    info json
);
  • 原始数据
1
select * from student;

  • 如下指令可以生成除了课程之外的 json 数据
1
2
3
4
5
6
select CONCAT('{',
              '"sno": "', s.sno, '",',
              '"name": "', s.sname, '",',
              '"course": ', '[]',
              '}') info
from student s

  • 接着只需要生成课程数据即可
1
2
3
4
5
6
7
8
9
10
11
select
    s.sno,
    group_concat(
        CONCAT('{',
               '"cname": "', c.cname, '",',
               '"grade": ', sc.grade,
               '}')
    ) course_info
from student s, sc, course c
where s.sno = sc.sno and sc.cno = c.cno
group by sno;
  • 结果如下
image-20210623160857468
  • 接着我们把上面两个结合在一起即可
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 插入数据部分
-- insert stu_info
select CONCAT('{',
              '"sno": "', s.sno, '",',
              '"name": "', s.sname, '",',
              '"course": ', '[' , M.course_info, ']',
              '}') info
from student s, (
    select
    s.sno,
    group_concat(
        CONCAT('{',
               '"cname": "', c.cname, '",',
               '"grade": ', sc.grade,
               '}')
    ) course_info
    from student s, sc, course c
    where s.sno = sc.sno and sc.cno = c.cno
    group by sno
) M
where s.sno = M.sno;

  • 我们可以把上面的数据插入到一个表里，然后输出成 json 文件即可
    • 上面代码取消第二行的注释即可
  • 输出结果
    • 如下命令得开权限，否则会报错
1
2
3
4
5
select CONCAT(
    '{', '\"students\": ', '[' , group_concat(info), ']', '}'
) stu_infos
from stu_info
into outfile 'D:MyTemp/stu_infos.json';
  • 会有字符串缺失的问题
    • 可以修改最长字符串大小
1
2
3
4
-- 设置最长字符串大小
set SESSION group_concat_max_len=10000;
-- 查看最长字符串大小
show variables like 'group_concat_max_len';
  • 可以直接在外面执行命令，直接命令行
    • -N 表示取消表头
    • 另外一种解决方法是输出的时候，每行一条 json 数据
1
mysql -N -h 127.0.0.1 -u root -p  --default-character-set=UTF8 -e "set SESSION group_concat_max_len=10000; select CONCAT('{', '\"students\": ', '[' , group_concat(info), ']', '}') stu_infos from stu_info" json_format > D:/MyTemp/stu_infos.json
  • 结果如下

  • 格式化结果如下