MySQL 的 JSON 格式字段用法 |
您所在的位置：网站首页 › mysql存入数组 › MySQL 的 JSON 格式字段用法 |
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;
查询 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;
-> 查询到的字段字符串类型还会有个双引号，还需要做一层处理 可以使用 ->> 去除，且转义符也会去除 select json_obj->>'$.name' `name`, json_obj->>'$.tags[0]' `tags0`, json_arr->>'$[0]' xx from t_json_tbl;
可以使用内置函数进行查询 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 '$.num' = 555555;执行计划（数值型的使用->可以生效，但是字符串型的需要用生成列作为索引条件才能生效） explain select * from t_json_tbl2 where gen_col = 555555;
使用 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 selectBy(Long id); JsonTbl selectLike(String name); }xml 配置文件 id, json_obj, json_arr, json_str select * from t_json_tbl where `id`=#{id} select * from t_json_tbl where `json_obj`->'$.name'=#{name} 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 |
【本文地址】
今日新闻 |
推荐新闻 |