先看一下库的初始模样
格式为
[{"audio_url":"http:\/\/huayuncpv.meldingcloud.com\/WGXT_LS\/WGXT_LS\/59113250e4b07847ade89953\/aa76609f8f2c43119c4a6812cf0edcbc_10.mp3"}]
我们需要用sql语句将其提取出来
第一种方法
SELECT id, TRIM(BOTH '"' FROM JSON_EXTRACT(
audio_info,"$[0].audio_url")) as audio_url1,
JSON_EXTRACT(
audio_info,"$[1].audio_url") as audio_url2
from tab_article where audio_info is not null
and audio_info !='[]'
结果:
第二种方法
select id,JSON_UNQUOTE(`audio_info`->'$[0].audio_url') as url1,
JSON_UNQUOTE(`audio_info`->'$[1].audio_url') as url2
from tab_article where audio_info is not null
and audio_info !='[]'
结果: