给MySQL存储过程传JSON对象

引言

我们经常会遇到需要给存储过程传递数组对象，但迫于技术上的问题，只能简化成处理单个对象。如果对象有10个属性，存储过程就要传递10个参数，如果只想传5个参数，那是无法做到的。

如果存储过程参数能传对象或数组，对象的属性是可以缺漏的，这样存储过程就可以适应性多种情况，功能也更强大。

MySQL从5.7开始支持JSON数据类型，提供了操作JSON的函数，让我们的想法成为现实。

原理

MySQL解析JSON的函数有：JSON_EXTRACT、JSON_LENGTH、JSON_UNQUOTE。通过这三个函数就能提取到json的值。下面介绍下这几个函数的功能和用法。

1. JSON_EXTRACT
   按路径获取json值。用法：JSON_EXTRACT(json,path)。

2. JSON_LENGTH
   获取指定路径元素的个数。用法：JSON_LENGTH(json,path)。

3. JSON_UNQUOTE
   去除双引号。用法：JSON_UNQUOTE(json)。

• 路径的语法如下：
  $.key:提取JSON文档顶层的键值对中指定键的值
  $.key1.key :提取JSON文档中嵌套结构的值
  $.array[index]:提取JSON文档中数组中指定索引位置的值

示例

下面以添加商品为例，演示怎么解析JSON。下图为JSON的内容：

{
  "user_id": "adefad6db14611ef932db888e3dfbf64",
  "items": [
    {
      "goods_id": "b2609d04b14611ef932db888e3dfbf64",
      "goods_name": "饼干",
      "amount": 100
    },
    {
      "goods_id": "b5ec2022b14611ef932db888e3dfbf64",
      "goods_name": "苹果",
      "amount": 200
    }
  ]
}

• 解析JSON代码

由于逻辑比较简单，代码中已经加了注释，就不详细描述了。

create  procedure prc_add_goods(json JSON)
begin
    declare v_goodsId varchar(64);
    declare v_goodsName varchar(64);
    declare v_userId varchar(64);
    #日志
    declare v_log varchar(2000);

    declare v_itemCount int;
    declare v_i int;
    #提取user_id
    set v_userId = JSON_UNQUOTE(JSON_EXTRACT(json, '$.user_id'));
    set v_log=concat('userId:',v_userId);

    #获取items的个数
    select JSON_LENGTH(json, '$.items') into v_itemCount;
    set v_i = 0;
    while v_i < v_itemCount
        do
            #提取goods_id
            set v_goodsId = JSON_UNQUOTE(JSON_EXTRACT(json, concat('$.items[', v_i, '].goods_id')));
            #提取goods_name
            set v_goodsName = JSON_UNQUOTE(JSON_EXTRACT(json, concat('$.items[', v_i, '].goods_name')));
            set v_log = concat(v_log,'\r\n','goodsId:' , v_goodsId , '  goodsName:' , v_goodsName);
            set v_i = v_i + 1;
        end while;
    #输出日志
    select v_log;
end;

调用测试：

call prc_add_goods('{
  "user_id": "adefad6db14611ef932db888e3dfbf64",
  "items": [
    {
      "goods_id": "b2609d04b14611ef932db888e3dfbf64",
      "goods_name": "饼干",
      "amount": 100
    },
    {
      "goods_id": "b5ec2022b14611ef932db888e3dfbf64",
      "goods_name": "苹果",
      "amount": 100
    }
  ]
}')

运行结果：

总结

本文展示了，如何在存储过程中解析JSON对象和数组。采用JSON参数可以让存储过程功能更强大，调用更友好，更多高级的应用大家可以尝试下。