SQL对JSON的转义

浏览:19日期:2023-02-13

问题描述

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

问题解答

回答1:

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

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

相关文章: