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

最新推荐文章于 2025-09-09 11:40:13 发布

原创最新推荐文章于 2025-09-09 11:40:13 发布 · 置顶 · 4.6k 阅读

CC 4.0 BY-SA版权

本文详细介绍了SQL中字符串处理、数据表操作等关键技术，包括数字辅助表创建、字符串分割、子串提取及数据库查询优化策略。通过实例演示了如何高效地进行数据库管理与数据操作。

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

oracle 字符串分割版本


with a as (select '1' WWW,  'A,B,C,D' str FROM DUAL
           UNION select '2' WWW,  'X,Y,Z' str FROM DUAL)
,b as (select row_number() over (order by 'xh') xh from user_objects ) 

SELECT www, substr( str , xh, instr(CONCAT(str,','),',', xh)   -xh) AS splitstr  
FROM  a,  
      b  
WHERE xh <= LENGTH(  str)  
AND substr( CONCAT(',',str),xh, 1) = ',' ;