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 ;

 

mysql 列转行 并进行json截取(mysql 5.6)

标签：sel   HERE   union   str   ring   file   help   --   url