mysql保存json串用blob好不好 mysql保存json字符串

转载

gulaotou 2023-07-27 19:04:03

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