Mysql中json格式数据查询、修改

最新推荐文章于 2025-05-06 13:44:26 发布

原创最新推荐文章于 2025-05-06 13:44:26 发布 · 4.8k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #经验分享 #数据库

sql 专栏收录该内容

4 篇文章

订阅专栏

博客分享了MySQL相关经验，包含测试数据、查询操作，还给出结果截图示例，同时涉及修改/更新操作及对应结果截图示例，聚焦于数据库操作实践。

测试数据：

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `json_data` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('10001', '张三', '{\"age\": \"22\", \"name\": \"张三\", \"code\": \"10001\", \"time\": \"2018-12-2--2020-12\", \"school\": \"北京大学\", \"status\": \"01\",  \"edu_backgroud\": \"本科\"}');
INSERT INTO `student` VALUES ('10002', '李四', '{\"age\": \"18\", \"name\": \"李四\", \"code\": \"10002\", \"time\": \"2018-12-2--2021-12\", \"school\": \"北京中学\", \"status\": \"01\",  \"edu_backgroud\": \"高中\"}');
INSERT INTO `student` VALUES ('10003', '王麻子', '{\"age\": \"8\", \"name\": \"王麻子\", \"code\": \"10003\", \"time\": \"2000-12-2--2007-12\", \"school\": \"北京小学\", \"status\": \"01\",  \"edu_backgroud\": \"小学\"}');
INSERT INTO `student` VALUES ('10004', '李白', '{\"age\": \"4\", \"name\": \"李白\", \"code\": \"10004\", \"time\": \"2018-12-2--2020-12\", \"school\": \"北京幼儿园\", \"status\": \"01\",  \"edu_backgroud\": \"幼儿园\"}');

SET FOREIGN_KEY_CHECKS = 1;

查询：

SELECT
	id,
	name,
	json_data,
	json_extract(json_data,"$.name"),
	REPLACE(json_extract(json_data,"$.name"),'"',''),
	json_extract(json_data,"$.age"),
	json_extract(json_data,"$.school"),
	json_extract(json_data,"$.school")
FROM
	`student` 


说明：此sql中json_extract函数用来处理json格式数据
      json_data是存接送格式数据字段
      $.age是引用json_data字段中json字段且直接引用的字段查询结果会带引号，可通过REPLACE函数替换。

结果截图示例：

修改/更新：

修改json中一个值：
UPDATE `student` 
SET json_data = json_set( json_data, '$.age', '88' ) 
WHERE
	NAME = '张三'

修改json中多个值：
UPDATE `student` 
SET json_data = json_set( json_data, '$.age', '88','$.school', '清华大学' ) 
WHERE
	NAME = '张三'


说明：sql中json_set是Mysql用来更新数据，
      json_data是存json数据的字段
      $.age'、$.school是json中的字段名

结果截图示例：