承接上文:MySQL 中在不编写存储过程或函数的前提下,利用SQL 语句拆分一个字符串到数组
主要思路其实一致:够造序列,然后结合MySQL的SUBSTRING_INDEX 函数的特性分割字符串。
SET @str ="a,b,c,d,e,f,g,h,i,j,k" ;
SET @delim="," ;
SET @str_length=length(@str)- length( REPLACE (@str,@delim,'') )+1 ;
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX( @str, @delim, tmp.seq),@delim, - 1) as str_split
FROM
(
SELECT
s1.i + s2.i + s3.i + s4.i + s5.i + s6.i + s7.i + s8.i + s9.i+1 AS seq
FROM ( SELECT 0 AS i UNION ALL SELECT 1 AS i ) s1
CROSS JOIN ( SELECT 0 AS i UNION ALL SELECT 2 AS i ) s2
CROSS JOIN ( SELECT 0 AS i UNION ALL SELECT 4 AS i ) s3
CROSS JOIN ( SELECT 0 AS i UNION ALL SELECT 8 AS i ) s4
CROSS JOIN ( SELECT 0 AS i UNION ALL SELECT 16 AS i ) s5
CROSS JOIN ( SELECT 0 AS i UNION ALL SELECT 32 AS i ) s6
CROSS JOIN ( SELECT 0 AS i UNION ALL SELECT 64 AS i ) s7
CROSS JOIN ( SELECT 0 AS i UNION ALL SELECT 128 AS i ) s8
CROSS JOIN ( SELECT 0 AS i UNION ALL SELECT 256 AS i ) s9
ORDER BY seq
LIMIT 400
) tmp
WHERE
tmp.seq |