场景:部分字段是采用json存储的,当需要查询json字段中的某一个值时,不处理的话,会默认取出双引号
mysql字段中的json
{"return_code":"SUCCESS","return_msg":{},"mch_appid":"wx123456","mchid":"123456","nonce_str":"mmvfvm7r","result_code":"SUCCESS","partner_trade_no":"ER201912301431061279295325","payment_no":"10100171331631912310070916971367","payment_time":"2019-12-31 20:20:16"}
SELECT
JSON_EXTRACT(`remark`, '$.payment_time') #会取出有双引号
#JSON_UNQUOTE(JSON_EXTRACT(`remark`,'$.payment_time') )# 不会取出双引号
#`remark`->>'$.payment_time' # 不会取出双引号
#unix_timestamp(`remark`->>'$.payment_time') ## 不会取出双引号且格式化时间
FROM cms_send_wechat_log
WHERE `status`= 1