Mysql插入JSON串會被去一層轉義

問題描述

背景：在數據庫更新、新增一個字段為JSON串的時候，被去一層轉義，導致程序解析失敗，報錯

原JSON串

[{"rules": [{"ruleType": 1,"value": "1458","rate": 90},{"ruleType": 3,"value": "{\"begin\":\"測試歡迎語\",\"end\":\"測試結束語\",\"template\":[\"6962def-sadfa\"],\"title\":\"標題\"}","rate": 10}],"tag": 1,"sort": 2,"ruleName": "分流默認路由"},{"rules": [{"ruleType": 1,"value": "1533","rate": ""}],"labelCode": "red_user","sort": 1,"tag": 2,"ruleName": "標簽路由"} ]

先對JSON壓縮

[{"rules": [{"ruleType": 1,"value": "1458","rate": 90},{"ruleType": 3,"value": "{\"begin\":\"測試歡迎語\",\"end\":\"測試結束語\",\"template\":[\"6962def-sadfa\"],\"title\":\"標題\"}","rate": 10}],"tag": 1,"sort": 2,"ruleName": "分流默認路由"},{"rules": [{"ruleType": 1,"value": "1533","rate": ""}],"labelCode": "red_user","sort": 1,"tag": 2,"ruleName": "標簽路由"}]

然后就直接

update `shuidi_cs_biz`.`session_sub_route_rule` set rules = '[{"rules": [{"ruleType": 1,"value": "1458","rate": 90},{"ruleType": 3,"value": "{\"begin\":\"測試歡迎語\",\"end\":\"測試結束語\",\"template\":[\"6962def-sadfa\"],\"title\":\"標題\"}","rate": 10}],"tag": 1,"sort": 2,"ruleName": "分流默認路由"},{"rules": [{"ruleType": 1,"value": "1533","rate": ""}],"labelCode": "red_user","sort": 1,"tag": 2,"ruleName": "標簽路由"}]' where id = 1;

update 或者 insert 都行， 然后查下更新后的

[ {"rules": [{"ruleType": 1,"value": "1458","rate": 90},{//注意這部分，解析出現問題了"ruleType": 3,"value": "{"begin ":"測試歡迎語 ","end ":"測試結束語 ","template ":["6962 def - sadfa "],"title ":"標題 "}","rate": 10}],"tag": 1,"sort": 2,"ruleName": "分流默認路由" }, {"rules": [{"ruleType": 1,"value": "1533","rate": ""}],"labelCode": "red_user","sort": 1,"tag": 2,"ruleName": "標簽路由" }]

然后GG了，線上出問題了。

注意

這種問題出現在**，JSON字符串字段，包含一個轉義字符串的情況**，如圖

這里，

解決的辦法

第一種： 在壓縮字符串后，進行一次轉義

[{\"rules\": [{\"ruleType\": 1,\"value\": \"1458\",\"rate\": 90},{\"ruleType\": 3,\"value\": \"{\\\"begin\\\":\\\"測試歡迎語\\\",\\\"end\\\":\\\"測試結束語\\\",\\\"template\\\":[\\\"6962def-sadfa\\\"],\\\"title\\\":\\\"標題\\\"}\",\"rate\": 10}],\"tag\": 1,\"sort\": 2,\"ruleName\": \"分流默認路由\"},{\"rules\": [{\"ruleType\": 1,\"value\": \"1533\",\"rate\": \"\"}],\"labelCode\": \"red_user\",\"sort\": 1,\"tag\": 2,\"ruleName\": \"標簽路由\"}]

然后新增，修改就都可以

第二種

有第一種，必然有第二種，哈哈哈

1、我用的navicat，直接在工具上，copy as insert statement

2、 然后發現，人家的格式就是轉義后的，參考這個維護就行

insert into `shuidi_cs_biz`.`session_sub_route_rule` ( `sub_route_name`, `channel_id`, `rules`, `status`, `is_delete`, `create_time`, `update_time`) values ( '路由', '7', '[{\"rules\": [{\"ruleType\": 1,\"value\": \"1458\",\"rate\": 90},{\"ruleType\": 3,\"value\": \"{\"begin\":\"測試歡迎語\",\"end\":\"測試結束語\",\"template\":[\"6962def-sadfa\"],\"title\":\"標題\"}\",\"rate\": 10}],\"tag\": 1,\"sort\": 2,\"ruleName\": \"分流默認路由\"},{\"rules\": [{\"ruleType\": 1,\"value\": \"1533\",\"rate\": \"\"}],\"labelCode\": \"red_user\",\"sort\": 1,\"tag\": 2,\"ruleName\": \"標簽路由\"}]', '1', '1', '2021-07-26 10:25:02', '2022-04-15 11:05:46');

這個問題采坑線上兩次了

總結

以上是生活随笔為你收集整理的Mysql插入JSON串会被去一层转义的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。