JSON数据类型
MySQL中的JSON类型是MySQL 5.7版本中引入的一种数据类型,用于存储和操作JSON数据
存储方式
MySQL中的JSON类型以文本方式存储
- varchar、text、json类型字段的区别
存储方面:
- VARCHAR是一种可变长度的字符串类型,适合存储较短的字符串。它的最大长度限制为65535个字符。VARCHAR类型使用动态方式存储数据,仅占用实际内容所需的空间,加上额外的1-2个字节来记录字符串的长度。
- TEXT类型是长文本数据类型,专门用于存储大量非结构化文本数据,最大可以存储65535个字符,数据存储在外存中。
- JSON:可以存储4GB的JSON文档, JSON类型提供了对JSON数据的原生支持,包括自动校验文档的正确性。此外,JSON类型允许对JSON数据执行特定的查询和操作
性能方面:
- VARCHAR由于其存储效率和直接包含在表中的特性,对于较小的字符串数据访问速度较快。
- TEXT类型由于数据存储在外存,当频繁访问时可能会有性能影响,尤其是当涉及到大量的磁盘I/O操作时。
- JSON类型虽然增加了数据校验和特定查询操作的优势,但这些操作可能会对性能产生一定的影响,特别是在处理大型JSON文档时。
JSON 类型比较适合存储一些列不固定、修改较少、相对静态的数据
索引支持
MySQL 8.0版本中增加了对JSON类型的索引支持。可以使用CREATE INDEX语句创建JSON类型的索引,提高JSON类型数据的查询效率。
语法
JSON类型的插入
- 字符串直接插入
-- 插入数组
insert into users(json_data) values('[1, "abc", null, true, "08:45:06.000000"]');
-- 插入对象
insert into users(json_data) values('{"id": 87, "name": "carrot"}');
-- 插入嵌套json
insert into users(json_data) values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
json格式的字段,插入时会自动校验格式,如果格式不是json的,会报错
insert into users(json_data) values('{"id", "name": "carrot"}');
> 3140 - Invalid JSON text: "Missing a colon after a name of object member." at position 5 in value for column 'users.json_data'.
- JSON_OBJECT()函数插入对象
对于 JSON 文档,KEY 名不能重复。
如果插入的值中存在重复 KEY,在 MySQL 8.0.3 之前,遵循 first duplicate key wins 原则,会保留第一个 KEY,后面的将被丢弃掉。
从 MySQL 8.0.3 开始,遵循的是 last duplicate key wins 原则,只会保留最后一个 KEY。
-- 格式:
JSON_OBJECT([key, val[, key, val] ...])
-- 创建对象,一个key对应一个value : {"id": 87, "name": "carrot"}
insert into users(json_data) values(json_object('id', 87, 'name', 'carrot'));
- JSON_ARRAY()函数插入数组
-- 格式:
JSON_ARRAY([val[, val] ...])
-- 使用JSON_ARRAY()函数创建数组 : [1, "abc", null, true, "08:09:38.000000"]
insert into users(json_data) values(JSON_ARRAY(1, "abc", null, true,curtime()));
Json查询
指定属性语法
- 函数查询:json_extract(json字段, ‘$.json属性’)
- 对象操作方法进行查询:json字段->‘$.json属性’
select JSON_EXTRACT(json_data,'$.name') from users
select json_data->'$.name' from users
需要注意: ->和JSON_EXTRACT查询到的字段字符串还会有个双引号
可以使用**->>和JSON_UNQUOTE**去除双引号和转义字符
- 模糊查询JsonArray中字段:
where column->'$[*].key' like '%value%'
- 精确查询JsonArray类型字段
where JSON_CONTAINS(column,JSON_OBJECT('key', "value"))
- 多层级关系,模糊查询所有的
where column->'$**.key' like '%value%'
其他常用函数
MySQL 常用的JSON函数列表:
- JSON_OBJECT(key1, value1, key2, value2, …):创建一个JSON对象。
- JSON_ARRAY(value1, value2, …):创建一个JSON数组。
- JSON_EXTRACT(json_expr, path):从JSON字符串中提取数据。
- JSON_CONTAINS(json_doc, val[, path]):判断JSON字符串中是否包含某个值。
- JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path]…):判断JSON字符串中是否包含指定路径的值。
- JSON_INSERT(json_doc, path, val[, path, val]…):向JSON字符串中插入数据。
- JSON_REPLACE(json_doc, path, val[, path, val]…):替换JSON字符串中指定路径的值。
- JSON_SET(json_doc, path, val[, path, val]…):设置JSON字符串中指定路径的值, 若路径不存在则创建。
- JSON_REMOVE(json_doc, path[, path]…):从JSON字符串中删除指定路径的值。
- JSON_ARRAY_APPEND(json_doc, path, value[, path,value]…):向JSON数组中添加元素。
- JSON_ARRAY_INSERT(json_doc, path, value[, path,value]…):向JSON数组中插入元素。
- JSON_ARRAY_REMOVE(json_doc, path[, path]…):从JSON数组中删除元素。
- JSON_LENGTH(json_doc[, path]):返回JSON字符串或数组的长度。
- JSON_SET(‘{“key1”: “value1”}’, ‘$.key2’, ‘value2’) 更新 JSON 文档中的数据,若路径不存在则创建
辅助函数
- JSON_TYPE返回 JSON 值的数据类型。
- JSON_LENGTH返回 JSON 数组或对象的长度
- JSON_KEYS返回 JSON 对象的键