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

2021-11-20 00:00:00 regex mysql

你能用 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 没有拆分字符串功能，因此您必须进行变通.使用上面答案页面上列出的方法之一拆分数据后，您可以对数据执行任何操作.

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

相关文章