select
JSON_UNQUOTE(JSON_EXTRACT(a.order_form_info, '$.Content')) as 'form'
from order_form a ;
表中json字符串中特殊空格字符,使用JSON_EXTRACT报错
Invalid JSON text in argument 1 to function json_extract: "Invalid escape character in string." at position 32.
{"Content": "(历时4小时) 反馈添加笔记","city": "10000", "orderId": "1","orderType": "无线域","serviceStartTime": 202203060000,"serviceStopTime": 202203070000}
解决
将有问题的JSON字符串的空格进行替换处理:
select
JSON_EXTRACT(REPLACE( REPLACE(REPLACE(a.order_form_info, CHAR(9), ''), CHAR(10), '\n'), CHAR(13), '\n'), '$.Content')as 'form'
from order_form a ;
执行结果
char(9), char(10), char(13)分别是:
char(9) 表示水平制表符 (tab键 \t)
char(10) 表示换行键 (\n)
char(13) 表示回车键 (\r)