MYSQL 分享：split最快速度将字符串进行分割以表的方式进行展示

USE test;  
CREATE TABLE test.Num ( xh INT PRIMARY KEY ); -- 创建数字辅助表   
SET @i = 0;   
INSERT INTO test.Num(xh) -- 写入数字辅助表   
SELECT @i:=@i+1   
FROM information_schema.`TABLES` a  , information_schema.`TABLES` b   
LIMIT 0 ,100 ;  


SET @str = "as,sbsd,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16";  


-- 1 
SELECT  SUBSTRING( str,xh, LOCATE(",",CONCAT(@str,","),xh)-xh) AS splitstr  
FROM test.Num a,  
( SELECT @str AS str )b  
WHERE a.xh <= LENGTH(  str)  
AND SUBSTRING( CONCAT(",",str),xh, 1) = "," ;  
-- 2   
SET @p_DayId = "11,20,30",@p_Num = "40,50,60";
SELECT  
SUBSTRING_INDEX(SUBSTRING_INDEX(@p_DayId, ",", xh), ",", -1) a,
SUBSTRING_INDEX(SUBSTRING_INDEX(@p_Num, ",", xh), ",", -1) b
FROM  Num
WHERE xh <= (LENGTH(@p_DayId)-LENGTH(REPLACE(@p_DayId,",","")) +1)    
-- 经测试，以上是最快方式；利用集合处理的思想，避免了循环分割。



SELECT aa.id,aa.`mc1`,  aa.type,  SUBSTRING_INDEX(SUBSTRING_INDEX(aa.type, "|", xh), "|", -1) a    
FROM   ys.Num  nn
JOIN bidw.`tv` aa
WHERE xh <= (CHAR_LENGTH(aa.type)-CHAR_LENGTH(REPLACE(aa.type,"|","")) +1)    




-- 下面是mssql


USE tempdb; 
CREATE TABLE Num( xh INT PRIMARY KEY(xh) );
DECLARE @xh INT ;
SET @xh = 1 
WHILE @xh <=1000 
BEGIN 
INSERT INTO Num(xh)
SELECT @xh ; 
SET @xh = @xh + 1 ;
END 




DECLARE @string VARCHAR(1000);
SET @string = "S,M,LL,XL,XXL,3XL,4XL";
SELECT t.Splitstr_
FROM ( SELECT a.xh, 
SUBSTRING( str_,  a.xh  , charindex(",", str_+ ","  ,a.xh ) - a.xh ) AS splitstr_ 
FROM Num  a,  
( SELECT @string AS str_  
) b  
WHERE a.xh <= len(str_)  
AND SUBSTRING( ","+str_ ,xh, 1) = "," ) t