MySQL中的JSON输出

数据输出

在题目中还有个输出的要求，代码如下

  select CONCAT('[', GROUP_CONCAT(JSON_QUOTE(st.name)), ']') from student_table st
  left join teacher_table tt on st.teached_by = tt.teacher_id
  where tt.number = 'T002';

JSON_QUOTE给值加上引号，形成一个“假”的字符串
GROUP_CONCAT将值用特定符号连接，默认逗号。有“多行合并一行”的功能
CONCAT使用concat给两边加上方括号，形成一个“假”的数组

完成后的整个存储过程如下

CREATE PROCEDURE `update_info`(in p_input json, out p_return_code varchar(255), out p_return_message varchar(255))
BEGIN
  declare v_teachers json default cast(p_input ->> '$.teachers' as json);
  declare v_students json;
  
  declare v_length int;
  declare v_i int;
  
  set p_return_message = '';
  
  set v_length = JSON_LENGTH(v_teachers);
  set v_i = 0;
  while v_i < v_length do
    label_at_teacher_loop:
    BEGIN
      declare v_temp json;
      declare v_name varchar(255);
      declare v_age int;
      declare v_number varchar(255);
      declare v_count int;

      set v_temp = json_extract(v_teachers,concat('$[',v_i,']'));
      set v_name = cast(json_unquote(json_extract(v_temp,'$.name')) as CHAR);
      set v_age = cast(json_unquote(json_extract(v_temp,'$.age')) as UNSIGNED);
      set v_number = cast(json_unquote(json_extract(v_temp,'$.number')) as CHAR);

      select count(1) into v_count
      from teacher_table
      where number = v_number;

      if v_count > 0 then
        set p_return_code = 'E';
        set p_return_message = concat(p_return_message, ',', v_number, '该工号已经存在');
        leave label_at_teacher_loop;
      end if;

      insert into teacher_table(name, age, number)
      values (v_name, v_age, v_number);
    END;

    set v_i = v_i + 1;
  end while;
  
  set v_students = cast(json_unquote(json_extract(p_input,'$.students'))as json);
  set v_length = JSON_LENGTH(v_students);
  set v_i = 0;
  while v_i < v_length do
    label_at_student_loop:
    BEGIN
      declare v_temp json;
      declare v_name varchar(255);
      declare v_age int;
      declare v_number varchar(255);
      declare v_teached_by varchar(255);
      declare v_teached_by_id int;
      declare v_count int;

      set v_temp = json_extract(v_students,concat('$[',v_i,']'));
      set v_name = cast(json_unquote(json_extract(v_temp,'$.name')) as CHAR);
      set v_age = cast(json_unquote(json_extract(v_temp,'$.age')) as UNSIGNED);
      set v_number = cast(json_unquote(json_extract(v_temp,'$.number')) as CHAR);
      set v_teached_by = cast(json_unquote(json_extract(v_temp,'$.teached_by')) as CHAR);

      select count(1) into v_count
      from student_table
      where number = v_number;

      if v_count > 0 then
        set p_return_code = 'E';
        set p_return_message = concat(p_return_message, ',', v_number, '该学号已经存在');
        leave label_at_student_loop;
      end if;

      select teacher_id into v_teached_by_id
      from teacher_table
      where number = v_teached_by;

      if v_teached_by_id is null then
        set p_return_code = 'E';
        set p_return_message = concat(p_return_message, ',', v_teached_by, '该工号不存在');
        leave label_at_student_loop;
      end if;

      insert into student_table(name, age, number, teached_by)
      values (v_name, v_age, v_number, v_teached_by_id);
    END;

    set v_i = v_i + 1;
  end while;
  
  if p_return_code is null THEN
    set p_return_code = 'S';
  end if;
  
  select concat('[', GROUP_CONCAT(JSON_QUOTE(st.name)), ']') from student_table st
  left join teacher_table tt on st.teached_by = tt.teacher_id
  where tt.number = 'T002';
  
END

常用JSON输出

单个JSON

select json_object(
  'name', name,
  'age', age,
  'number', number
)
from student_table;

使用json_object与普通的SQL查询基本一样

单个ARRAY

select json_array('value1', 'value2');
select json_array(name, age, number) from student_table;

json_array没有“多行合并一行”的功能，每行记录出来都单独形成一个ARRAY

多行合并ARRAY

多行合并的例子刚才我们已经用到了

  select CONCAT('[', GROUP_CONCAT(JSON_QUOTE(st.name)), ']') from student_table st
  left join teacher_table tt on st.teached_by = tt.teacher_id
  where tt.number = 'T002';

GROUP_CONCAT是少有的拥有把多行数据合并成一行输出能力的内置方法

但需要注意的是，GROUP_CONCAT连接后的最长长度默认只有1024个字符，因此如果有长需求的时候需要更改全局变量或者临时设置一下会话变量

SET GLOBAL group_concat_max_len=1024000;

SET SESSION group_concat_max_len=1024000;
SET local group_concat_max_len=1024000;
SET @@group_concat_max_len=1024000;

GLOBAL设置的是全局变量，但需要注意的是全局变量是已经从配置文件中读取出来的了，更改变量不会更改配置文件。另外其他已经建立的连接是从之前的全局变量复制出来的全局变量，不会自动更新其他三种都是更改的会话变量，效果一样，更改后在当前会话中立马生效

复杂对象数组

如果我们想输出这样的格式要怎么写

{
  "keya": {
    "test": "value"
  },
  "keyb": ["value1", "value2"]
}

跟直觉一致

  select JSON_OBJECT(
    'keya', JSON_OBJECT(
      'test', 'value'
    ),
    'keyb', JSON_ARRAY('value1', 'value2')
  );

但是如果我们想在某个值上放上多行合并的数组怎么办呢，比如我们想把学生表里姓名取出一个数组["Tony", "Bob"]

  {
    "students": ["Tony", "Bob"]
  }

刚开始可能会尝试把刚才写的直接拿过来用

select json_object(
  'student', select CONCAT('[', GROUP_CONCAT(JSON_QUOTE(st.name)), ']') from student_table st left join teacher_table tt on st.teached_by = tt.teacher_id where tt.number = 'T002'
);
或者
select json_object(
  'student', CONCAT('[', GROUP_CONCAT(JSON_QUOTE(st.name)), ']')
)
from student_table st left join teacher_table tt on st.teached_by = tt.teacher_id where tt.number = 'T002';

结果发现concat出来的实际是字符串，里面的引号会被转义

  {
    "student": "[\"Tony\",\"Bob\"]"
  }

遇到这种情况，可以使用json_merge来将字符串的数组变成一个真的数组

select json_object(
  'student', json_merge(json_array(), CONCAT('[', GROUP_CONCAT(JSON_QUOTE(st.name)), ']'))
)
from student_table st left join teacher_table tt on st.teached_by = tt.teacher_id where tt.number = 'T002';

json_merge本来是将多个对象合并一个对象的，类似于JavaScript中的Object.assign()。事实上在MySQL中，json格式跟字符串会进行很多隐式的转换，因此将一个字符串数组合并到一个真的数组中的话，MySQL就会默认进行转换

MySQL中JSON操作的方法很多，这里只是列举几种常用的，详细的可以查阅官方文档

06.实战练习-JSON的输出