MySQL将逗号分隔的字符串分隔为临时表。

Can you parse a comma separated string into a temp table in MySQL using RegEx?

可以使用RegEx将逗号分隔的字符串解析为MySQL中的临时表吗?

'1|2|5|6' into temp table with 4 rows.

8 个解决方案

#1

This is pretty much the same question as Can Mysql Split a column?

这个问题和Mysql是否可以拆分列是一样的吗?

MySQL doesn't have a split string function so you have to do work arounds. You can do anything with the data once you split it using one of the methods listed on the answer page above.

MySQL没有分割字符串函数，所以你需要做变通。使用上面回答页面中列出的方法分割数据后，可以对数据进行任何操作。

You can loop over that custom function and break when it returns empty, you'll have to play and learn some syntax (or at least I would) but the syntax for a FOR loop in mysql is here: http://www.roseindia.net/sql/mysql-example/for.shtml

您可以在该自定义函数上进行循环，当它返回空时中断，您将不得不学习一些语法(或者至少我愿意)，但是在mysql中for循环的语法是:http://www.roseindia.net/sql/mysql-example/for.shtml。

You can iterate over it, incrementing the position in the function below:

你可以对它进行迭代，增加下面函数中的位置:

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, '');

(Credit: https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ )

(来源:https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/)

Which should return '' if no match is found, so break the loop if no match is found. This will allow you to with only mysql parse over the split string and run the insert queries into a temp table. But man why not just use a scripting language like php for that kind of work? :(

如果没有找到匹配项，则返回，如果没有找到匹配项，则中断循环。这将允许您仅对分割字符串进行mysql解析，并将插入查询运行到临时表中。但是为什么不使用php这样的脚本语言呢?:(

Code for loop syntax:

代码for循环的语法:

DELIMITER $$  

CREATE PROCEDURE ABC(fullstr)

   BEGIN
      DECLARE a INT Default 0 ;
      DECLARE str VARCHAR(255);
      simple_loop: LOOP
         SET a=a+1;
         SET str=SPLIT_STR(fullstr,"|",a);
         IF str='' THEN
            LEAVE simple_loop;
         END IF;
         #Do Inserts into temp table here with str going into the row
         insert into my_temp_table values (str);
   END LOOP simple_loop;
END $$

#2

You can use regular expression in MySQL to specify a pattern for a complex search, you cannot parse the strings.

可以在MySQL中使用正则表达式来指定复杂搜索的模式，不能解析字符串。

But you can build INSERT query with the help of REPLACE and CONCATENATE to save data to temp table.

但是您可以在REPLACE和CONCATENATE的帮助下构建INSERT查询，将数据保存到temp表。

#3

DELIMITER $$  

CREATE PROCEDURE SPLIT_VALUE_STRING()

    BEGIN

        SET @String      = '1,22,333,444,5555,66666,777777';
        SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
        myloop: WHILE (@Occurrences > 0)
        DO 
            SET @myValue = SUBSTRING_INDEX(@String, ',', 1);
            IF (@myValue != '') THEN
            /* my code... */
            ELSE
                LEAVE myloop; 
            END IF;
            SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
            IF (@occurrences = 0) THEN 
                LEAVE myloop; 
            END IF;
            SET @String = SUBSTRING(@String,LENGTH(SUBSTRING_INDEX(@String, ',', 1))+2);
        END WHILE;                  

   END $$

#4

I have done this, for when you don't have table values and so on:

我已经这样做了，当你没有表值时

select *
from(
    select c, SUBSTRING_INDEX(SUBSTRING_INDEX('1|2|5|6', '|', c+1), '|', -1) as name
    from(
        SELECT (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue) c
        FROM (
            SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16 
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
    ) as b
    WHERE c <= (CHAR_LENGTH('1|2|5|6') - CHAR_LENGTH(REPLACE('1|2|5|6', '|', '')))
) as a;

May not be the best answer, but works without aid of functions and procedures, no additional tables etc.

可能不是最好的答案，但是没有函数和过程的帮助，没有额外的表等等。

#5

select distinct
  SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4', ',', numbers.n), ',', -1) name
from
  (select @rownum := @rownum + 1 as n
from YourTable
cross join (select @rownum := 0) r
) numbers 
order by
   n

#6

If the text you are trying to split contains mutli-byte characters, this approach will break down because of LENGTH being calculated incorrectly. For such cases, the following version with CHAR_LENGTH instead of LENGTH works:

如果您试图分割的文本包含多字节字符，由于长度计算不正确，这种方法将会失败。对于这种情况，使用CHAR_LENGTH而不是LENGTH的以下版本可以工作:

CREATE DEFINER=`root`@`localhost` FUNCTION `strSplit`(
           `src` MEDIUMTEXT CHARACTER SET utf8, 
           `delim` VARCHAR(12), 
           `pos` INTEGER
          )
    RETURNS mediumtext
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  DECLARE output MEDIUMTEXT CHARACTER SET utf8;
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(src, delim, pos) ,  
              CHAR_LENGTH(SUBSTRING_INDEX(src, delim, pos - 1)) + 1) , delim , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END

Reference: http://www.shakedos.com/2011/Nov/23/mysql-split-string-function-fix-split_str.html

参考:http://www.shakedos.com/2011/Nov/23/mysql-split-string-function-fix-split_str.html

#7

Just because I really love resurrecting old questions:

因为我真的很喜欢让旧问题复活:

CREATE PROCEDURE `SPLIT_LIST_STR`(IN `INISTR` TEXT CHARSET utf8mb4, IN `ENDSTR` TEXT CHARSET utf8mb4, IN `INPUTSTR` TEXT CHARSET utf8mb4, IN `SEPARATR` TEXT CHARSET utf8mb4)
BEGIN
    SET @I = 1;
    SET @SEP = SEPARATR;
    SET @INI = INISTR;
    SET @END = ENDSTR;
    SET @VARSTR = REPLACE(REPLACE(INPUTSTR, @INI, ''), @END, '');
    SET @N = FORMAT((LENGTH(@VARSTR)-LENGTH(REPLACE(@VARSTR, @SEP, '')))/LENGTH(@SEP), 0)+1;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_table(P1 TEXT NULL);

    label1: LOOP
        SET @TEMP = SUBSTRING_INDEX(@VARSTR, @SEP, 1);
        insert into temp_table (`P1`) SELECT @TEMP;
        SET @I = @I + 1;
        SET @VARSTR = REPLACE(@VARSTR, CONCAT(@TEMP, @SEP), '');
        IF @N >= @I THEN
          ITERATE label1;
        END IF;
        LEAVE label1;
      END LOOP label1;
    SELECT * FROM temp_table;
    END

Which Produces:

生产:

P1
1
2
3
4

When Using CALL SPLIT_LIST_STR('("', '")', '("1", "2", "3", "4")', '", "');

当使用调用SPLIT_LIST_STR(“(“”、“”)”,“(“1”、“2”、“3”、“4”)”、“”、“”);

I might pop up later to tide up the code a bit more! Cheers!

稍后，我可能会弹出以使代码更有意义!干杯!

#8

I found good solution for this

我找到了很好的解决方法

https://forums.mysql.com/read.php?10,635524,635529

https://forums.mysql.com/read.php?10,635524年、635529年

Thanks to Peter Brawley

感谢彼得·布劳利

Trick: massage a Group_Concat() result on the csv string into an Insert...Values... string:

技巧:将csv字符串上的Group_Concat()结果转换为Insert…值…字符串:

drop table if exists t;
create table t( txt text );
insert into t values('1,2,3,4,5,6,7,8,9');

drop temporary table if exists temp;
create temporary table temp( val char(255) );
set @sql = concat("insert into temp (val) values ('", replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");
prepare stmt1 from @sql;
execute stmt1;
select distinct(val) from temp;
+------+
| val  |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
| 7    |
| 8    |
| 9    |
+------+

Also if you just want to join some table to list of id you can use LIKE operator. There is my solution where I get list of id from blog post urls, convert them to comma separated list started and finished with commas and then join related products by id list with LIKE operator.

另外，如果您只是想要将某个表连接到id列表，可以使用LIKE操作符。我的解决方案是从博客文章url中获取id列表，将它们转换为逗号分隔的列表，以逗号开头和结束，然后使用LIKE操作符通过id列表连接相关产品。

SELECT b2.id blog_id, b2.id_list, p.id
FROM (
    SELECT b.id,b.text,
    CONCAT(
        ",",
            REPLACE(
                EXTRACTVALUE(b.text,'//a/@id')
                , " ", ","
            )
        ,","
    ) AS id_list
    FROM blog b
) b2
LEFT JOIN production p ON b2.id_list LIKE CONCAT('%,',p.id,',%')
HAVING b2.id_list != ''