记录下最近 MySQL JSON 函数的使用

超级加速同调

起因

在最近的一个项目实施过程中，由于原有数据存储的格式，需要从一大串的 JSONObject 或 JSONArray 中循环获取、或指定读取某一个 key 对应的值，通过原有的 concat + like 的组合效率已经远远不行了，这个时候想到了 MySQL 自5.7以来对JSON函数的支持，想着通过这样的方式来尝试下。

// 原有数据
    "key1": "value1",
    "key2": "value2",
    "key3": 3,
    "object1": {
        "object1Key1": "object1Value1",
        "object1Array": [
                "object1ArrayKey1": "object1ArrayValue1",
                "object1ArrayKey2": "object1ArrayValue2"
                "object1ArrayKey1": "object1ArrayValue3",
                "object1ArrayKey2": "object1ArrayValue4"
    "array1": [
            "array1Key1": "array1Value1",
            "array1Key2": 2
            "array1Key1": "array1Value3",
            "array1Key2": 4
}

原有数据大致是这样的，仍然需要注意的一点是存储在数据库的的数据类型是longtext，不是json格式。

接下来，我们通过 MySQL 的一系列函数+表，来获取我们所需要的一个数据。在此之前，我们先初始化下。

CREATE TABLE `test` (
  `str` varchar(600) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO test (str)
VALUES ('{"key1":"value1","key2":"value2","key3":3,"object1":{"object1Key1":"object1Value1","object1Array":[{"object1ArrayKey1":"object1ArrayValue1","object1ArrayKey2":"object1ArrayValue2"},{"object1ArrayKey1":"object1ArrayValue3","object1ArrayKey2":"object1ArrayValue4"}]},"array1":[{"array1Key1":"array1Value1","array1Key2":2},{"array1Key1":"array1Value3","array1Key2":4}]}');

1.获取 "key3"，相对而言，这个是比较简单的。

select json_extract(str, '$.key3') key3 from test;

2.获取 "key3" 和 "object1ArrayKey1"

对于object1ArrayKey1而言，他是在"object1Array"数值下的对象的一个key，所以想要获取这样的一个值，我们需要遍历这个对象这个时候就需要用到mysql本身自带的一张表来帮助我们实现。

select * from mysql.help_topic;

select json_extract(str, '$.key3')        key3,
       json_extract(json_unquote(json_extract(json_extract(str, '$.object1.object1Array'),
                                              concat('$[', help_topic.help_topic_id, ']'))),
                    '$.object1ArrayKey1') object1ArrayKey1
from test
         join mysql.help_topic on help_topic_id < json_length(json_extract(str, '$.object1.object1Array'));

3.获取"array1"下"array1Key1"值为'array1Value3'的对象的"array1Key2"的值

select json_extract(str,
                    replace(json_unquote(json_search(str, 'all', 'array1Value3', null, '$.array1[*].array1Key1')),
                            'array1Key1',
                            'array1Key2')) array1Key2
from test;

先用json_search函数后去"array1"下"array1Key1"值为'array1Value3'所处位置，把"array1Key1"替换为"array1Key2"再进行搜索；
还有点需要注意，json_search搜索出来的结果待引号，可以用json_unquote函数来取消引号，返回字符串；
另外使用json_search时，第三个参数，也就是待搜索值得是一个字符串。

在此基础上，我们还发现如果对于数组获取其对象属性时，数组在有限长度下，使用自己手写的序列表会比mysql.help_topic快，如下。

select 1 num
union
select 2 num