MySQL拆分逗号分隔的字符串到临时表中 [英] MySQL Split Comma Separated String Into Temp Table

问题描述

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

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

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

推荐答案

这与 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中FOR循环的语法在这里: http://www.roseindia.net/sql/mysql-example/for. shtml

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

您可以对其进行迭代，从而增加以下函数中的位置:

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

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

如果没有找到匹配项，应该返回''，因此如果没有找到匹配项，则中断循环.这将允许您仅使用mysql解析拆分字符串，然后将插入查询运行到临时表中.但是，伙计，为什么不仅仅使用像php这样的脚本语言来完成这种工作呢? :(

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? :(

循环语法代码:

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 $$

这篇关于MySQL拆分逗号分隔的字符串到临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！