测试数据:
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中的字段名
结果截图示例: