mysql拆分字段返回空,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.

解决方案

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

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.

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

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

Code for loop syntax:

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