MYSQL Explode 字符串切割

MYSQL Explode 字符串切割

在老外的站点上看到非常不错的 Explode 存储过程实现。为了能方便处理 SELECT 出来的结果集字段（select lib_Explode(‘,’,string) from xxx; ），我改了一个 FUNCTION ，基本上是一样的。但需要将里面建表的语句单独拿出来。

EXPLODE 方法：

DROP TABLE IF EXISTS lib_Explode;
CREATE TABLE lib_Explode(
`pos` int unsigned NOT NULL auto_increment,
`val` VARCHAR(255) NOT NULL,
PRIMARY KEY (`pos`)
) ENGINE=Memory COMMENT='Explode() results.';


CREATE FUNCTION lib_Explode( sSepar VARCHAR(255), saVal TEXT ) returns INTEGER
body:
BEGIN
IF sSepar IS NULL OR saVal IS NULL THEN LEAVE body; END IF;

SET @saTail = saVal;
SET @iSeparLen = LENGTH( sSepar );
SET @total = 0;

create_layers:
WHILE @saTail != '' DO

# Get the next value
SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
INSERT INTO lib_Explode SET val = @sHead;

END WHILE;

SELECT count(*) INTO @total from lib_Explode;
return @total;

END; //
EXPLODE 存储过程：

CREATE PROCEDURE lib_Explode( sSepar VARCHAR(255), saVal TEXT )
body:
BEGIN

DROP TEMPORARY TABLE IF EXISTS lib_Explode;
CREATE TEMPORARY TABLE lib_Explode(
`pos` int unsigned NOT NULL auto_increment,
`val` VARCHAR(255) NOT NULL,
PRIMARY KEY (`pos`)
) ENGINE=Memory COMMENT='Explode() results.';

IF sSepar IS NULL OR saVal IS NULL THEN LEAVE body; END IF;

SET @saTail = saVal;
SET @iSeparLen = LENGTH( sSepar );

create_layers:
WHILE @saTail != '' DO

# Get the next value
SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
INSERT INTO lib_Explode SET val = @sHead;

END WHILE;

END; //
相关文章：