需求:从product_item表的ext_info字段中解析出具体数据
例如ext_info字段存的数据为:{"apply-product-name":"产品名称","enableThisProduct":"0","unitType":"piece","num":"4","unitMoney":"720"}
从上述数据中取出产品名称,数量和单价,对应的SQL为:
SELECT json_extract(ext_info, '$."apply-product-name"') AS 产品名称
, json_extract(ext_info, '$."num"') AS 数量
, json_extract(ext_info, '$."unitMoney"') AS 单价
FROM product_item
WHERE id = '111'
注意事项:如果json中的字段名带引号,sql中的$.后也要带上引号,否则查不出数据