mysql split 逗号分隔_MYSQL :逗号分隔串表，分解成竖表

DROP TEMPORARY TABLE IF EXISTS Temp_Num ;

CREATE TEMPORARY TABLE Temp_Num ( xh INT PRIMARY KEY ); -- 创建数字辅助表

SET @i = 0;

INSERT INTO Temp_Num(xh) -- 写入数字辅助表

SELECT @i := @i+1

FROM AdDataCenter.`Ad_Targeting_Mobisage` a

LIMIT 0, 100 ;

SELECT b.AdGroupID , SUBSTRING( str_split , a.xh , LOCATE(',',CONCAT( str_split ,','), a.xh ) - a.xh ) AS splitstr

FROM Temp_Num a

CROSS JOIN

(SELECT AppCategory AS str_split ,app.*

FROM AdDataCenter.Ad_Targeting_Mobisage app

WHERE app.AdTargetingID IN (1,2,3,4) ) b

WHERE a.xh <= LENGTH( str_split )

AND SUBSTRING( CONCAT(',', str_split ), a.xh, 1) = ','

LIMIT 0 ,1000 ;

SELECT AppCategory AS str_split ,app.*

FROM AdDataCenter.Ad_Targeting_Mobisage app

WHERE app.AdTargetingID IN (1,2,3,4);

原数据

str_split                                                               AdTargetingID

----------------------------------------------------------------------  ---------------

1,10,11,12,13,14,15,16,19,2,20,21,22,24,25,26,27,28,29,3,31,32,4,5,6,8                2

1,10,11,13,14,15,16,2,20,21,22,25,26,27,28,29,3,31,32,4,6,8                                    3

1,10,11,12,13,14,15,16,19,20,21,22,25,27,28,3,32,4,6,8                                             4

实现的效果是

AdGroupID  splitstr

---------  ----------

2  1

2  10

2  11

2  12

2  13

2  14

2  15

2  16

2  19

2  2

2  20

2  21

2  22

2  24

2  25

2  26

2  27

2  28

2  29

2  3

2  31

2  32

2  4

2  5

2  6

2  8

3  1

3  10

3  11

3  13

3  14

3  15

3  16

3  2

3  20

3  21

3  22

3  25

3  26

3  27

3  28

3  29

3  3

3  31

3  32

3  4

3  6

3  8

4  1

4  10

4  11

4  12

4  13

4  14

4  15

4  16

4  19

4  20

4  21

4  22

4  25

4  27

4  28

4  3

4  32

4  4

4  6

4  8