编码踩坑——MySQL更新存放JSON的字段、\“ 转义成 “

拼搏现实的明天。 2023-09-23 19:19 64阅读 0赞

本篇介绍在执行MySQL线上变更时遇到的问题，表现为"更新JSON字段时，实际更新的值与SQL语句中的值不一致，JSON格式错误"，如下；

### 问题描述 ###

处理线上问题，需要新插入一条记录；将原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
    }

![292d161d14b3429d826ec6c96af3b6ed.png][]

###  ###

### 问题排查 ###

联系DBA描述问题，得到的回复是——

> **\\"相当于就转义成 "了，这是MySQL自身处理的结果；**

实验如下：

![6314b0a151cb4022a3ac25899d53ed3c.png][]

而代码中执行的就没问题，原因是代码中该字段对应的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\": 0\n" +
                "}";
        
        // 未格式化 会少去/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}";

[292d161d14b3429d826ec6c96af3b6ed.png]: https://img-blog.csdnimg.cn/292d161d14b3429d826ec6c96af3b6ed.png
[6314b0a151cb4022a3ac25899d53ed3c.png]: https://img-blog.csdnimg.cn/6314b0a151cb4022a3ac25899d53ed3c.png