MySQL 中 Json 字段的常见用法

从 MySQL5.7.8 开始，MySQL支持 JSON 数据类型，可以有效访问 JSON 文档中的数据。这里记录一下关于 JSON 数据最常用的一些用法。

示例数据表结构：

CREATE TABLE `detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `result` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

现有1条数据：

id	result
1	{“msg”: “success”, “data”: [{“name”: “华宇大药房”, “address”: “北京市朝阳区顺义路115号”}], “status”: true}

原 JSON 数据为：

{
    "msg": "success",
    "data": [
        {
            "name": "华宇大药房",
            "address": "北京市朝阳区顺义路115号"
        }
    ],
    "status": true
}

查询

JSON 数据类型使用 column->'path' 的方式来访问需要的数据，注意这个 'path' 外层是有单引号的。其中

• column 表示要查询的数据字段列名；
• path 为 JSON 数据的访问路径，path格式为 $.path 或 $[idx]，支持多层嵌套访问；
• $.path 用于 JSONObject 类型数据；
• $[idx] 用于 JSONArray 类型数据；
• $ 代表整个 JSON 数据的 root 节点；
• path 为访问字段 key，如果字段名包含空格，则需要用双引号包住，如 $."nick name"；
• [idx] 是数组的索引；

查询 JSON 数据字段

使用上面的知识点，可以得到查询的 sql。

SELECT
    `result` -> '$.status' `status`,
    `result` -> '$.msg' `msg`,
    `result` -> '$.data[0].name' `data_0_name` 
FROM
    `detail`

输出：

status	msg	data_0_name
true	“success”	“华宇大药房”

可以看到，这样查询到的属性还带有引号，可以使用 ->> 代替 ->，转义符也会去除。

新的sql：

SELECT
    `result` -> '$.status' `status`,
    `result` ->> '$.msg' `msg`,
    `result` ->> '$.data[0].name' `data_0_name` 
FROM
    `detail`

输出：

status	msg	data_0_name
true	success	华宇大药房

这里的箭头等价于内置函数：

• JSON_EXTRACT(column, path) 等价于 column->path
• JSON_UNQUOTE(JSON_EXTRACT(column, path)) 等价于 column->>path

上面的 sql 可以使用内置函数来写：

SELECT
    JSON_EXTRACT( `result`, '$.status' ) `status`,
    JSON_UNQUOTE( JSON_EXTRACT( `result`, '$.msg' ) ) `msg`,
    JSON_UNQUOTE( JSON_EXTRACT( `result`, '$.data[0].name' ) ) `data_0_name` 
FROM
    `detail`

条件查询

SELECT * FROM `detail` WHERE `result` -> '$.status' = true

模糊查询

SELECT * FROM `detail` WHERE `result` -> '$.data[0].name' like '%华宇%'

更新

与更新对应的有几种不同的类型操作：

JSON_INSERT 新增字段

JSON_INSERT(json_doc, path, val[, path, val] ...) 会在路径上添加新的字段，不会改变已经存在的字段。

使用 JSON_INSERT 向这条数据中的 JSON 数据的 data 数组第一个对象数据中插入一个新的属性 "id": 610。

UPDATE `detail`
SET 
`result` = JSON_INSERT(`result`, '$.data[0].id', 610)
WHERE id = 1

修改后的 JSON 数据：

{
    "msg": "success",
    "data": [
        {
            "id": 610,
            "name": "华宇大药房",
            "address": "北京市朝阳区顺义路115号"
        }
    ],
    "status": true
}

JSON_SET 更新或插入

函数 JSON_SET(json_doc, path, val[, path, val] ...) 更新或插入，使用 JSON_SET 将这条数据中的 JSON 数据的 data 数组第一个对象数据中的 name 修改为 新华宇大药房，同时新增 "phone": "18888888888" 。

UPDATE `detail`
SET 
`result` = JSON_SET(`result`, '$.data[0].name', '新华宇大药房', '$.data[0].phone', '18888888888')
WHERE id = 1

更新后的 JSON

{
    "msg": "success",
    "data": [
        {
            "id": 610,
            "name": "新华宇大药房",
            "phone": "18888888888",
            "address": "北京市朝阳区顺义路115号"
        }
    ],
    "status": true
}

JSON_REPLACE 更新

函数 JSON_REPLACE(json_doc, path, val[, path, val] ...) 只更新，用法与上面类似。

UPDATE `detail`
SET 
`result` = JSON_REPLACE(`result`, '$.data[0].name', '新新华宇大药房')
WHERE id = 1

JSON_REMOVE 删除

函数 JSON_REMOVE(json_doc, path[, path] ...) 删除对象中的字段或删除数组中对应索引上的值。

UPDATE `detail`
SET 
`result` = JSON_REMOVE(`result`, '$.data[0].phone')
WHERE id = 1

删除后的 JSON

{
    "msg": "success",
    "data": [
        {
            "id": 610,
            "name": "新华宇大药房",
            "address": "北京市朝阳区顺义路115号"
        }
    ],
    "status": true
}

这里只记录了最基本的使用，更多 JSON 数据相关的操作请参考：MySQL – JSON 数据类型