如为8.0以上版本,可以直接使用原生的json解析器处理,低版本不支持
set @fild='"},{"'; SELECT t1.id ,t2.num_rn ,substring_index(substring_index(t1.image_file,@fild, t2.num_rn + 1), '"},{"', -1) as result -- 列转行 FROM ( SELECT 1 as id ,'[{"id":"78c34a25-c206-4e32-9dc1-082a115f4d3e","user_id":"b79bb5e1-b08c-4a05-be3e-0fb1154d925f","url":"https://img_url/mobilecheckquality/upload/d3c12032-3863-4125-83db-5caeb095bac8.jpg"},{"id":"724bc057-be95-4ba4-b0c9-4ce9623dd6ed","user_id":"b79bb5e1-b08c-4a05-be3e-0fb1154d925f","url":"https://img_url/mobilecheckquality/upload/12497eac-0033-4152-9ea7-529eb2f3f9c6.jpg"},{"id":"416cbb4f-8a16-419d-a7c6-a3d01877548b","user_id":"b79bb5e1-b08c-4a05-be3e-0fb1154d925f","url":"https://img_url/mobilecheckquality/upload/416cbb4f-8a16-419d-a7c6-a3d01877548b.jpg"},{"id":"ef56c530-cf6e-42c5-a82b-5df159732a08","user_id":"b79bb5e1-b08c-4a05-be3e-0fb1154d925f","url":"https://img_url/mobilecheckquality/upload/ef56c530-cf6e-42c5-a82b-5df159732a08.jpg"}]' as image_file ) t1 join ( select 0 as num_rn union all select 1 as num_rn union all select 2 as num_rn union all select 3 as num_rn union all select 4 as num_rn union all select 5 as num_rn union all select 6 as num_rn union all select 7 as num_rn union all select 8 as num_rn union all select 9 as num_rn ) t2 ON t2.num_rn < ((LENGTH(t1.image_file) - LENGTH(REPLACE(t1.image_file, @fild, '')))/length(@fild) + 1) order by t2.num_rn ;