Mysql插入JSON串会被去一层转义

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');

这个问题采坑线上两次了