MySQL JSON操作介绍

MySQL自版本5.7.8开始引入了对JSON的支持。毕竟随着JSON数据格式越来越流行，数据库字段类型支持JSON能给很多业务提供更大的便捷性，下面我们直接介绍对MySQL里JSON字段的增删改查操作。

建字段

首先我们建一张表testJson，里面有个jsonField的字段为json类型。

CREATE TABLE testJson (
  id INT AUTO_INCREMENT PRIMARY KEY,
  jsonField JSON
);

增

新增一个json数据

insert into testJson (jsonField)
values ('{
    "school":"NO.1 Primary School",
    "brief":"good class",
    "class" : {
        "grade" : "three",
        "studentCount" : 30,
        "teacher" : {
            "name" : "John",
            "gender" : "male"
        }
    },
    "students" : [
        {
            "name" : "Jack",
            "gender" : "male",
            "age":13
        },
        {
            "name" : "Tom",
            "gender" : "male",
            "age":13
        },
        {
            "name" : "Marry",
            "gender" : "female",
            "age":14
        }
    ]
}');

JSON里新增一个一级字段

UPDATE testJson SET jsonField = JSON_SET(jsonField, '$.headMaster', 'William') WHERE id = 1;

JSON里新增一个二级字段

UPDATE testJson SET jsonField = JSON_SET(jsonField, '$.class.courseNum', 5) WHERE id = 1;

JSON数组里新增一个元素

UPDATE testJson SET jsonField = JSON_ARRAY_APPEND(jsonField, '$.students', JSON_OBJECT('name', 'Cindy', 'gender', 'female', 'age', 12)) WHERE id = 1;

从上面的新增语句可以看出，新增JSON里的元素本质是用JSON_SET和JSON_ARRAY_APPEND 两个方法，一个用于新增字段，一个用于新增元素，然后通过$代表json最顶层位置去一级一级定位要新增字段的位置。

查

根据json里的字段查询

SELECT * FROM testJson WHERE JSON_EXTRACT(jsonField, '$.headMaster') = 'William';

根据json里的数组元素做查询

select jsonField ->'$.students[*]' from testJson where JSON_CONTAINS(jsonField->'$.students[*].name',JSON_ARRAY('Cindy'),'$');

上面的查询语句分别根据headMaster字段为William的全部数据，以及students里有name为Cindy的记录里的students数据。

第一个查询使用了JSON_EXTRCT方法，它会根据传的path路径（$.headMaster）返回对应的数据，我们根据返回数据是否是William来过滤结果。

第二个查询使用的JSON_CONTAINS方法，该方法第一个参数表示用于做判断的内容，这里使用jsonField->’$.students[*].name’，表示要查询students的name字段，其中[*]表示students是数组内容，第二个参数是代表一个具体值用于匹配第一个参数的字段，这里使用JSON_ARRAY代表返回一个数组字段包含JSON_ARRAY里定义的数据。

改

修改JSON里一级数据

update testJson set jsonField=JSON_REPLACE(jsonField, '$.headMaster', 'Joe') where JSON_EXTRACT(jsonField, '$.headMaster') = 'William';

上面这条查询语句通过JSON_EXTRACT提取文档然后通过JSON_REPLACE方法做更新

修改JSON里的数组数据

UPDATE testJson
SET jsonField = JSON_SET(jsonField, '$.students', (
  SELECT JSON_ARRAYAGG(
    JSON_OBJECT('name', IF(element ->> '$.name' = 'Cindy', 'Clark', element ->> '$.name'), 'age', element ->> '$.age', 'gender', element ->> '$.gender')
  )
  FROM JSON_TABLE(
    jsonField -> '$.students',
    "$[*]" COLUMNS (
      element JSON PATH '$'
    )
  ) AS t
))

上面的修改语句本质是对students里有名为Cindy的改为Clark，整个修改语句用到了比较多的JSON相关方法。

我们首先看里面的

SELECT JSON_ARRAYAGG() FROM JSON_TABLE() as t

该方法显示通过JSON_TABLE构建了一个新的子表。

接着我们通过JSON_ARRAYAGG重新生成一个JSON数组，而数组的元素内容就由JSON_OBJECT构造。

JSON_OBJECT('name', IF(element ->> '$.name' = 'Cindy', 'Clark', element ->> '$.name'), 'age', element ->> '$.age', 'gender', element ->> '$.gender')
  )

JSON_OBJECT构造了有name,age,gender三个字段的元素，其中age和gender直接使用element里的age和gender,name字段用了一个IF判断，如果值为Cindy就改为Clark，否则使用element的name。到这里我们基本就实现了把Cindy改为Clark。最后用一个JSON_SET方法用于设置到students字段。整个修改语句的执行效率如下图。

删

删除JSON里的一级字段

update testJson set jsonField=JSON_REMOVE(jsonField, '$.headMaster') where id = 1;

删除JSON数组里的元素

UPDATE testJson
SET jsonField = JSON_REPLACE(jsonField, '$.students',
  JSON_REMOVE(JSON_EXTRACT(jsonField, '$.students'),
    JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(jsonField, '$.students[*].name'), 'all', 'Clark'))))
WHERE JSON_SEARCH(JSON_EXTRACT(jsonField, '$.students[*].name'), 'all', 'Clark') IS NOT NULL;

上面的删除语句是把students里名为Clark的学生给删除了。该语句也用到了比较多的JSON方法。首先看里面的JSON_SEARCH方法。

• 该方法第一个参数是要查询的内容，我们通过JSON_EXTRACT(jsonField, ‘$.students[*].name’)得到students数组里的所有name字段[‘Jack’,  ‘Tom’,  ‘Mary’, ‘Clark’]。
• 第二个参数all代表的是全部匹配，它只有两种值’one’,’all’，’one’代表只搜索第一个匹配的。
• 第三个元素代表要查找的内容。

通过上面的JSON_SEARCH我们会对应得到name为Clark的元素位置

select JSON_SEARCH(JSON_EXTRACT(jsonField, '$.students[*].name'), 'all', 'Clark') from testJson

类似上面的查询语句会得到”$[3]”，代表在数组里的第4个元素，但该函数返回的是个字符串，我们需要通过JSON_UNQUOTE做下处理去掉字符串变成$[3]。

然后使用JSON_REMOVE

• 该方法第一个参数是要删除的内容，我们通过JSON_EXTRACT(jsonField, ‘$.students’)提取了students数据。
• 第二个参数代表要删除的内容即上面得到的$[3]。

删除后的数据再通过JSON_REPLACE替换到students数据，最后就得到我们要的删除效果了。

结语

通过上面对增删改查的介绍，大家应该基本对MySQL怎么操作JSON有基础的认知，MySQL对JSON的支持整体来说丰富了整个数据库的能力，但我们也看到在处理一些JSON数组相关的内容时，整个MySQL语句还是比较复杂的。大家可以对比下MongoDB常用操作介绍，看看语法的简洁性以及对应的性能，纯操作JSON，MongoDB在性能上还是有极大优势，我自己做的测试，相同的要求，MongoDB处理json的效率比MySQL快基本10倍以上。因此具体使用上，大家还是根据各自业务需求做数据库选项。