如何在MySQL存储过程中分割逗号分隔的文本

How to split comma separated text (list of IDs) in MySQL stored procedure to use result in SQL "IN" statement.

如何在MySQL存储过程中分割逗号分隔的文本(id列表)，以使用SQL“in”语句中的结果。

SELECT * FROM table WHERE table.id IN (splitStringFunction(commaSeparatedData, ','));

7 个解决方案

#1

You can do it two ways:

你可以用两种方法:

SQL Library
SQL库
Natively with REGEXP
本机与REGEXP

#2

This is simple as hell for MySQL:

这对MySQL来说简直就是地狱:

SELECT * FROM table WHERE FIND_IN_SET(table.id, commaSeparatedData);

Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

参考:http://dev.mysql.com/doc/refman/5.0/en/string-functions.html # function_find-in-set

#3

You could use a prepared statement inside the stored procedure to achieve this. You can create the whole select query as a string inside a variable and then concatenate in the comma delimited string into its IN clause. Then you can make a prepared statement from the query string variable and execute it.

您可以在存储过程中使用一个准备好的语句来实现这一点。您可以将整个select查询创建为变量中的字符串，然后将逗号分隔的字符串连接到它的in子句中。然后，您可以从查询字符串变量中创建一条准备好的语句并执行它。

DELIMITER ;;
create procedure testProc(in listString varchar(255))

BEGIN

set @query = concat('select * from testTable where id in (',listString,');');
prepare sql_query from @query;
execute sql_query;

END
;;

DELIMITER ;

call testProc("1,2,3");

#4

You could try this MySql example. Before you use it, put some type safety checks in there (i.e. check id is integer, or match against regular expression before insert).

你可以试试这个MySql例子。在使用它之前，在其中放置一些类型安全检查(即检查id是整数，或者在插入之前与正则表达式匹配)。

 # BEGIN split statements ids
 DECLARE current_pos INT DEFAULT 1;
 DECLARE delim CHAR DEFAULT ',';
 DECLARE current CHAR DEFAULT '';
 DECLARE current_id VARCHAR(100) DEFAULT '';;
 CREATE TEMPORARY TABLE ids (`id` VARCHAR(100));
 split_ids: LOOP
  SET current = MID(statement_ids, current_pos, 1);
  IF (current_pos = LENGTH(statement_ids)) THEN
   IF current != delim THEN SET current_id = CONCAT(current_id,current); END IF;
   INSERT INTO ids(id) VALUES (current_id);
   LEAVE split_ids;
  END IF;
  IF current = delim THEN
   INSERT INTO ids(id) VALUES (current_id);
   SET current_id = '';
  ELSE
   SET current_id = CONCAT(current_id,current);
  END IF;
  SET current_pos = current_pos+1;
 END LOOP split_ids;
 # END split statement ids

 # to check ids are correct
 SELECT * FROM ids;

 # to use the ids:
 SELECT * FROM statements WHERE id IN (SELECT id FROM ids);

#5

OK, slightly "easier" but less geeky way for people like me:

好吧，对像我这样的人来说，稍微“简单”一点，但不那么古怪:

say you have one table 'combined_city_state' which looks like:

假设有一个表“combined_city_state”，它看起来如下:

'Chicago, Illinois'

copy that to 2 other tables:

复制到另外两个表:

CREATE TABLE city LIKE combined_city_state;
INSERT city SELECT * FROM combined_city_state;

CREATE TABLE state LIKE combined_city_state;
INSERT state SELECT * FROM combined_city_state;

You now have 3 tables with the same data as 'combined_city_state'.

现在有3个表的数据与“combined_city_state”相同。

Install this function:

安装这个功能:

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

Then apply this to each table to remove the extra index of data:

然后将此应用于每个表，以删除额外的数据索引:

UPDATE firms 
SET city = (SELECT SPLIT_STR((city), ',', 1));


UPDATE firms 
SET state = (SELECT SPLIT_STR((state), ',', 2));

This leaves you with one column of just cities, one of just states. You can now remove the original 'combined_city_state' column if you don't need anymore.

这就剩下了一列城市，一列州。如果不再需要，现在可以删除原来的“combined_city_state”列。

#6

I'm surprised this one-liner isn't properly mentioned here:

我很惊讶这句话在这里没有被恰当地提及:

SELECT * FROM table
WHERE id in (SELECT convert(int,Value) FROM dbo.Split(@list_string,',')

All you need is a Split SQL function like the one below which will come in handy in other ways as well:

您所需要的是一个拆分SQL函数，如下所示，它在其他方面也很有用:

CREATE FUNCTION dbo.Split
(
    @List nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(

    Id int identity(1,1),
    Value nvarchar(100)
) 
AS  
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
    Insert Into @RtnValue (value)
    Select 
        Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
        Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End

Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))

Return
END

#7

I have parsed data with hyphens in it. The example below uses a fixed text string to demonstrate, just change the references to relevant column names in the table. I played for ages with a way to ensure it worked on codes with varying numbers of components and in the end decided to add the where clause. Most data you are trying to parse would have a fixed number of columns.

我用连字符解析了数据。下面的示例使用一个固定的文本字符串进行演示，只需将引用更改为表中的相关列名。我用了很长一段时间来确保它在不同数量的组件上工作，最后决定添加where子句。您试图解析的大多数数据都有固定的列数。

select
SUBSTRING_INDEX(TS,"-",1) as "1",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",2)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",2)))-1)) as "2",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",3)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",3)))-1)) as "3",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",4)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",4)))-1)) as "4",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",5)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",5)))-1)) as "5",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",6)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",6)))-1)) as "6",reverse(left(reverse(SUBSTRING_INDEX(TS,"-",7)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",7)))-1)) as "7",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",8)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",8)))-1)) as "8",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",9)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",9)))-1)) as "9",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",10)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",10)))-1)) as "10"
from (select "aaa-bbb-ccc-ddd-eee-fff-ggg-hhh-iii-jjj" as TS) as S
where (LENGTH(TS)-LENGTH(REPLACE(TS,'-',''))) =9

#1