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

目录
  • 背景
  • 问题分析
    • 1、属性值是 Json 格式的,需要使用 Json 操作函数处理
    • 2、字段内容不规范,乱七八糟
    • 3.又要抽取内容、又要格式化,记录还有 900w+,太慢了
  • 最后执行结果比较
    • 数据导入比较
  • 总结

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

没有 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、字段内容不规范,乱七八糟

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

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

  • 数字 + 备注:1(配合欠佳)、1-\+(我猜这是想表示偏高或偏低)
  • 数字 + 单位:跟上面相似,1mm
  • 多数值或区间:22.52/42.45、1-5
  • 纯文本描述:不配合、无法记录
  • 文本、数字混杂描述:较上次增长 10、<1、小于1、BD234/KD23

没办法,找产品和业务对情况,好在不多,就 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转储为数字内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • mysql(5.6及以下)解析json的方法实例详解

    mysql(5.6及以下)解析json #json解析函数 DELIMITER $$ DROP FUNCTION IF EXISTS `json_extract_c`$$ CREATE FUNCTION `json_extract_c`( details TEXT, required_field VARCHAR (255) ) RETURNS TEXT CHARSET latin1 BEGIN SET details = SUBSTRING_INDEX(details, "{", -1

  • MySQL5.7中的JSON基本操作指南

    前言 因为项目需要,存储字段存储成了JSON格式,在项目中是将查询出来的值通过jackson转成相应的bean进行处理的,觉得不够简单方便. MySQL从5.7版本开始就支持JSON格式的数据,操作用起来挺方便的. 建表 在新建表时字段类型可以直接设置为json类型,比如我们创建一张表: mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `info`

  • MySQL5.7 JSON类型使用详解

    JSON是一种轻量级的数据交换格式,采用了独立于语言的文本格式,类似XML,但是比XML简单,易读并且易编写.对机器来说易于解析和生成,并且会减少网络带宽的传输. JSON的格式非常简单:名称/键值.之前MySQL版本里面要实现这样的存储,要么用VARCHAR要么用TEXT大文本. MySQL5.7发布后,专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析. 我们先看看MySQL老版本的JSON存取. 示例表结构: CREATE TABLE json_test( id INT, p

  • mysql查询字段类型为json时的两种查询方式

    表结构如下: id varchar(32) info json 数据: id = 1 info = {"age": "18","disname":"小明"} -------------------------------------------- 现在我需要获取info中disanme的值,查询方法有: 1. select t.id,JSON_EXTRACT(t.info,'$.disname') as disname fro

  • MySQL操作之JSON数据类型操作详解

    上一篇文章我们介绍了mysql数据存储过程参数实例详解,今天我们看看MySQL操作之JSON数据类型的相关内容. 概述 mysql自5.7.8版本开始,就支持了json结构的数据存储和查询,这表明了mysql也在不断的学习和增加nosql数据库的有点.但mysql毕竟是关系型数据库,在处理json这种非结构化的数据时,还是比较别扭的. 创建一个JSON字段的表 首先先创建一个表,这个表包含一个json格式的字段: CREATE TABLE table_name ( id INT NOT NULL

  • 详解Mysql中的JSON系列操作函数

    前言 JSON是一种轻量级的数据交换格式,采用了独立于语言的文本格式,类似XML,但是比XML简单,易读并且易编写.对机器来说易于解析和生成,并且会减少网络带宽的传输. JSON的格式非常简单:名称/键值.之前MySQL版本里面要实现这样的存储,要么用VARCHAR要么用TEXT大文本. MySQL5.7发布后,专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析. 下面一起来实际操作一下. 创建带有 JSON 字段的表 比如一个'文章'表,字段包括 id.标题 title.标签 t

  • mysql5.6及以下版本如何查询数据库里的json

    MySQL里面保存数据有时候会把一些杂乱且不常用的时候丢进一个json字段里面 下面说说mysql存储json注意那些格式吧: 1:注意保存是中文不要变成转码的,转码之后导致查询非常麻烦,压缩时候后面多带一个参数,方便不止一点点哟! json_encode(array(),JSON_UNESCAPED_UNICODE); 好处:这样查询的时候中文字符更好的匹配查询 2:字段统一    存的时候最好开始定好字段名称  ,开发一个大点的项目不可能一个人开发统一字段可以减少很多不需要的麻烦和字段不同意

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

    目录 背景 问题分析 1.属性值是 Json 格式的,需要使用 Json 操作函数处理 2.字段内容不规范,乱七八糟 3.又要抽取内容.又要格式化,记录还有 900w+,太慢了 最后执行结果比较 数据导入比较 总结 这只是一次简单数据迁移的统计,数据量不大,麻烦的是一些中间步骤处理和思量. 没有 SQL 优化.索引优化的内容,大家轻喷. 背景 用户眼科属性表记录数大概 986w,目的是把大概 29w 记录的属性值(json 格式)的其中八个字段解析为数字,转储为统计表的记录,用于图表分析. 以下

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

    目录 前言 1. 问题现象 2. 解决方案 3. JSON数据查询 3.1 一般基础查询操作 3.2 一般函数查询操作 4. JSON数据新增更新删除 前言 昨天上线后通过系统报警发现了一个bug,于是紧急进行了回滚操作,但是期间有用户下单,数据产生了影响,因此需要排查影响了哪些订单,并对数据进行修复. 1. 问题现象 由于bug导致了订单表的customer_extra_info字段的hasFreightInsurance误更新成了“是”,因此需要查询回滚前一共有多少被误更新为“是”的订单,如

  • mysql中json类型字段的基本用法实例

    目录 前言 基本环境 JSON类型字段常用操作 插入JSON类型数据 查询JSON类型数据 更新JSON类型数据中的特定字段 匹配JSON类型数据中的特定字段 结语 前言 mysql从5.7.8版本开始原生支持了JSON类型数据,同时可以对JSON类型字段中的特定的值进行查询和更新等操作,通过增加JSON类型的属性可以大大的提高我们在mysql表中存储的数据的拓展性,无需每次新增字段时都进行表结构的调整,下面我们不深入讲解底层的实现原理,我们主要来梳理一下我们在日常工作中使用实践 基本环境 my

  • MySQL之JSON类型字段的使用技巧分享

    目录 准备工作 JSON对象基础操作 JSON数组操作 更多操作 JSON字段在JAVA中的实践 测试环境: MySQL8.0.19 准备工作 CREATE TABLE json_demo ( `id` INT ( 11 ) NOT NULL PRIMARY KEY, `content` json NOT NULL ); INSERT INTO json_demo ( id, content ) VALUES /*这条是数组*/ ( 1, '[{"key": 1, "orde

  • Mysql json类型字段Java+Mybatis数据字典功能的实践方式

    目录 前言 应用场景介绍 数据字典案例实践 表结构 实体类 关系映射 泛型擦除问题解决(7.21新增) 效果展示 总结 前言 JSON类型是MySQL5.7.8中新加入的一种数据类型,并在后续版本尤其是MySQL8.0中得到了大幅增强,现在的JSON类型的功能十分强大,合理使用能让我们的开发更加有效! 但本文不准备花篇幅来介绍MySQL的JSON类型字段的相关API,因为官方文档里面写得已经十分详细了,大家如果对MySQL的JSON类型还不怎么了解的话可以看看官方文档: MySQL5.7 JSO

  • MySQL中JSON字段数据类型详解

    目录 前言 创建JSON值 搜索JSON类型数据 在 JSON 和非 JSON 值之间转换 JSON 值的汇总 总结 前言 JSON 类型是从 MySQL 5.7 版本开始支持的功能,MySQL 支持由RFC 7159定义的本机JSON数据类型,该类型可有效访问 JSON(JavaScript 对象 table 示法)文档中的数据.与将 JSON 格式的字符串存储在字符串列中相比,JSON数据类型具有以下优点: 自动验证存储在JSON列中的 JSON 文档.无效的文档会产生错误. 优化的存储格式

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

    目录 引言 第一步:一行拆分成多行 1.1 新建一张表keyid,只insert从0开始的数字,如下: 1.2 找到拆分标识符 1.3 通过join on拆分多行 第二步:解析json字符串 总结 引言 在开发过程中,遇到过json数据组的字符串,需要解析json组,得到组内所有的信息.如下格式: [{"itemId":3101,"itemName":"空滤器及进气管道"},{"itemId":3102,"itemN

  • MySQL中json字段的操作方法

    MySQL5.7.8中引入了json字段,这种类型的字段使用的频率比较低,但是在实际操作中,有些业务仍然在用,我们以此为例,介绍下json字段的操作方法: 还是从例子看起: mysql> create table test1(id int,info json); Query OK, 0 rows affected (0.02 sec) mysql> insert into test1 values (1,'{"name":"yeyz","age

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

    最近用到了mysql5.7的json字段的检索查询,发现挺好用的,记录一下笔记我们有一个日志表,里面的data字段是保存不同对象的json数据,遇到想根据里面的json的字段内容作为条件查询的情况 mysql根据json字段的内容检索查询数据 使用 字段->'$.json属性'进行查询条件 使用json_extract函数查询,json_extract(字段,"$.json属性") 根据json数组查询,用JSON_CONTAINS(字段,JSON_OBJECT('json属性'

  • Mybatis-Plus读写Mysql的Json字段的操作代码

    目录 前置条件 一.新建mysql表增加json字段 二.pojo类 三.测试类 前置条件 确保mysql的版本是5.7+ 一.新建mysql表增加json字段 二.pojo类 package com.cxstar.domain; import com.alibaba.fastjson.JSONObject; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.

随机推荐