快捷導(dǎo)航

MySQL更新存放JSON的字段、\“ 轉(zhuǎn)義成 “的問題描述

更新時間：2022年12月20日 11:21:22 作者：七海健人

本篇介紹在執(zhí)行MySQL線上變更時遇到的問題，表現(xiàn)為"更新JSON字段時，實(shí)際更新的值與SQL語句中的值不一致，JSON格式錯誤"，本文給大家分享問題描述及解決方案，感興趣的朋友一起看看吧

問題描述

處理線上問題，需要新插入一條記錄；將原JSON粘貼出來，修改部分字段，然后提交SQL更新語句，原始JSON如下：

{
    "playerQualifyType": 1,
    "surveyId": 14,
    "playerRegisterEndTime": 1670860799000,
    "planName": "《碧藍(lán)大陸》先鋒測試招募！",
    "planStatus": 2,
    "playerRegisterStartTime": 1669860000000,
    "gameName": "碧藍(lán)大陸",
    "developerName": "海南羽弘信息技術(shù)有限公司",
    "planStartTime": 1673316000000,
    "qualificationCodeUrl": "",
    "updateTime": 1669777099000,
    "planPlayerNum": 1500,
    "extend": "{\"innerDesc\":\"\",\"planId\":16,\"verifyOperator\":\"Akira-11126666\",\"verifyResultDesc\":\"\",\"verifyResultDescImgList\":[],\"verifyResultStatus\":2,\"verifyTime\":1669777099887}",
    "planEndTime": 1673884800000,
    "pkgStatus": 0
}

現(xiàn)提交SQL變更單，執(zhí)行SQL語句如下：

update t set `json_field` = 
'
{
    "playerQualifyType": 1,
    "surveyId": 14,
    "playerRegisterEndTime": 1670860799000,
    "planName": "《碧藍(lán)大陸》先鋒測試招募！",
    "planStatus": 2,
    "playerRegisterStartTime": 1669860000000,
    "gameName": "碧藍(lán)大陸",
    "developerName": "海南羽弘信息技術(shù)有限公司",
    "planStartTime": 1673316000000,
    "qualificationCodeUrl": "",
    "updateTime": 1669777099000,
    "planPlayerNum": 1500,
    "extend": "{\"innerDesc\":\"\",\"planId\":16,\"verifyOperator\":\"Akira-11126666\",\"verifyResultDesc\":\"\",\"verifyResultDescImgList\":[],\"verifyResultStatus\":2,\"verifyTime\":1669777099887}",
    "planEndTime": 1673884800000,
    "pkgStatus": 0
}
'
where id = 55;

結(jié)果數(shù)據(jù)庫中更新后查出來的紀(jì)錄如下，不滿足JSON格式了，仔細(xì)檢查發(fā)現(xiàn)是extend字段的值中的反斜杠全部沒有了，JSON解析失敗；

{
    "playerQualifyType": 1,
    "surveyId": 14,
    "playerRegisterEndTime": 1670860799000,
    "planName": "《碧藍(lán)大陸》先鋒測試招募！",
    "planStatus": 2,
    "playerRegisterStartTime": 1669860000000,
    "gameName": "碧藍(lán)大陸",
    "developerName": "海南羽弘信息技術(shù)有限公司",
    "planStartTime": 1673316000000,
    "qualificationCodeUrl": "",
    "updateTime": 1669777099000,
    "planPlayerNum": 1500,
    "extend": "{"innerDesc":"","planId":16,"verifyOperator":"Akira-11126666","verifyResultDesc":"","verifyResultDescImgList":[],"verifyResultStatus":2,"verifyTime":1669777099887}",
    "planEndTime": 1673884800000,
    "pkgStatus": 0
}

問題排查

聯(lián)系DBA描述問題，得到的回復(fù)是——

\"相當(dāng)于就轉(zhuǎn)義成 "了，這是MySQL自身處理的結(jié)果；

實(shí)驗(yàn)如下：

而代碼中執(zhí)行的就沒問題，原因是代碼中該字段對應(yīng)的Java類型為String，也就是說原JSON的外層因?yàn)樘琢艘粚与p引號""，因此還會有一次轉(zhuǎn)義符的嵌套，如下；

\"extend\":\"{\\\"innerDesc\\\":\\\"\\\",\\\"planId\\\":16,\\\"verifyOperator\\\":\\\"Akira-11126666\\\",\\\"verifyResultDesc\\\":\\\"\\\",\\\"verifyResultDescImgList\\\":[],\\\"verifyResultStatus\\\":2,\\\"verifyTime\\\":1669777099887}\"

因此，代碼中執(zhí)行JSON字符串的MySQL插入，實(shí)際入庫的值與預(yù)期一致；正確的SQL如下：

update t set `json_field` = 
'
{\"playerQualifyType\":1,\"surveyId\":14,\"playerRegisterEndTime\":1670860799000,\"planName\":\"《碧藍(lán)大陸》先鋒測試招募！\",\"planStatus\":2,\"playerRegisterStartTime\":1669860000000,\"gameName\":\"碧藍(lán)大陸\",\"developerName\":\"海南羽弘信息技術(shù)有限公司\",\"planStartTime\":1673316000000,\"qualificationCodeUrl\":\"\",\"updateTime\":1669777099000,\"planPlayerNum\":1500,\"extend\":\"{\\\"innerDesc\\\":\\\"\\\",\\\"planId\\\":16,\\\"verifyOperator\\\":\\\"Akira-11126666\\\",\\\"verifyResultDesc\\\":\\\"\\\",\\\"verifyResultDescImgList\\\":[],\\\"verifyResultStatus\\\":2,\\\"verifyTime\\\":1669777099887}\",\"planEndTime\":1673884800000,\"pkgStatus\":0}
'
where id = 55;

解決方案

在更新Mysql表的JSON字符串字段時，步驟應(yīng)該如下：

1. 輸入查詢語句，找到目標(biāo)記錄，復(fù)制JSON字段的值到JSON編輯工具中修改JSON中的屬性值

2. 將修改后的JSON粘貼到IDE中，IDE用雙引號接收這一串JSON；

3. 執(zhí)行SQL時，SQL語句中的值以IDE中被雙引號包裹的字符串值為SQL中的新的value；

    // 將JSON粘貼進(jìn)來 整體作為SQL更新語句中的新的value 這里也可以不格式化JSON
    String formatValue = "{\n" +
            "    \"playerQualifyType\": 1,\n" +
            "    \"surveyId\": 14,\n" +
            "    \"playerRegisterEndTime\": 1670860799000,\n" +
            "    \"planName\": \"《碧藍(lán)大陸》先鋒測試招募！\",\n" +
            "    \"planStatus\": 2,\n" +
            "    \"playerRegisterStartTime\": 1669860000000,\n" +
            "    \"gameName\": \"碧藍(lán)大陸\",\n" +
            "    \"developerName\": \"海南羽弘信息技術(shù)有限公司\",\n" +
            "    \"planStartTime\": 1673316000000,\n" +
            "    \"qualificationCodeUrl\": \"\",\n" +
            "    \"updateTime\": 1669777099000,\n" +
            "    \"planPlayerNum\": 1500,\n" +
            "    \"extend\": \"{\\\"innerDesc\\\":\\\"\\\",\\\"planId\\\":16,\\\"verifyOperator\\\":\\\"Akira-11126666\\\",\\\"verifyResultDesc\\\":\\\"\\\",\\\"verifyResultDescImgList\\\":[],\\\"verifyResultStatus\\\":2,\\\"verifyTime\\\":1669777099887}\",\n" +
            "    \"planEndTime\": 1673884800000,\n" +
            "    \"pkgStatus\": 0\n" +
            "}";
    
    // 未格式化 會少去/n換行符
    String value = "{\"playerQualifyType\":1,\"surveyId\":14,\"playerRegisterEndTime\":1670860799000,\"planName\":\"《碧藍(lán)大陸》先鋒測試招募！\",\"planStatus\":2,\"playerRegisterStartTime\":1669860000000,\"gameName\":\"碧藍(lán)大陸\",\"developerName\":\"海南羽弘信息技術(shù)有限公司\",\"planStartTime\":1673316000000,\"qualificationCodeUrl\":\"\",\"updateTime\":1669777099000,\"planPlayerNum\":1500,\"extend\":\"{\\\"innerDesc\\\":\\\"\\\",\\\"planId\\\":16,\\\"verifyOperator\\\":\\\"Akira-11126666\\\",\\\"verifyResultDesc\\\":\\\"\\\",\\\"verifyResultDescImgList\\\":[],\\\"verifyResultStatus\\\":2,\\\"verifyTime\\\":1669777099887}\",\"planEndTime\":1673884800000,\"pkgStatus\":0}";

到此這篇關(guān)于MySQL更新存放JSON的字段、\“ 轉(zhuǎn)義成 “的文章就介紹到這了,更多相關(guān)MySQL JSON轉(zhuǎn)義內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: