MySQL中的JSON解析

自从MySQL5.7开始，MySQL对于JSON的支持越来越完善，不仅有json数据格式，还有各种内置的json处理方法。本文主要介绍题目用到的几种常见方法，更多方法读者可以查阅相关文档

解析JSON

  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;
    set v_students = cast(json_unquote(json_extract(p_input,'$.students')) as json);
复制代码

先声明两个变量 v_teachers 和 v_students ，类型都是JSON，在MySQL里面，数组和对象都是JSON格式的。

这里用了3个内置方法，一种新的写法

json_extract : 返回对象中某个key的值，这个方法会以带着双引号的字符串的形式返回

json_unquote : 去掉字符串的双引号

cast(内容 as 转换的类型) : 把内容转化为特定类型

->> : JSON解析，相当于直接取某个key，支持路径的写法，比如'$.teachers[0].number'

掌握了这几个，JSON对象的解析基本就没什么问题了

拿到v_teachers数组后，接下来就要开始循环数组处理了

  declare v_length int;
  declare v_i int;
  declare v_temp json;
  set v_length = JSON_LENGTH(v_teachers);
  set v_i = 0;
复制代码

先声明几个变量用作循环中的值

v_length : v_teachers数组的长度

v_i : 循环的下标，就是普通循环中的i的作用

v_temp : 临时变量，取v_teachers数组中每个对象的值保存

json_length : 计算数组长度的内置方法

接下来就是while循环了

  declare v_name varchar(255);
  declare v_age int;
  declare v_number varchar(255);
  while v_i < v_length do
    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);
    #TODO
    set v_i = v_i + 1;
  end while;
复制代码

MySQL里面没有好用的循环，while循环已经算是比较清晰的了。这里又声明了几个变量来获取每个teachers对象里面的值

  declare v_count int;
  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(v_number, '该工号已经存在')
    #TODO
  end if;
复制代码

这里从teacher_table取count出来是为了看一下表里是不是已经有工号等于T002的老师了，如果有的话就不能再插入了，要报错，本存储过程使用两个out参数来作为错误信息的返回。

问题在于，出错后就不能继续执行了，需要跳出循环或者结束整个存储过程的执行（根据业务需求）。存储过程中有个label的标记，可以在任何内部执行中跳出label代码段的过程

label_at_start:
BEGIN
  leave label_at_start;
复制代码

现在假设如果遇到重复的教师工号就跳过该条数据，完善逻辑并且添加完整的插入语句后的代码如下

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 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(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);
  #TODO
  if p_return_code is null THEN
    set p_return_code = 'S';
  end if;
复制代码

最后还判断了一下如果 p_return_code is null 就赋给它一个S，这样能保证整个存储过程的返回是S代表成功，E代表失败

类似的逻辑可以写出对于学生数据的处理

  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(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(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;
复制代码