mysql 列转行 并进行json截取(mysql 5.6)
标签：sel HERE union str ring file help -- url
mysql 列转行 并进行json截取(mysql 5.6)
t1.*
,concat(replace(substr(result,instr(result,‘"url":"‘) +7,instr(result,‘"}‘)-instr(result,‘"url":"‘) -7),‘https://‘,‘http://‘),‘@1500h‘) as temp_image -- json数据截取
from (
SELECT
t.*
,concat(substring_index(substring_index(t.image_file,‘"},{"‘, b.help_topic_id + 1), ‘"},{"‘, -1),‘"}‘) as result -- 列转行
FROM (
SELECT
a.id
,a.image_file -- json array string
FROM table_a a
where length(a.image_file) > 3
limit 10,1
) t
join (
select 0 as help_topic_id union all
select 1 as help_topic_id union all
select 2 as help_topic_id union all
select 3 as help_topic_id union all
select 4 as help_topic_id union all
select 5 as help_topic_id union all
select 6 as help_topic_id union all
select 7 as help_topic_id union all
select 8 as help_topic_id union all
select 9 as help_topic_id
) b
ON b.help_topic_id < ((LENGTH(t.image_file) - LENGTH(REPLACE(t.image_file, ‘"},{"‘, ‘‘)))/5 + 1)
order by result
) t1
;