如何在 MySQL 存储过程中拆分逗号分隔的文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2182668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to split comma separated text in MySQL stored procedure
提问by Peter Stegnar
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, ','));
采纳答案by David Titarenco
回答by DarkSide
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
回答by Lee Fentress
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.
您可以在存储过程中使用准备好的语句来实现这一点。您可以将整个选择查询创建为变量内的字符串，然后将逗号分隔的字符串连接到其 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");
回答by uxnow
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);
回答by leonthelion
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:
将其复制到另外 2 个表：
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' 列。
回答by Eirik H
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 functionlike 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
回答by Andrew Paddison
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
回答by Nikolay Hristov
A bit strange but:
有点奇怪但是：
SET @i = 1;
set @str = 'a,b,c,d,e,f,g,h';
select temp.length into @length from
(select
ROUND(
(
LENGTH(dt.data)
- LENGTH( REPLACE (dt.data, ",", "") )
) / LENGTH(",")
)+1 AS length
from (select @str as data) dt
) temp;
SET @query = CONCAT('select substring_index(
substring_index(@str, '','', seq),
'','',
-1
) as letter from seq_', @i, '_to_',@length);
PREPARE q FROM @query;
EXECUTE q;
