人妖在线一区,国产日韩欧美一区二区综合在线,国产啪精品视频网站免费,欧美内射深插日本少妇

新聞動(dòng)態(tài)

MySQL提取Json內(nèi)部字段轉(zhuǎn)儲(chǔ)為數(shù)字

發(fā)布日期:2022-02-09 16:34 | 文章來(lái)源:CSDN

這只是一次簡(jiǎn)單數(shù)據(jù)遷移的統(tǒng)計(jì),數(shù)據(jù)量不大,麻煩的是一些中間步驟處理和思量。

沒(méi)有 SQL 優(yōu)化、索引優(yōu)化的內(nèi)容,大家輕噴。

背景

用戶眼科屬性表記錄數(shù)大概 986w,目的是把大概 29w 記錄的屬性值(json 格式)的其中八個(gè)字段解析為數(shù)字,轉(zhuǎn)儲(chǔ)為統(tǒng)計(jì)表的記錄,用于圖表分析。

以下結(jié)構(gòu)、數(shù)據(jù)都大部分我瞎謅的,不可當(dāng)真

用戶眼科屬性表結(jié)構(gòu)如下

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 '角色I(xiàn)D',
  `type` tinyint(4) NOT NULL COMMENT '字段類型。0:文本 1:備選項(xiàng) 2:時(shí)間 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='屬性';

問(wèn)題分析

1、屬性值是 Json 格式的,需要使用 Json 操作函數(shù)處理

因?yàn)閷傩灾凳?Json 格式的,如下。較大的一個(gè) Json,但是只需要其中 8 個(gè)字段值,提取出來(lái)分門(mén)別類歸為不同統(tǒng)計(jì)指標(biāo)下。

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

所以,需要用到 Json 操作函數(shù):json_extract(value,'$.key1.key2')。

但是需要注意的是這個(gè)函數(shù)提取的值是帶""。比如對(duì)上述記錄執(zhí)行json_extract(value,'$.sight.nakedEye.left')的結(jié)果是"22";也可能字段值是空字符串,那結(jié)果就是""。

所以,需要使用 replace函數(shù)把結(jié)果中的 "" 刪除掉,最后提取字段的表達(dá)式就是:replace(json_extract(value,'$.sight.nakedEye.left'),'"','')。

如果字段不存在的話,結(jié)果就是 NULL;無(wú)論是外層 sight 不存在,或是內(nèi)層 left 不存在。

2、字段內(nèi)容不規(guī)范,亂七八糟

理想下,填寫(xiě)的都是規(guī)范數(shù)字,那經(jīng)過(guò)上面那一步就可以提取完直接導(dǎo)入新表。

但是,現(xiàn)實(shí)很殘酷,填的東西那叫一個(gè)亂七八糟。比如:

  • 數(shù)字 + 備注:1(配合欠佳)、1-\+(我猜這是想表示偏高或偏低)
  • 數(shù)字 + 單位:跟上面相似,1mm
  • 多數(shù)值或區(qū)間:22.52/42.45、1-5
  • 純文本描述:不配合、無(wú)法記錄
  • 文本、數(shù)字混雜描述:較上次增長(zhǎng) 10、<1、小于1、BD234/KD23

沒(méi)辦法,找產(chǎn)品和業(yè)務(wù)對(duì)情況,好在不多,就 4000 多條,大致掃一下心里有數(shù)。得出以下幾條解決方案:

  • 數(shù)字開(kāi)頭:數(shù)字開(kāi)頭都是正確記錄的數(shù)據(jù),省略掉文字描述即可
  • 多數(shù)值或區(qū)間:取最前面的數(shù)即可
  • 純文本:說(shuō)明沒(méi)有數(shù)據(jù),排除掉
  • 文本、數(shù)字混雜:具體問(wèn)題具體分析,把其他處理掉之后看還有多少

具體怎么做呢?

第一步:排除正常的數(shù)字?jǐn)?shù)據(jù)和空數(shù)據(jù)

WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 這個(gè)已經(jīng)可以排除 null 了
 AND `nakedEyeLeft` != ''

第二步:如果不包含數(shù)字,將其設(shè)置 NULL 或空字符串

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

第三步:提取數(shù)字開(kāi)頭的數(shù)據(jù)的首個(gè)數(shù)值

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

結(jié)合起來(lái)就是

SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]''', '', 
                      IF((nakedEyeLeft + 0 = 0), nakedEyeLeft, nakedEyeLeft + 0))
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 這個(gè)已經(jīng)可以排除 null 了
 AND `nakedEyeLeft` != ''

PS:處理一個(gè)字段的SQL 看著就簡(jiǎn)單,但是因?yàn)榕恳淮翁幚?8 個(gè)字段,組合起來(lái)就很長(zhǎng)。

千萬(wàn)注意不要寫(xiě)錯(cuò)字段。

最后剩下的就是第四類:文本、數(shù)字混雜,40 多條。

有些看著簡(jiǎn)單的,可以用正則自動(dòng)化處理,比如<1、小于1。

記錄的增長(zhǎng)值,需要查找上次記錄進(jìn)行計(jì)算:較上次增長(zhǎng) 10。

剩下有點(diǎn)復(fù)雜的,就需要人為處理,提取出可用數(shù)據(jù),比如BD234/KD23

不知道看到這里的各位是不是也覺(jué)得有些麻煩呢?

我也以為咬著牙搞了,結(jié)果業(yè)務(wù)說(shuō)直接處理成 0,到時(shí)候發(fā)現(xiàn)是 0 的話,可以通過(guò)頁(yè)面重新保存的。

就不需要判斷是不是數(shù)字打頭了,直接 + 0;如果是數(shù)字打頭,會(huì)保留開(kāi)頭的數(shù)字;否則 = 0。

那最后數(shù)據(jù)格式化SQL:

UPDATE property 
SET nakedEyeLeft = IF(nakedEyeLeft NOT regexp '[0-9]''', '', nakedEyeLeft + 0)
WHERE `nakedEyeLeft` REGEXP '[^0-9.]' = 1 // 這個(gè)已經(jīng)可以排除 null 了
 AND `nakedEyeLeft` != '';

3.又要抽取內(nèi)容、又要格式化,記錄還有 900w+,太慢了

property 表有 900w+ 的數(shù)據(jù),而所需記錄的條件,只有name、ownerType、type是可知的,沒(méi)法命中現(xiàn)有的索引。

如果直接查找的話,直接就是全表掃描,外加數(shù)據(jù)提取和格式化;更何況還需要關(guān)聯(lián)其他表,補(bǔ)充統(tǒng)計(jì)指標(biāo)的一些其他字段。

這種情況下,直接導(dǎo)入統(tǒng)計(jì)表的話,結(jié)果就是把兩張表+關(guān)聯(lián)表一起鎖較長(zhǎng)時(shí)間,期間沒(méi)法更改和插入,這樣不大現(xiàn)實(shí)。

減少掃描行數(shù)

做法一:給 name、ownerType、type 加上索引,將掃描記錄縮減到 20 w。

但是問(wèn)題是900w 數(shù)據(jù)加索引,用完需要?jiǎng)h除索引(因?yàn)椴皇菢I(yè)務(wù)情況需要),就會(huì)導(dǎo)致兩次波動(dòng);

再加上后續(xù)處理鎖表時(shí)長(zhǎng),問(wèn)題還是很大。

做法二:將一個(gè)記錄較少的表做驅(qū)動(dòng)表,這個(gè)表可以關(guān)聯(lián)目標(biāo)表。

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:備選項(xiàng) 2:時(shí)間 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 可以關(guān)聯(lián)到記錄表,加上之前的條件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='記錄';

記錄表可以通過(guò) name='眼科記錄'命中索引idxName,掃描行數(shù)只有2w,加上屬性表 29w,最后掃描行數(shù)只有 30w 左右,比之全表掃描屬性表少了 30 倍?。?!。

避免數(shù)據(jù)提取和格式化的鎖表時(shí)長(zhǎng)

因?yàn)榇嬖?8 個(gè)字段,每個(gè)字段都需要提取和格式化,中間還需要進(jìn)行判斷。這樣子一個(gè) SQL 里面同樣的提取和格式化操作就要多次執(zhí)行了。

所以,為了避免這樣的問(wèn)題,需要中間表暫存提取和格式化結(jié)果。

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 '眼軸長(zhǎng)度-左眼',
  `axialLengthRight` varchar(255) DEFAULT NULL COMMENT '眼軸長(zhǎng)度-右眼',
  `korneaRadiusLeft` varchar(255) DEFAULT NULL COMMENT '角膜曲率-左眼',
  `korneaRadiusRight` varchar(255) DEFAULT NULL COMMENT '角膜曲率-右眼',
  `updated` datetime NOT NULL COMMENT '更新時(shí)間',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

先將數(shù)據(jù)導(dǎo)入該表,在此基礎(chǔ)上做提取,然后格式化。

最后執(zhí)行結(jié)果比較

數(shù)據(jù)導(dǎo)入比較

結(jié)果:全表掃描屬性表導(dǎo)入中間表(40s),屬性表新增索引+導(dǎo)入(6s + 3s),關(guān)聯(lián)導(dǎo)入(1.4s)。

因?yàn)樾枰P(guān)聯(lián)其他表,并沒(méi)有預(yù)測(cè)的那么理想。

中間表數(shù)據(jù)提?。?.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'),'"','');

中間表數(shù)據(jù)格式化: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` != '');

統(tǒng)計(jì)指標(biāo)中間表

因?yàn)閷?shí)際導(dǎo)入統(tǒng)計(jì)指標(biāo)表時(shí),還需要排除為空數(shù)據(jù),以及關(guān)聯(lián)其他表做補(bǔ)充。

為了減少對(duì)指標(biāo)表的影響,又建了指標(biāo)表的中間表,結(jié)構(gòu)完全一致,ID自增是目標(biāo)表 + 10000。

將屬性中間表的數(shù)據(jù)導(dǎo)入指標(biāo)中間表,最后直接 INSERT ... SELECT FROM,就很快了。

當(dāng)然這步其實(shí)有點(diǎn)矯枉過(guò)正了,但是為了避免線上的一些波動(dòng),還是謹(jǐn)慎一些較好。

總結(jié)

這是一次簡(jiǎn)單的數(shù)據(jù)遷移經(jīng)歷記錄。

沒(méi)有索引優(yōu)化、SQL優(yōu)化的內(nèi)容,只是覺(jué)得大家需要有這種關(guān)注性能和對(duì)用戶影響的考慮。

到此這篇關(guān)于MySQL提取Json內(nèi)部字段轉(zhuǎn)儲(chǔ)為數(shù)字的文章就介紹到這了,更多相關(guān)MySQL提取Json轉(zhuǎn)儲(chǔ)為數(shù)字內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站!

國(guó)外穩(wěn)定服務(wù)器

版權(quán)聲明:本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái),僅供學(xué)習(xí)參考,不代表本站立場(chǎng),如有內(nèi)容涉嫌侵權(quán),請(qǐng)聯(lián)系alex-e#qq.com處理。

相關(guān)文章

實(shí)時(shí)開(kāi)通

自選配置、實(shí)時(shí)開(kāi)通

免備案

全球線路精選!

全天候客戶服務(wù)

7x24全年不間斷在線

專屬顧問(wèn)服務(wù)

1對(duì)1客戶咨詢顧問(wèn)

在線
客服

在線客服:7*24小時(shí)在線

客服
熱線

400-630-3752
7*24小時(shí)客服服務(wù)熱線

關(guān)注
微信

關(guān)注官方微信
頂部