MySQL 将逗号分隔的字符串拆分为临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11835155/
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
MySQL Split Comma Separated String Into Temp Table
提问by Mike Flynn
Can you parse a comma separated string into a temp table in MySQL using RegEx?
您可以使用 RegEx 将逗号分隔的字符串解析为 MySQL 中的临时表吗？
'1|2|5|6' into temp table with 4 rows.
回答by
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.
MySQL 没有拆分字符串功能，因此您必须进行变通。使用上面答案页面上列出的方法之一拆分数据后，您可以对数据执行任何操作。
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
你可以循环那个自定义函数并在它返回空时中断，你必须玩和学习一些语法（或者至少我会），但 mysql 中 FOR 循环的语法在这里： 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, '');
(Credit: https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/)
（来源：https: //blog.fedecarg.com/2009/02/22/mysql-split-string-function/）
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? :(
如果未找到匹配项，则应返回 '' ，因此如果未找到匹配项，则中断循环。这将允许您仅使用 mysql 解析拆分字符串并将插入查询运行到临时表中。但是为什么不直接使用像 php 这样的脚本语言来完成这种工作呢？:(
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 $$
回答by Alexey Muravyov
I found good solution for this
我找到了很好的解决方案
https://forums.mysql.com/read.php?10,635524,635529
https://forums.mysql.com/read.php?10,635524,635529
Thanks to Peter Brawley
感谢彼得·布劳利
Trick: massage a Group_Concat() result on the csv string into an Insert...Values... string:
技巧：将 csv 字符串上的 Group_Concat() 结果转化为 Insert...Values... 字符串：
drop table if exists t;
create table t( txt text );
insert into t values('1,2,3,4,5,6,7,8,9');
drop temporary table if exists temp;
create temporary table temp( val char(255) );
set @sql = concat("insert into temp (val) values ('", replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");
prepare stmt1 from @sql;
execute stmt1;
select distinct(val) from temp;
+------+
| val |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+------+
Also if you just want to join some table to list of id you can use LIKE operator. There is my solution where I get list of id from blog post urls, convert them to comma separated list started and finished with commas and then join related products by id list with LIKE operator.
此外，如果您只想将某个表加入 id 列表，您可以使用 LIKE 运算符。在我的解决方案中，我从博客文章 url 获取 id 列表，将它们转换为逗号分隔列表，以逗号开头和结尾，然后使用 LIKE 运算符通过 id 列表加入相关产品。
SELECT b2.id blog_id, b2.id_list, p.id
FROM (
SELECT b.id,b.text,
CONCAT(
",",
REPLACE(
EXTRACTVALUE(b.text,'//a/@id')
, " ", ","
)
,","
) AS id_list
FROM blog b
) b2
LEFT JOIN production p ON b2.id_list LIKE CONCAT('%,',p.id,',%')
HAVING b2.id_list != ''
回答by Guzzo Walter
DELIMITER $$
CREATE PROCEDURE SPLIT_VALUE_STRING()
BEGIN
SET @String = '1,22,333,444,5555,66666,777777';
SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
myloop: WHILE (@Occurrences > 0)
DO
SET @myValue = SUBSTRING_INDEX(@String, ',', 1);
IF (@myValue != '') THEN
/* my code... */
ELSE
LEAVE myloop;
END IF;
SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
IF (@occurrences = 0) THEN
LEAVE myloop;
END IF;
SET @String = SUBSTRING(@String,LENGTH(SUBSTRING_INDEX(@String, ',', 1))+2);
END WHILE;
END $$
回答by Rui Costa
I have done this, for when you don't have table values and so on:
我已经这样做了，因为当你没有表值等时：
select *
from(
select c, SUBSTRING_INDEX(SUBSTRING_INDEX('1|2|5|6', '|', c+1), '|', -1) as name
from(
SELECT (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue) c
FROM (
SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
) as b
WHERE c <= (CHAR_LENGTH('1|2|5|6') - CHAR_LENGTH(REPLACE('1|2|5|6', '|', '')))
) as a;
May not be the best answer, but works without aid of functions and procedures, no additional tables etc.
可能不是最好的答案，但无需功能和过程的帮助，也无需额外的表格等即可工作。
回答by Sunil
select distinct
SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4', ',', numbers.n), ',', -1) name
from
(select @rownum := @rownum + 1 as n
from YourTable
cross join (select @rownum := 0) r
) numbers
order by
n
回答by Wasif
You can use regular expression in MySQL to specify a pattern for a complex search, you cannot parse the strings.
您可以在 MySQL 中使用正则表达式为复杂搜索指定模式，您无法解析字符串。
But you can build INSERT query with the help of REPLACE and CONCATENATE to save data to temp table.
但是您可以在 REPLACE 和 CONCATENATE 的帮助下构建 INSERT 查询以将数据保存到临时表。
回答by sprockets
If the text you are trying to split contains mutli-byte characters, this approach will break down because of LENGTH being calculated incorrectly. For such cases, the following version with CHAR_LENGTH instead of LENGTH works:
如果您尝试拆分的文本包含多字节字符，则此方法将因 LENGTH 计算错误而失效。对于这种情况，使用 CHAR_LENGTH 而不是 LENGTH 的以下版本有效：
CREATE DEFINER=`root`@`localhost` FUNCTION `strSplit`(
`src` MEDIUMTEXT CHARACTER SET utf8,
`delim` VARCHAR(12),
`pos` INTEGER
)
RETURNS mediumtext
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE output MEDIUMTEXT CHARACTER SET utf8;
SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(src, delim, pos) ,
CHAR_LENGTH(SUBSTRING_INDEX(src, delim, pos - 1)) + 1) , delim , '');
IF output = '' THEN SET output = null; END IF;
RETURN output;
END
Reference: http://www.shakedos.com/2011/Nov/23/mysql-split-string-function-fix-split_str.html
参考：http: //www.shakedos.com/2011/Nov/23/mysql-split-string-function-fix-split_str.html
回答by Diego Freitas Coelho
Just because I really love resurrecting old questions:
只是因为我真的很喜欢重温旧问题：
CREATE PROCEDURE `SPLIT_LIST_STR`(IN `INISTR` TEXT CHARSET utf8mb4, IN `ENDSTR` TEXT CHARSET utf8mb4, IN `INPUTSTR` TEXT CHARSET utf8mb4, IN `SEPARATR` TEXT CHARSET utf8mb4)
BEGIN
SET @I = 1;
SET @SEP = SEPARATR;
SET @INI = INISTR;
SET @END = ENDSTR;
SET @VARSTR = REPLACE(REPLACE(INPUTSTR, @INI, ''), @END, '');
SET @N = FORMAT((LENGTH(@VARSTR)-LENGTH(REPLACE(@VARSTR, @SEP, '')))/LENGTH(@SEP), 0)+1;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_table(P1 TEXT NULL);
label1: LOOP
SET @TEMP = SUBSTRING_INDEX(@VARSTR, @SEP, 1);
insert into temp_table (`P1`) SELECT @TEMP;
SET @I = @I + 1;
SET @VARSTR = REPLACE(@VARSTR, CONCAT(@TEMP, @SEP), '');
IF @N >= @I THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SELECT * FROM temp_table;
END
Which Produces:
产生：
P1
1
2
3
4
When Using CALL SPLIT_LIST_STR('("', '")', '("1", "2", "3", "4")', '", "');
使用时 CALL SPLIT_LIST_STR('("', '")', '("1", "2", "3", "4")', '", "');
I might pop up later to tide up the code a bit more! Cheers!
我可能会在稍后弹出以进一步完善代码！干杯!
