MySQL 中提取 JSON 字段数据的方法

MySQL 中提取 JSON 字段数据的方法

前言

由于保存用户上传数据时，有一部分的字段为固定的，但不同用户对应的具体数据部分字段不固定，因此使用 MySQL 的 json 类型保存不固定的部分，将不固定的这部分字段统一放入 value 字段下，value 类型设为 json。
但在使用 MySQL 的MAX函数统计 value 下的字段时，发现统计结果异常，因此发现在 MySQL 中不同提取 JSON 字段数据的方法结果的不同。

提取 JSON 字段数据的常用方法

1. 使用->和->>操作符：

   • ->操作符用于提取 JSON 字段中的 JSON 值，可以通过键路径访问嵌套的属性：json_column->'$.key'
   • ->>操作符用于提取 JSON 字段中的字符串值，类似于->，但返回的是文本而不是 JSON：json_column->>'$.key'

2. 使用JSON_VALUE()和JSON_EXTRACT()函数：

   • JSON_VALUE()函数用于提取 JSON 字段中指定键的值作为字符串。可以通过键路径访问嵌套的属性：JSON_VALUE(json_column, '$.key')
   • JSON_EXTRACT()函数用于提取 JSON 字段中指定键的值作为 JSON。可以使用$符号指定键路径：JSON_EXTRACT(json_column, '$.key')

示例

表机构：

CREATE TABLE `json_test`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `value` json NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

数据：

INSERT INTO `json_test` VALUES (1, 'server_1', '{\"ip\": \"192.168.1.10\", \"ssh\": {\"port\": 22, \"password\": \"1234\", \"username\": \"test\"}, \"total_disk\": 100}');
INSERT INTO `json_test` VALUES (2, 'server_2', '{\"ip\": \"192.168.1.11\", \"ssh\": {\"port\": 22, \"password\": \"1234\", \"username\": \"test\"}, \"total_disk\": 100}');
INSERT INTO `json_test` VALUES (3, 'server_3', '{\"ip\": \"192.168.1.12\", \"ssh\": {\"port\": 22, \"password\": \"1234\", \"username\": \"test\"}, \"total_disk\": 100}');

1. ->和JSON_EXTRACT取出的结果类似，都是 json 数据，如果值是字符串会带上""

   • SELECT value->'$.ip' FROM json_test

     "192.168.1.10"
     "192.168.1.11"
     "192.168.1.12"

   • SELECT JSON_EXTRACT(value, '$.total_disk') FROM json_test

     100
     1000
     500

2. ->>和JSON_VALUE取出的结果类似，都是字符串，但不会有""

   • SELECT value->>'$.ip' FROM json_test

     192.168.1.10
     192.168.1.11
     192.168.1.12

   • SELECT JSON_VALUE(value, '$.total_disk') FROM json_test

     100
     1000
     500

3. 嵌套访问：SELECT value->'$.ssh.username' FROM json_test

   "test"
   "test"
   "test"

4. 也可以使用JSON_UNQUOTE去掉""：SELECT JSON_UNQUOTE(JSON_EXTRACT(value, '$.ssh.username')) FROM json_test

   test
   test
   test

注意问题

当使用->>和JSON_VALUE时，由于返回的是字符串，在使用 MySQL 的函数时，可能会出现问题，比如：SELECT MAX(JSON_VALUE(value, '$.total_disk')) FROM json_test，返回结果为 500，使用SELECT MAX(JSON_EXTRACT(value, '$.total_disk')) FROM json_test时，返回正确结果 1000

性能考虑和最佳实践

• 考虑性能方面的问题，尽量避免在查询中频繁使用 JSON 字段的提取操作。
• 当需要大量处理 JSON 数据时，考虑使用 MySQL 8.0+ 版本提供的 JSON_TABLE() 函数，将 JSON 数据解析为关系型数据。

注意事项和限制

• 确保 JSON 字段的格式和路径的正确性，避免语法错误导致的查询问题。
• 注意不同 MySQL 版本对于 JSON 函数和操作符的支持程度，避免使用不受支持的功能。