MySQL 提取 Json 内部字段转储为数字
这只是一次简单数据迁移的统计,数据量不大,麻烦的是一些中间步骤处理和思量。
没有 SQL 优化、索引优化的内容,大家轻喷。
背景
用户眼科属性表记录数大概 986w,目的是把大概 29w 记录的属性值(json 格式)的其中八个字段解析为数字,转储为统计表的记录,用于图表分析。
以下结构、数据都大部分我瞎诌的,不可当真
用户眼科属性表结构如下
CREATE TABLE `property` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ownerId` int(11) NOT NULL COMMENT '记录ID或者模板ID',
`ownerType` tinyint(4) NOT NULL COMMENT '类型。0:记录 1:模板',
`recorderId` bigint(20) NOT NULL DEFAULT '0' COMMENT '记录者ID',
`userId` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户ID',
`roleId` bigint(20) NOT NULL DEFAULT '0' COMMENT '角色ID',
`type` tinyint(4) NOT NULL COMMENT '字段类型。0:文本 1:备选项 2:时间 3:图片 4:ICD10 9:新图片',
`name` varchar(128) NOT NULL DEFAULT '' COMMENT '字段名称',
`value` mediumtext NOT NULL COMMENT '字段值',
PRIMARY KEY (`id`),
UNIQUE KEY `idxOwnerIdOwnerTypeNameType` (`ownerType`,`ownerId`,`name`,`type`) USING BTREE,
KEY `idxUserIdRoleIdRecorderIdName` (`userId`,`roleId`,`recorderId`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='属性';
复制代码
问题分析
1、属性值是 Json 格式的,需要使用 Json 操作函数处理
因为属性值是 Json 格式的,如下。较大的一个 Json,但是只需要其中 8 个字段值,提取出来分门别类归为不同统计指标下。
{
......
"sight": {
"nakedEye": {
"left": "0.9",
"right": "0.6"
},
"correction": {
"left": "1",
"right": "1"
}
},
......
"axialLength": {
"left": "21",
"right": "12"
},
"korneaRadius": {
"left": "34",
"right": "33"
},
......
}
复制代码
所以,需要用到 Json 操作函数:json_extract(value,’ . k e y 1. k e y 2 ′ ) 。 但 是 需 要 注 意