MySQL JSON数据类型

发布时间:2024年01月22日

在日常开发中,我们经常会在 MySQL 中使用 JSON 字段,比如很多表中都有 extra 字段,用来记录一些特殊字段,通过这种方式不需要更改表结构,使用相对灵活。
目前对于 JSON 字段的实践各个项目不尽相同,MySQL 表结构中使用的字段类型一般为 text、varchar 或者 json。

基础知识

MySQL 在 5.7.8 版本后,开始支持 JSON 数据类型(JSON 协议)。

合法性校验

JSON 数据类型会对字段进行合法性校验,比如插入或者更新的值不合法,会操作失败。

CREATE TABLE `json_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `extra` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- invalid json
insert into json_test values(1, '{"age":x}');
update json_test set extra = '{"age":x}' where id = 1;

在这里插入图片描述

底层存储

JSON 数据类型优化了底层存储(二进制存储),让用户能够更快地访问 JSON 中的元素。相比于存储 JSON 的字符串数据类型(VARCHAR、TEXT),JSON 底层的二进制存储不需要从文本进行 JSON 解析。
JSON 数据类型能够存储的空间和 LONGTEXT 和 LONGBLOB 类似,最多能存储 4G。

工具函数

函数功能示例
JSON_VALID检查输入的字符串是否为一个合法的 JSON 文本,如是返回 1,否则返回 0。SELECT JSON_VALID(‘{“id”: 1, “name”: “John”}’); – 返回值为 1 SELECT JSON_VALID(‘{“id”: 1, “name”: “John”, }’);-- 返回值为 0
JSON_EXTRACT从 JSON 文本中提取指定的数据。SELECT JSON_EXTRACT(‘{“id”: 1, “name”: “John”}’, ‘$.name’);-- 返回值为 “John”
JSON_SET在一个给定的 JSON 文本中设置或覆盖一个给定的路径的值,并返回新的 JSON 文本。SELECT JSON_SET(‘{“id”: 1, “name”: “John”}’, ‘$.name’, ‘Mike’);-- 返回值为 ‘{“id”: 1, “name”: “Mike”}’
JSON_REMOVE从一个给定的 JSON 文本中删除指定的路径的值,并返回新的 JSON 文本。SELECT JSON_REMOVE(‘{“id”: 1, “name”: “John”}’, ‘$.name’);-- 返回值为 ‘{“id”: 1}’
JSON_ARRAY创建一个包含指定值的 JSONArray。SELECT JSON_ARRAY(1, “John”, true);-- 返回值为 ‘[1, “John”, true]’
JSON_OBJECT创建一个包含指定键值对的 JSONObject。SELECT JSON_OBJECT(‘id’, 1, ‘name’, ‘John’, ‘is_active’, true);-- 返回值为 ‘{“id”: 1, “name”: “John”, “is_active”: true}’

你可以使用 INSERT 语句插入 JSON 数据:

INSERT INTO json_test (extra) VALUES ('{"customer": "John Doe", "items": {"product": "apple", "quantity": 1}}');

你可以使用 -> 操作符从 JSON 字段中查询数据:

SELECT extra->'$.customer' AS customer FROM orders;
在这个例子中,查询返回 extra 字段中的 customer 属性。

你也可以使用 JSON_SET 函数更新 JSON 字段的值:

UPDATE json_test SET extra = JSON_SET(extra, '$.customer', 'Jane Doe') WHERE id = 1;
在这个例子中,更新 extra 字段中的 customer 属性的值为 'Jane Doe'

以上就是在 MySQL 中使用 JSON 数据类型的一些基本方法。具体使用时,可能需要根据你的实际需求进行调整。

注意事项

  • 默认值:JSON 不能有非 NULL 默认值(和 TEXT 类似)。

  • 索引 JSON 列不能直接索引,如果要建索引,可以参考
    https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html#json-column-indirect-index,但一般来说,如果要在 JSON 字段中建索引,不如考虑将字段提取为单独一列。

  • 数据完整性:与使用结构化的表格数据相比,使用 JSON 数据类型可能更难保证数据的完整性。例如,你不能在 JSON 字段中定义外键约束。

  • 查询复杂性:查询 JSON 数据通常比查询结构化的表格数据更复杂。

文章来源:https://blog.csdn.net/t949500898/article/details/135746885
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。