Mysql中Json数据类型

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 对象的键