MySQL更新存放JSON的欄位、\「跳脫成「的問題描述

2022-12-21 14:01:32

問題描述

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

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

現提交SQL變更單，執行SQL語句如下：

update t set `json_field` = 
'
{
    "playerQualifyType": 1,
    "surveyId": 14,
    "playerRegisterEndTime": 1670860799000,
    "planName": "《碧藍大陸》先鋒測試招募！",
    "planStatus": 2,
    "playerRegisterStartTime": 1669860000000,
    "gameName": "碧藍大陸",
    "developerName": "海南羽弘資訊科技有限公司",
    "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;

結果資料庫中更新後查出來的紀錄如下，不滿足JSON格式了，仔細檢查發現是extend欄位的值中的反斜槓全部沒有了，JSON解析失敗；

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

問題排查

聯絡DBA描述問題，得到的回覆是——

"相當於就跳脫成 "了，這是MySQL自身處理的結果；

實驗如下：

而程式碼中執行的就沒問題，原因是程式碼中該欄位對應的Java型別為String，也就是說原JSON的外層因為套了一層雙引號""，因此還會有一次跳脫符的巢狀，如下；

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

因此，程式碼中執行JSON字串的MySQL插入，實際入庫的值與預期一致；正確的SQL如下：

update t set `json_field` = 
'
{"playerQualifyType":1,"surveyId":14,"playerRegisterEndTime":1670860799000,"planName":"《碧藍大陸》先鋒測試招募！","planStatus":2,"playerRegisterStartTime":1669860000000,"gameName":"碧藍大陸","developerName":"海南羽弘資訊科技有限公司","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字串欄位時，步驟應該如下：

1. 輸入查詢語句，找到目標記錄，複製JSON欄位的值到JSON編輯工具中修改JSON中的屬性值

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

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

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

到此這篇關於MySQL更新存放JSON的欄位、“ 跳脫成 “的文章就介紹到這了,更多相關MySQL JSON跳脫內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL更新存放JSON的欄位、\「跳脫成「的問題描述

目錄

問題描述

問題排查

解決方案

熱門文章

MySQL更新存放JSON的欄位、\「 跳脫成 「的問題描述

目錄

問題描述

問題排查

解決方案

熱門文章

MySQL更新存放JSON的欄位、\「跳脫成「的問題描述