本篇介绍在执行MySQL线上变更时遇到的问题，表现为"更新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
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\": 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}";
到此这篇关于MySQL更新存放JSON的字段、\“ 转义成 “的文章就介绍到这了,更多相关MySQL JSON转义内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！
您可能感兴趣的文章:
mysql事务的基本要素与事务隔离级别详解 2023-08-08 MySQL中group by与max()一起使用的坑 2023-08-08 MySQL数据库表的合并及分区方式 2023-08-08 Mysql空值处理函数详解 2023-08-08 Windows安装MySQL后怎么开启root的网络访问权限 2023-08-08 mysql如何将sql查询的结果以百分比展示出来 2023-08-08 MYSQL中解析json格式数据方法示例 2023-08-08 mysql中insert语句的5种用法简单示例 2023-08-08 美国设下计谋,用娘炮文化重塑日本,已影响至中国 2021-11-19 时空伴随者是什么意思?时空伴随者介绍 2021-11-09 工信部称网盘企业免费用户最低速率应满足基本下载需求，天翼云盘回应：坚决支持，始终 2021-11-05 2022年放假安排出炉：五一连休5天 2022年所有节日一览表 2021-10-26 电脑版 - 返回首页
2006-2023 脚本之家 JB51.Net , All Rights Reserved.
