如何将从mySQL中选择列的结果拆分为多个列

Question

我在MYSQL数据库中有列想提取那列数据并将其拆分成多个列这里是我想拆分的数据样本

```

{"1744":"1","1745":"1","1747":"1","1748":"1","1749":"1","1750":"1"} 
{"1759":"1"} 
{"47":"1","48":"Ehebr","49":"1479977045596.jpg"} 

``` 我想这分为两列，像这样与第一数据：

当你看到这个数据有不同的长度，并会升ike能够分割任何长度的数据，看看这里[How to split a resulting column in multiple columns 但我不认为这就是我想要的结果，我到那里就像这样还想修剪所有其他花括号和报价数据。这里是我到目前为止的代码 ```

SELECT combined,SUBSTRING_INDEX(combined , ':', 1) AS a, 
SUBSTRING_INDEX(SUBSTRING_INDEX(combined , ':', 2),':',-1) AS b, 
SUBSTRING_INDEX(SUBSTRING_INDEX(combined , ':', -2),':',1) AS c, 
SUBSTRING_INDEX(combined , ':', -1) AS d 
FROM tablefoo WHERE combined is not null; 

```

Answer 1

如果你能过程和游标住

drop procedure if exists p; 
delimiter // 
CREATE DEFINER=`root`@`localhost` PROCEDURE `p`(
    IN `instring` varchar(255) 

) 
LANGUAGE SQL 
NOT DETERMINISTIC 
CONTAINS SQL 
SQL SECURITY DEFINER 
COMMENT '' 
begin 
declare tempstring varchar(10000); 
declare outstring varchar(100); 
declare c1 varchar(100); 
declare c2 varchar(100); 
declare checkit int; 
declare done int; 
DECLARE CUR1 CURSOR for SELECT t.col FROM T; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 

drop table if exists occursresults; 
create table occursresults (col1 varchar(20), col2 varchar(20)); 

open CUR1; 
read_loop: LOOP 
     FETCH CUR1 INTO tempstring; 

     if done then leave read_loop; end if; 

     set tempstring = replace(tempstring,'{',''); 
     set tempstring = replace(tempstring,'}',''); 
     set tempstring = replace(tempstring,'"',''); 
     set checkit = 0; 
     #select tempstring; 

     looper: while tempstring is not null and instr(tempstring,',') > 0 do 
       set checkit = checkit + 1; 
       if checkit > 100 then #In case of infinite loop 
        leave looper; 
       end if; 
       set outstring = substr(tempstring,1,instr(tempstring, ',') - 1); 
       set tempstring = ltrim(rtrim(replace(tempstring,concat(outstring,','),''))); 
       set c1 = substr(outstring,1,instr(outstring, ':') - 1); 
       set c2 = replace(outstring,concat(c1,':'),''); 
       INSERT INTO OCCURSRESULTS (COL1,COL2) VALUES (c1,c2); 
     #  select tempstring,outstring,c1,c2;  
     end while; 
     #select tempstring; 
     set outstring = tempstring; 
     set c1 = substr(outstring,1,instr(outstring, ':') - 1); 
     set c2 = replace(outstring,concat(c1,':'),''); 
     INSERT INTO OCCURSRESULTS (Col1,Col2) VALUES (c1,c2); 


end loop; 
close cur1; 

end // 

delimiter ; 

MariaDB [sandbox]> select * from t; 
+---------------------------------------------------------------------+ 
| col                 | 
+---------------------------------------------------------------------+ 
| {"1744":"1","1745":"1","1747":"1","1748":"1","1749":"1","1750":"1"} | 
| {"1759":"1"}              | 
| {"47":"1","48":"Ehebr","49":"1479977045596.jpg"}     | 
+---------------------------------------------------------------------+ 
3 rows in set (0.00 sec) 

MariaDB [sandbox]> 
MariaDB [sandbox]> call p(1); 
Query OK, 0 rows affected (0.65 sec) 

MariaDB [sandbox]> 
MariaDB [sandbox]> SELECT * FROM OCCURSRESULTS; 
+------+-------------------+ 
| col1 | col2    | 
+------+-------------------+ 
| 1744 | 1     | 
| 1745 | 1     | 
| 1747 | 1     | 
| 1748 | 1     | 
| 1749 | 1     | 
| 1750 | 1     | 
| 1759 | 1     | 
| 47 | 1     | 
| 48 | Ehebr    | 
| 49 | 1479977045596.jpg | 
+------+-------------------+ 
10 rows in set (0.00 sec)