功能类似于MySQL中的爆炸功能

Is there any function in MySQL to explode data of a column and then retrieve it? Like if a column data is P:12 , then can the data be exploded on ':' and then read?

MySQL中是否有任何函数可以分解列的数据然后检索它？就像列数据是P：12一样，那么数据可以在'：'上展开然后读取吗？

3 个解决方案

#1

Here are many discussion about the SPLIT problem in mysql :

以下是关于mysql中SPLIT问题的许多讨论：

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

#2

You can write a Split function in MYSQL check this link

您可以在MYSQL中编写一个Split函数来检查此链接

From the Link

来自链接

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)

RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Usage

用法

SELECT SPLIT_STR(string, delimiter, position)

Example

例

SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

+-------+
| third |
+-------+
| ccc   |
+-------+

#3

I parse the string using a while loop and insert the split items into a temporary table. It's complicated, but I copy and paste the code, then change the table name for readability. Here's an example to parse a comma delimited list of user ids into a temp table:

我使用while循环解析字符串并将拆分项插入临时表。它很复杂，但我复制并粘贴代码，然后更改表名以便于阅读。这是一个将逗号分隔的用户ID列表解析为临时表的示例：

CREATE PROCEDURE spParse
(_UserList MEDIUMTEXT)
BEGIN
    DECLARE _Pos INT;
    DECLARE _Start INT;
    DECLARE _Item INT;
    DECLARE _Length INT;

    CREATE TEMPORARY TABLE IF NOT EXISTS TempUserList
    (
        UserID INT
    );

    SET _Length = LENGTH(_UserList);
    SET _Pos = 1;
    SET _Start = 1;
    divide_loop:LOOP
        IF _Pos > _Length Then
            LEAVE divide_loop;
        End If;
        IF SUBSTRING(_UserList,_Pos,1) = ',' Then
            IF _Pos - _Start > 0 Then
                IF IsNumeric(SUBSTRING(_UserList,_Start,_Pos-_Start)) Then
                    SET _Item = CONVERT(SUBSTRING(_UserList,_Start,_Pos-_Start),Signed);
                    INSERT INTO TempUserList (UserID)
                    VALUES (_Item);
                End If;                 
            End If;
            SET _Start = _Pos + 1;
        End If;
        SET _Pos = _Pos + 1;
    END LOOP divide_loop;
    IF _Start <= _Length Then
        If IsNumeric(SUBSTRING(_UserList,_Start,_Length - _Start + 1)) Then
            SET _Item = CONVERT(SUBSTRING(_UserList,_Start,_Length - _Start + 1),Signed);       
            INSERT INTO TempUserList (UserID)
            VALUES (_Item);
        End If;
    End If;

    SELECT UserID FROM TempUserList;

    DROP TABLE TempUserList;
END

#1