• MySQL 的 JSON 格式字段用法

    1、概述

    MySQL 5.7.8 新增 JSON 数据类型，用于定义 JSON 格式的数据。
    在此之前，表中要存储 JSON 数据都是定义一个 varchar 类型字段，客户端序列化和反序列化处理。但是这种方法不具备强约束性，只要是字符串存什么都行。

    而新的 JSON 类型会校验数据格式，只能存储 JSONObject 类型和 JSONArray 类型。

    JSONObject:

    {
  "name": "aaa"
}

    JSONArray:

    [
  {"name":"aaa"}, {}
]

    键只能为字符串
    值类型支持 null, string, boolean, number, object, array

    2、定义

    创建表时指定字段类型为 JSON，JSON 类型无需指定长度，且默认值只能为 null。
    JSON 字段类型不用显示指定是对象还是数组结构，根据实际存储数据自动推断

    CREATE TABLE `t_json_tbl` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `json_obj` json DEFAULT NULL COMMENT 'json 对象字段',
  `json_arr` json DEFAULT NULL COMMENT 'json 数组字段',
  `json_str` varchar(255) DEFAULT NULL COMMENT 'json 格式字符串字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    3、插入数据

    方式 1：
    以普通字符串形式插入，需要遵循 json 格式

    insert into t_json_tbl(json_obj, json_arr, json_str) 
  values('{"name":"tom", "age":21, "tags":["a", "b"]}', '["aa", "bb", "cc"]', '{"name":"jj"}');
-- id=1

    方式 2：
    使用 JSON 内置创建函数

    创建 JSON 对象：JSON_OBJECT([key, val[, key, val] ...])
    创建 JSON 数组：JSON_ARRAY([val[, val] ...])
    函数文档：json-creation-functions

    函数可以嵌套使用

    insert into t_json_tbl(json_obj, json_arr, json_str)
  values(JSON_OBJECT('name', 'jerry', 'tags', JSON_ARRAY('c', 'd')), JSON_ARRAY('xx', 'yy', 'cc'), JSON_OBJECT('name', 'jack'));
-- id=2

    （也可以通过 JSON 函数操作普通字符串类型数据）

    注意不要这么使用:

    -- JSON_OBJECT('name', 'jerry', 'tags', '["c", "d"]') 第二个字段的值以普通字符串插入，不是数组格式
insert into t_json_tbl(json_obj, json_arr, json_str)
  values(JSON_OBJECT('name', 'jerry', 'tags', '["c", "d"]'), JSON_ARRAY('xx', 'yy', 'cc'), JSON_OBJECT('name', 'jack'));
-- id=3

    同名的字段只会保留第一个

    4、查询操作

    按照普通字符串去查询整个字段数据：

    select json_obj, json_arr from t_json_tbl;

    image

    查询 JSON 中字段的数据

    查询 JSON 字段数据，需要 column -> 'path' 形式访问指定字段的具体数据。
    注意这个 'path' 外层是有一个单引号的

    其中 column 表示要查询的数据字段列名；
    path 为 JSON 数据的访问路径，path格式为 $.path 或 $[idx]。

    • $.path 用于 JSONObject 类型数据；
    • $[idx] 用于 JSONArray 类型数据；
    • $ 代表整个 JSON 数据的 root 节点；
    • path 为访问字段 key，如果字段名包含空格，则需要用双引号包住，如 $."nick name"；
    • [idx] 是数组的索引。

    例：

    {
  "name":"tom", 
  "age":21, 
  "tags":["a", "b"]
}
    • $.name 值为 "tom"
    • $.tags[0] 值为 "a"

    查询示例： 
    select json_obj->'$.name' `name`, json_obj->'$.tags[0]' `tags0`, json_arr->'$[0]' xx from t_json_tbl;

    image

    -> 查询到的字段字符串类型还会有个双引号，还需要做一层处理
    可以使用 ->> 去除，且转义符也会去除

    select json_obj->>'$.name' `name`, json_obj->>'$.tags[0]' `tags0`, json_arr->>'$[0]' xx from t_json_tbl;

    image


    可以使用内置函数进行查询

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

    其他内置查询函数：json-search-functions

    条件查询

    select * from t_json_tbl where json_obj->'$.name' = 'Merry';

    模糊查询

    JSON 字段的模糊搜索仅支持 %str% 格式，也因此其模糊搜索时索引是失效的

    select * from t_json_tbl where json_obj->'$.name' like '%tom%';

    5、更新操作

    使用内置函数更新 JSON 字段：json-modification-functions

    更新字段

    • JSON_SET(json_doc, path, val[, path, val] ...) 更新或插入
    • JSON_REPLACE(json_doc, path, val[, path, val] ...) 只更新
    update t_json_tbl 
set 
json_obj = JSON_SET(json_obj, '$.name', 'Merry'),
json_arr = JSON_SET(json_arr, '$[0]', 'aa2222', '$[2]', 'gggg', '$[7]', 'fdsfd')
where id = 1;

    新增字段

    • JSON_INSERT(json_doc, path, val[, path, val] ...) 插入新字段，不会改变已经存在的
    update t_json_tbl 
set 
json_obj = JSON_INSERT(json_obj, '$.name', 'Merry'),
json_arr = JSON_INSERT(json_arr, '$[0]', 'aa2222', '$[2]', 'gggg', '$[7]', 'fdsfd')
where id = 2;

    删除字段

    • JSON_REMOVE(json_doc, path[, path] ...) 删除字段
    update t_json_tbl 
set 
json_obj = JSON_REMOVE(json_obj, '$.tags'),
json_arr = JSON_REMOVE(json_arr, '$[0]', '$[7]')
where id = 3;

    其他操作函数：json-modification-functions

    6、索引使用

    JSON 字段不支持原生索引，需要基于 JSON 字段创建一个生成列，然后给这个生成列创建索引。插入数据时会自动填充生成列。
    使用生成列作为条件，可以执行 like 模糊搜索，索引是生效的

    新建一个表

    CREATE TABLE `t_json_tbl2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `json_obj` json DEFAULT NULL COMMENT 'json 数据',
  `gen_col` int(11) GENERATED ALWAYS AS (json_extract(`json_obj`,'$.num')) VIRTUAL COMMENT '生成列',
  PRIMARY KEY (`id`),
  KEY `idx_gen_col` (`gen_col`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    写个存储过程，插入大量数据

    drop PROCEDURE if exists batchInsert;
DELIMITER $$
create PROCEDURE batchInsert(n int)
begin
declare i int default 0;
SET autocommit=0;
while i <= n
do
set i = i+1;
insert into t_json_tbl2(json_obj) values(JSON_OBJECT('name', concat('lisi-', i), 'num', i));
end while;
SET autocommit=1;
end $$
DELIMITER ;

-- 调用
call batchInsert(5000000);

    查询

    -- 可以这么写
select * from t_json_tbl2 where gen_col = 555555;
-- 或者这么写
select * from t_json_tbl2 where json_obj->'$.num' = 555555;

    执行计划（数值型的使用->可以生效，但是字符串型的需要用生成列作为索引条件才能生效）

    explain select * from t_json_tbl2 where gen_col = 555555;

    image

    explain select * from t_json_tbl2 where json_obj->'$.num' = 555555;

    image

    7、Java 操作

    使用 Mybatis-plus 操作，需要配置类型处理器

    实体类

    @Data
@TableName(value = "t_json_tbl", autoResultMap=true)
public class JsonTbl implements Serializable {
    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    /**
     * json 对象字段
     * 给 mybatis-plus 配置类型处理器
     */
    @TableField(typeHandler = JacksonTypeHandler.class)
    private JsonObj jsonObj;

    /**
     * json 数组字段
     */
    private String jsonArr;

    /**
     * json 格式字符串字段
     */
    private String jsonStr;

}
@Data
public class JsonObj {
    private String name;
    private Integer age;
}

    mapper

    public interface JsonTblMapper extends BaseMapper<JsonTbl> {

    JsonTbl selectBy(Long id);
    JsonTbl selectLike(String name);

}

    xml 配置文件

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="top.originyy.case11.mybatis.mapper.JsonTblMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="top.originyy.case11.mybatis.entity.JsonTbl">
        <id column="id" property="id"/>
        <!--    配置类型处理器    -->
        <result column="json_obj" property="jsonObj"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
        <result column="json_arr" property="jsonArr"/>
        <result column="json_str" property="jsonStr"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, json_obj, json_arr, json_str
    </sql>
    
    <!--  自定义 sql 使用 resultMap 返回数据 -->
    <select id="selectBy" resultMap="BaseResultMap">
        select *
        from t_json_tbl
        where
        `id`=#{id}
    </select>

    <!--  自定义 sql 使用 resultMap 返回数据 -->
    <select id="selectLike" resultMap="BaseResultMap">
        select *
        from t_json_tbl
        where
        `json_obj`->'$.name'=#{name}
    </select>

</mapper>

    8、官方文档

    MySQL 5.7 的文档：
    https://dev.mysql.com/doc/refman/5.7/en/json.html
    JSON 操作函数目录：
    json-function-reference
    索引创建：
    https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html#json-column-indirect-index
  • 相关阅读:
    bzoj 3035 二分答案+二分图最大匹配
    bzoj 1058 bst
    bzoj 1093 缩点+DP
    bzoj 1452 二维树状数组
    bzoj 1968 数学
    bzoj 1034 贪心
    牛客小白月赛12 I （tarjan求割边）
    Loj 103、10043 (KMP统计子串个数)
    poj 2356 （抽屉原理）
    hdu 1907 （尼姆博弈）
  • 原文地址：https://www.cnblogs.com/liuyiyuan/p/16388360.html
Copyright © 2020-2023  润新知