目錄背景問題分析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轉儲為數字內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！