mysql导入竖杠分割的数据_MYSQL:逗号分隔串表，分解成竖表_MySQL

bitsCN.com

MYSQL :逗号分隔串表，分解成竖表

[sql] 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 ---------------------------------------------------------------------- ---