mysql json转义字符_SQL对JSON的转义

在 R 中执行一个SQL，涉及到双层JSON，也就是第二层的JSON包含一个转义符，\。用R的RMySQL 执行的时候第二层的转义符号会被去掉怎么办。

如果我直接复制 statement 在SQL中可以执行成功。

以下是测试数据

UPDATE ANALYSIS SET POSITION_LIST = '[{"date":"20150512","listDatePosition":"[{\\"code\\":\\"600000\\",\\"share\\":274700,\\"orderType\\":1,\\"price\\":17.32,\\"todayPosition\\":0,\\"totalPosition\\":43857,\\"tradeDate\\":\\"20150512104300\\"},{\\"code\\":\\"600652\\",\\"share\\":3900,\\"orderType\\":1,\\"price\\":18.85,\\"todayPosition\\":0,\\"totalPosition\\":623,\\"tradeDate\\":\\"20150512104300\\"},{\\"code\\":\\"600000\\",\\"share\\":180700,\\"orderType\\":1,\\"price\\":17.32,\\"todayPosition\\":0,\\"totalPosition\\":43857,\\"tradeDate\\":\\"20150512104300\\"},{\\"code\\":\\"600652\\",\\"share\\":3900,\\"orderType\\":1,\\"price\\":18.85,\\"todayPosition\\":0,\\"totalPosition\\":623,\\"tradeDate\\":\\"20150512104300\\"}]","startMoney":8000000,"counterFee":0,"endMoney":7996494.47,"deposits":0,"totalMarket":767919.22},{"date":"20150513","listDatePosition":"[{\\"code\\":\\"600000\\",\\"share\\":277100,\\"orderType\\":1,\\"price\\":17.32,\\"todayPosition\\":0,\\"totalPosition\\":28857,\\"tradeDate\\":\\"20150512104300\\"},{\\"code\\":\\"600652\\",\\"share\\":3900,\\"orderType\\":1,\\"price\\":18.85,\\"todayPosition\\":0,\\"totalPosition\\":623,\\"tradeDate\\":\\"20150512104300\\"},{\\"code\\":\\"600000\\",\\"share\\":182300,\\"orderType\\":1,\\"price\\":17.32,\\"todayPosition\\":0,\\"totalPosition\\":28857,\\"tradeDate\\":\\"20150512104300\\"},{\\"code\\":\\"600652\\",\\"share\\":3900,\\"orderType\\":1,\\"price\\":18.85,\\"todayPosition\\":0,\\"totalPosition\\":623,\\"tradeDate\\":\\"20150512104300\\"}]","startMoney":7996494.47,"counterFee":0,"endMoney":7988530.21,"deposits":0,"totalMarket":504980.46},{"date":"20150514","listDatePosition":"[{\\"code\\":\\"600652\\",\\"share\\":365600,\\"orderType\\":1,\\"price\\":21.565,\\"todayPosition\\":0,\\"totalPosition\\":7824,\\"tradeDate\\":\\"20150514103300\\"}]","startMoney":7988530.21,"counterFee":0,"endMoney":7985375.903,"deposits":0,"totalMarket":171189.12}]',TRADE_DAY_COUNT = '3',DEAL_DETAIL = '[{"volume":14649,"code":"600000","price":17.32,"tradeType":1,"tradetime":"20150512104000"},{"volume":9701,"code":"600000","price":17.32,"tradeType":1,"tradetime":"20150512104100"},{"volume":6970,"code":"600000","price":17.32,"tradeType":1,"tradetime":"20150512104200"},{"volume":12537,"code":"600000","price":17.32,"tradeType":1,"tradetime":"20150512104300"},{"volume":623,"code":"600652","price":18.85,"tradeType":1,"tradetime":"20150512104300"},{"volume":15000,"code":"600000","price":17,"tradeType":-1,"tradetime":"20150513103100"},{"volume":19230,"code":"600000","price":16.9,"tradeType":-1,"tradetime":"20150514103100"},{"volume":9627,"code":"600000","price":16.9,"tradeType":-1,"tradetime":"20150514103200"},{"volume":7201,"code":"600652","price":21.8,"tradeType":1,"tradetime":"20150514103300"}]',TYPE = 2 WHERE ID = UNHEX('8a2362225c3d480489e137dac92e8351')

问题已经得到解决。

双层JSON中的\ 在R中是一个转义符号，如果要表达\ 需要用\\表示，所以在生成内成的\\需要用 gsub(“\””,”\\\\\””,json) 来替代。

经过这样处理，才可以保证内层的JSON可以顺利传入到MySQL中。