文章详情

登录

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

选择考试省份

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL提取Json内部字段转储为数字

2022-05-20 20:41

关注
目录

这只是一次简单数据迁移的统计，数据量不大，麻烦的是一些中间步骤处理和思量。

没有 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,'$.key1.key2')。

但是需要注意的是这个函数提取的值是带""。比如对上述记录执行json_extract(value,'$.sight.nakedEye.left')的结果是"22"；也可能字段值是空字符串，那结果就是""。

所以，需要使用 replace函数把结果中的 "" 删除掉，最后提取字段的表达式就是：replace(json_extract(value,'$.sight.nakedEye.left'),'"','')。

如果字段不存在的话，结果就是 NULL；无论是外层 sight 不存在，或是内层 left 不存在。

2、字段内容不规范，乱七八糟

理想下，填写的都是规范数字，那经过上面那一步就可以提取完直接导入新表。

但是，现实很残酷，填的东西那叫一个乱七八糟。比如：

没办法，找产品和业务对情况，好在不多，就 4000 多条，大致扫一下心里有数。得出以下几条解决方案：

具体怎么做呢？

排除正常的数字数据和空数据


WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 这个已经可以排除 null 了
 AND `nakedEyeLeft` != ''

如果不包含数字，将其设置 NULL 或空字符串


SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]', '', nakedEyeLeft)

提取数字开头的数据的首个数值


SET nakedEyeLeft = IF((nakedEyeLeft + 0 = 0), nakedEyeLeft, nakedEyeLeft + 0)

结合起来就是


SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]''', '', 
                      IF((nakedEyeLeft + 0 = 0), nakedEyeLeft, nakedEyeLeft + 0))
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 这个已经可以排除 null 了
 AND `nakedEyeLeft` != ''

PS：处理一个字段的SQL 看着就简单，但是因为批量一次处理 8 个字段，组合起来就很长。

千万注意不要写错字段。

最后剩下的就是第四类：文本、数字混杂，40 多条。

有些看着简单的，可以用正则自动化处理，比如<1、小于1。

记录的增长值，需要查找上次记录进行计算：较上次增长 10。

剩下有点复杂的，就需要人为处理，提取出可用数据，比如BD234/KD23

不知道看到这里的各位是不是也觉得有些麻烦呢？

我也以为咬着牙搞了，结果业务说直接处理成 0，到时候发现是 0 的话，可以通过页面重新保存的。

就不需要判断是不是数字打头了，直接 + 0；如果是数字打头，会保留开头的数字；否则 = 0。

那最后数据格式化SQL：


UPDATE property 
SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]''', '', nakedEyeLeft + 0)
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 这个已经可以排除 null 了
 AND `nakedEyeLeft` != '';

3.又要抽取内容、又要格式化，记录还有 900w+，太慢了

property 表有 900w+ 的数据，而所需记录的条件，只有name、ownerType、type是可知的，没法命中现有的索引。

如果直接查找的话，直接就是全表扫描，外加数据提取和格式化；更何况还需要关联其他表，补充统计指标的一些其他字段。

这种情况下，直接导入统计表的话，结果就是把两张表+关联表一起锁较长时间，期间没法更改和插入，这样不大现实。

减少扫描行数

做法一：给 name、ownerType、type 加上索引，将扫描记录缩减到 20 w。

但是问题是900w 数据加索引，用完需要删除索引（因为不是业务情况需要），就会导致两次波动；

再加上后续处理锁表时长，问题还是很大。

做法二：将一个记录较少的表做驱动表，这个表可以关联目标表。


CREATE TABLE `property` (
  `ownerId` int(11) NOT NULL COMMENT '记录ID或者模板ID',
  `ownerType` tinyint(4) NOT NULL COMMENT '类型。0：记录 1：模板',
  `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 '字段值',
    省略其他字段
  UNIQUE KEY `idxOwnerIdOwnerTypeNameType` (`ownerType`,`ownerId`,`name`,`type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='属性';

表中ownerId 可以关联到记录表，加上之前的条件name、ownerType、type，如此刚好命中 并``idxOwnerIdOwnerTypeNameType (ownerType,ownerId,name,type) 。


CREATE TABLE `medicalrecord` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '记录名称',
  `type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '记录类型。',
    省略其他字段
  KEY `idxName` (`name`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='记录';

记录表可以通过 name='眼科记录'命中索引idxName，扫描行数只有2w，加上属性表 29w，最后扫描行数只有 30w 左右，比之全表扫描属性表少了 30 倍！！！。

避免数据提取和格式化的锁表时长

因为存在 8 个字段，每个字段都需要提取和格式化，中间还需要进行判断。这样子一个 SQL 里面同样的提取和格式化操作就要多次执行了。

所以，为了避免这样的问题，需要中间表暂存提取和格式化结果。


CREATE TABLE `propertytmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   `value` mediumtext NOT NULL COMMENT '字段值',
  `nakedEyeLeft` varchar(255) DEFAULT NULL COMMENT '视力-裸眼-左眼',
  `nakedEyeRight` varchar(255) DEFAULT NULL COMMENT '视力-裸眼-右眼',
  `correctionLeft` varchar(255) DEFAULT NULL COMMENT '视力-矫正-左眼',
  `correctionRight` varchar(255) DEFAULT NULL COMMENT '视力-矫正-右眼',
  `axialLengthLeft` varchar(255) DEFAULT NULL COMMENT '眼轴长度-左眼',
  `axialLengthRight` varchar(255) DEFAULT NULL COMMENT '眼轴长度-右眼',
  `korneaRadiusLeft` varchar(255) DEFAULT NULL COMMENT '角膜曲率-左眼',
  `korneaRadiusRight` varchar(255) DEFAULT NULL COMMENT '角膜曲率-右眼',
  `updated` datetime NOT NULL COMMENT '更新时间',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

先将数据导入该表，在此基础上做提取，然后格式化。

最后执行结果比较

数据导入比较

结果：全表扫描属性表导入中间表（40s），属性表新增索引+导入（6s + 3s），关联导入（1.4s）。

因为需要关联其他表，并没有预测的那么理想。

中间表数据提取：7.5s


UPDATE `propertytmp` 
SET nakedEyeLeft = REPLACE(json_extract(value,'$.sight.axialLength.left'),'"',''),
nakedEyeLeft = REPLACE(json_extract(value,'$.sight.nakedEye.left'),'"',''),
nakedEyeRight = REPLACE(json_extract(value,'$.sight.nakedEye.right'),'"',''),
correctionLeft = REPLACE(json_extract(value,'$.sight.correction.left'),'"',''),
correctionRight = REPLACE(json_extract(value,'$.sight.correction.right'),'"',''),
axialLengthLeft = REPLACE(json_extract(value,'$.axialLength.left'),'"',''),
axialLengthRight = REPLACE(json_extract(value,'$.axialLength.right'),'"',''),
korneaRadiusLeft = REPLACE(json_extract(value,'$.korneaRadius.left'),'"',''),
korneaRadiusRight = REPLACE(json_extract(value,'$.korneaRadius.right'),'"','');

中间表数据格式化：2.3s

正则判断比我想象的要快啊


UPDATE propertytmp 
SET nakedEyeLeft = IF(nakedEyeLeft NOT REGEXP '[0-9]' AND nakedEyeLeft != '', '', nakedEyeLeft + 0), 
nakedEyeRight = IF(nakedEyeRight NOT REGEXP '[0-9]' AND nakedEyeRight != '', '', nakedEyeRight + 0), 
correctionLeft = IF(correctionLeft NOT REGEXP '[0-9]' AND correctionLeft != '', '', correctionLeft + 0),
correctionRight = IF(correctionRight NOT REGEXP '[0-9]' AND correctionRight != '', '', correctionRight + 0),
axialLengthLeft = IF(axialLengthLeft NOT REGEXP '[0-9]' AND axialLengthLeft != '', '', axialLengthLeft + 0),
axialLengthRight = IF(axialLengthRight NOT REGEXP '[0-9]' AND axialLengthRight != '', '', axialLengthRight + 0),
korneaRadiusLeft = IF(korneaRadiusLeft NOT REGEXP '[0-9]' AND korneaRadiusLeft != '', '', korneaRadiusLeft + 0),
korneaRadiusRight = IF(korneaRadiusRight NOT REGEXP '[0-9]' AND korneaRadiusRight != '', '', korneaRadiusRight + 0)
WHERE (`nakedEyeLeft` REGEXP '[^0-9.]' = 1
       AND `nakedEyeLeft` != '')
  OR (`nakedEyeRight` REGEXP '[^0-9.]' = 1
      AND `nakedEyeRight` != '')
  OR (`correctionLeft` REGEXP '[^0-9.]' = 1
      AND `correctionLeft` != '')
  OR (`correctionRight` REGEXP '[^0-9.]' = 1
      AND `correctionRight` != '')
  OR (`axialLengthLeft` REGEXP '[^0-9.]' = 1
      AND `axialLengthLeft` != '')
  OR (`axialLengthRight` REGEXP '[^0-9.]' = 1
      AND `axialLengthRight` != '')
  OR (`korneaRadiusLeft` REGEXP '[^0-9.]' = 1
      AND `korneaRadiusLeft` != '')
  OR (`korneaRadiusRight` REGEXP '[^0-9.]' = 1
      AND `korneaRadiusRight` != '');

统计指标中间表

因为实际导入统计指标表时，还需要排除为空数据，以及关联其他表做补充。

为了减少对指标表的影响，又建了指标表的中间表，结构完全一致，ID自增是目标表 + 10000。

将属性中间表的数据导入指标中间表，最后直接 INSERT ... SELECT FROM，就很快了。

当然这步其实有点矫枉过正了，但是为了避免线上的一些波动，还是谨慎一些较好。

总结

这是一次简单的数据迁移经历记录。

没有索引优化、SQL优化的内容，只是觉得大家需要有这种关注性能和对用户影响的考虑。

到此这篇关于MySQL提取Json内部字段转储为数字的文章就介绍到这了,更多相关MySQL提取Json转储为数字内容请搜索自学编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持自学编程网！

阅读原文内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题

  • 2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）

    难度     813人已做
    查看

  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看

  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看

  • 2024年上半年软考高项第一、二批次真题考点汇总（完整版）

    难度     435人已做
    查看

  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机

MySQL提取Json内部字段转储为数字

数据库2022-05-20

mysql怎么取json数组转化为字段

mysql怎么取json数组转化为字段
数据库2024-04-12

mysql将行数据转为json字符串

数据库2023-09-09

如何将 MySQL 表字段类型从 BLOB 转换为 JSON？

数据库2023-10-22

MySQL解析JSON格式字段并取出部分值方式

MySQL解析JSON格式字段并取出部分值方式
数据库2024-08-09

mysql根据json字段内容作为查询条件(包括json数组)检索数据

数据库2024-04-02

MySQL对JSON类型字段数据进行提取和查询的实现

数据库2024-04-02

mysql实现将字符串字段转为数字排序或比大小

数据库2022-06-13

使用mybatisplus接收mysql字段为json类型的数据

数据库2023-08-31

数据库字段之谜：从数据存储到信息提取

数据库字段之谜：从数据存储到信息提取
数据库2024-03-12

Java如何取掉json数据中值为null的属性字段

数据库2024-04-02

使用MySQL从JSON字符串提取数据的方法详解

使用MySQL从JSON字符串提取数据的方法详解
数据库2024-10-22

mysql解析json数据组获取数据组所有字段的方法实例

数据库2022-08-22

mysql相同内容的字段怎么合并为一条数据

数据库2024-04-02

MYSQL： sql中某一个字段内容为用逗号分割的字符串转换成多条数据(适用于部分树机构)

数据库2023-08-17

MySQL查询函数之为表和字段取别名的案例

数据库2024-04-02

mysql 数据库 不同数据类型字段设置长度大小、取值范围 及 存储空间

数据库2023-08-16

【MySQL】 如何在MySQL中获取表中的某个字段为最大值和倒数第二条的整条数据？

数据库2023-10-27
位置：首页-资讯-数据库
咦！没有更多了？去看看其它编程学习网 内容吧
首页课程
资料下载问答资讯