MySQL JSON数据类型

在日常开发中，我们经常会在 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 数据通常比查询结构化的表格数据更复杂。