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

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

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='属性';

{   ......
  "sight": {
    "nakedEye": {
      "left": "0.9",
      "right": "0.6"
    },
    "correction": {
      "left": "1",
      "right": "1"
    }
  },
  ......
  "axialLength": {
    "left": "21",
    "right": "12"
  },
  "korneaRadius": {
    "left": "34",
    "right": "33"
  },
  ......
}

   

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

   

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

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

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` != ''

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

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='属性';

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='记录';

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;

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'),'"','');

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` != '');