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; //
相关文章：