MySQL 中提取 JSON 字段数据的方法
MySQL 中提取 JSON 字段数据的方法
前言
由于保存用户上传数据时,有一部分的字段为固定的,但不同用户对应的具体数据部分字段不固定,因此使用 MySQL 的 json 类型保存不固定的部分,将不固定的这部分字段统一放入 value 字段下,value 类型设为 json。
但在使用 MySQL 的MAX
函数统计 value 下的字段时,发现统计结果异常,因此发现在 MySQL 中不同提取 JSON 字段数据的方法结果的不同。
提取 JSON 字段数据的常用方法
-
使用
->
和->>
操作符:->
操作符用于提取 JSON 字段中的 JSON 值,可以通过键路径访问嵌套的属性:json_column->'$.key'
->>
操作符用于提取 JSON 字段中的字符串值,类似于->
,但返回的是文本而不是 JSON:json_column->>'$.key'
-
使用
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}');
-
->
和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
-
-
->>
和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
-
-
嵌套访问:
SELECT value->'$.ssh.username' FROM json_test
"test" "test" "test" -
也可以使用
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 函数和操作符的支持程度,避免使用不受支持的功能。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· 上周热点回顾(2.24-3.2)