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

吾爱主题阅读：142 2024-04-02 08:04:08 评论：0

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

没有 SQL 优化、索引优化的内容，大家轻喷。

背景

用户眼科属性表记录数大概 986w，目的是把大概 29w 记录的属性值（json 格式）的其中八个字段解析为数字，转储为统计表的记录，用于图表分析。

以下结构、数据都大部分我瞎诌的，不可当真

用户眼科属性表结构如下

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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 个字段值，提取出来分门别类归为不同统计指标下。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 { ...... "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 多条，大致扫一下心里有数。得出以下几条解决方案：

数字开头：数字开头都是正确记录的数据，省略掉文字描述即可
多数值或区间：取最前面的数即可
纯文本：说明没有数据，排除掉
文本、数字混杂：具体问题具体分析，把其他处理掉之后看还有多少

具体怎么做呢？

第一步：排除正常的数字数据和空数据

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

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

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

第三步：提取数字开头的数据的首个数值

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

结合起来就是

1 2 3 4 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：

1 2 3 4 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 数据加索引，用完需要删除索引（因为不是业务情况需要），就会导致两次波动；

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

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

1 2 3 4 5 6 7 8 9 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) 。

1 2 3 4 5 6 7 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 里面同样的提取和格式化操作就要多次执行了。

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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

1 2 3 4 5 6 7 8 9 10 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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 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` != '' );