MySQL SQL 将值拆分为多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17942508/
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
SQL split values to multiple rows
提问by AFD
I have table :
我有桌子：
id | name
1 | a,b,c
2 | b
i want output like this :
我想要这样的输出：
id | name
1 | a
1 | b
1 | c
2 | b
回答by fthiella
If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:
如果您可以创建一个数字表，其中包含从 1 到要拆分的最大字段的数字，则可以使用如下解决方案：
select
tablename.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
numbers inner join tablename
on CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
id, n
Please see fiddle here.
请在此处查看小提琴。
If you cannot create a table, then a solution can be this:
如果您无法创建表，那么解决方案可以是这样的：
select
tablename.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
(select 1 n union all
select 2 union all select 3 union all
select 4 union all select 5) numbers INNER JOIN tablename
on CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
id, n
an example fiddle is here.
一个示例小提琴是here。
回答by Luv
I have take the reference from here with changed column name.
DELIMITER $$
CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER)
RETURNS VARCHAR(65000)
BEGIN
DECLARE output VARCHAR(65000);
SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
, LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
, delim
, '');
IF output = '' THEN SET output = null; END IF;
RETURN output;
END $$
CREATE PROCEDURE BadTableToGoodTable()
BEGIN
DECLARE i INTEGER;
SET i = 1;
REPEAT
INSERT INTO GoodTable (id, name)
SELECT id, strSplit(name, ',', i) FROM BadTable
WHERE strSplit(name, ',', i) IS NOT NULL;
SET i = i + 1;
UNTIL ROW_COUNT() = 0
END REPEAT;
END $$
DELIMITER ;
回答by Andrey
My variant: stored procedure that takes table name, field names and delimiter as arguments. Inspired by post http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/
我的变体：将表名、字段名和分隔符作为参数的存储过程。灵感来自帖子http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/
delimiter $$
DROP PROCEDURE IF EXISTS split_value_into_multiple_rows $$
CREATE PROCEDURE split_value_into_multiple_rows(tablename VARCHAR(20),
id_column VARCHAR(20), value_column VARCHAR(20), delim CHAR(1))
BEGIN
DECLARE id INT DEFAULT 0;
DECLARE value VARCHAR(255);
DECLARE occurrences INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value VARCHAR(255);
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT tmp_table1.id, tmp_table1.value FROM
tmp_table1 WHERE tmp_table1.value IS NOT NULL AND tmp_table1.value != '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @expr = CONCAT('CREATE TEMPORARY TABLE tmp_table1 (id INT NOT NULL, value VARCHAR(255)) ENGINE=Memory SELECT ',
id_column,' id, ', value_column,' value FROM ',tablename);
PREPARE stmt FROM @expr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TEMPORARY TABLE IF EXISTS tmp_table2;
CREATE TEMPORARY TABLE tmp_table2 (id INT NOT NULL, value VARCHAR(255) NOT NULL) ENGINE=Memory;
OPEN cur;
read_loop: LOOP
FETCH cur INTO id, value;
IF done THEN
LEAVE read_loop;
END IF;
SET occurrences = (SELECT CHAR_LENGTH(value) -
CHAR_LENGTH(REPLACE(value, delim, '')) + 1);
SET i=1;
WHILE i <= occurrences DO
SET splitted_value = (SELECT TRIM(SUBSTRING_INDEX(
SUBSTRING_INDEX(value, delim, i), delim, -1)));
INSERT INTO tmp_table2 VALUES (id, splitted_value);
SET i = i + 1;
END WHILE;
END LOOP;
SELECT * FROM tmp_table2;
CLOSE cur;
DROP TEMPORARY TABLE tmp_table1;
END; $$
delimiter ;
Usage example (normalization):
用法示例（归一化）：
CALL split_value_into_multiple_rows('my_contacts', 'contact_id', 'interests', ',');
CREATE TABLE interests (
interest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
interest VARCHAR(30) NOT NULL
) SELECT DISTINCT value interest FROM tmp_table2;
CREATE TABLE contact_interest (
contact_id INT NOT NULL,
interest_id INT NOT NULL,
CONSTRAINT fk_contact_interest_my_contacts_contact_id FOREIGN KEY (contact_id) REFERENCES my_contacts (contact_id),
CONSTRAINT fk_contact_interest_interests_interest_id FOREIGN KEY (interest_id) REFERENCES interests (interest_id)
) SELECT my_contacts.contact_id, interests.interest_id
FROM my_contacts, tmp_table2, interests
WHERE my_contacts.contact_id = tmp_table2.id AND interests.interest = tmp_table2.value;
回答by Paul Spiegel
If the namecolumn were a JSON array (like '["a","b","c"]'), then you could extract/unpack it with JSON_TABLE()(available since MySQL 8.0.4):
如果该name列是一个 JSON 数组（如'["a","b","c"]'），那么您可以使用JSON_TABLE()（自 MySQL 8.0.4 起可用）提取/解压它：
select t.id, j.name
from mytable t
join json_table(
t.name,
'$[*]' columns (name varchar(50) path '$')
) j;
Result:
结果：
| id | name |
| --- | ---- |
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | b |
If you store the values in a simple CSV format, then you would first need to convert it to JSON:
如果您以简单的 CSV 格式存储值，那么您首先需要将其转换为 JSON：
select t.id, j.name
from mytable t
join json_table(
replace(json_array(t.name), ',', '","'),
'$[*]' columns (name varchar(50) path '$')
) j
Result:
结果：
| id | name |
| --- | ---- |
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | b |
回答by user9526573
The original question was for MySQL and SQL in general. The example below is for the new versions of MySQL. Unfortunately, a generic query that would work on any SQL server is not possible. Some servers do no support CTE, others do not have substring_index, yet others have built-in functions for splitting a string into multiple rows.
最初的问题是针对 MySQL 和 SQL。以下示例适用于新版本的 MySQL。不幸的是，无法在任何 SQL 服务器上使用通用查询。有些服务器不支持 CTE，有些服务器没有 substring_index，还有一些服务器具有将字符串拆分为多行的内置函数。
--- the answer follows ---
--- 答案如下---
Recursive queries are convenient when the server does not provide built-in functionality. They can also be the bottleneck.
当服务器不提供内置功能时，递归查询很方便。它们也可能是瓶颈。
The following query was written and tested on MySQL version 8.0.16. It will not work on version 5.7-. The old versions do not support Common Table Expression (CTE) and thus recursive queries.
以下查询是在 MySQL 8.0.16 版上编写和测试的。它不适用于 5.7- 版。旧版本不支持公共表表达式 (CTE)，因此不支持递归查询。
with recursive
input as (
select 1 as id, 'a,b,c' as names
union
select 2, 'b'
),
recurs as (
select id, 1 as pos, names as remain, substring_index( names, ',', 1 ) as name
from input
union all
select id, pos + 1, substring( remain, char_length( name ) + 2 ),
substring_index( substring( remain, char_length( name ) + 2 ), ',', 1 )
from recurs
where char_length( remain ) > char_length( name )
)
select id, name
from recurs
order by id, pos;
回答by Harry Marx
Here is my attempt: The first select presents the csv field to the split. Using recursive CTE, we can create a list of numbers that are limited to the number of terms in the csv field. The number of terms is just the difference in the length of the csv field and itself with all the delimiters removed. Then joining with this numbers, substring_index extracts that term.
这是我的尝试：第一个选择将 csv 字段呈现给拆分。使用递归 CTE，我们可以创建一个数字列表，该列表仅限于 csv 字段中的术语数量。术语的数量只是 csv 字段的长度与它本身的长度之差，其中删除了所有分隔符。然后加入这些数字， substring_index 提取该术语。
with recursive
T as ( select 'a,b,c,d,e,f' as items),
N as ( select 1 as n union select n + 1 from N, T
where n <= length(items) - length(replace(items, ',', '')))
select distinct substring_index(substring_index(items, ',', n), ',', -1)
group_name from N, T
回答by Imanez
CREATE PROCEDURE `getVal`()
BEGIN
declare r_len integer;
declare r_id integer;
declare r_val varchar(20);
declare i integer;
DECLARE found_row int(10);
DECLARE row CURSOR FOR select length(replace(val,"|","")),id,val from split;
create table x(id int,name varchar(20));
open row;
select FOUND_ROWS() into found_row ;
read_loop: LOOP
IF found_row = 0 THEN
LEAVE read_loop;
END IF;
set i = 1;
FETCH row INTO r_len,r_id,r_val;
label1: LOOP
IF i <= r_len THEN
insert into x values( r_id,SUBSTRING(replace(r_val,"|",""),i,1));
SET i = i + 1;
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
set found_row = found_row - 1;
END LOOP;
close row;
select * from x;
drop table x;
END
回答by Tawonga Donnell Msiska
Here is my solution
这是我的解决方案
-- Create the maximum number of words we want to pick (indexes in n)
with recursive n(i) as (
select
1 i
union all
select i+1 from n where i < 1000
)
select distinct
s.id,
s.oaddress,
-- n.i,
-- use the index to pick the nth word, the last words will always repeat. Remove the duplicates with distinct
if(instr(reverse(trim(substring_index(s.oaddress,' ',n.i))),' ') > 0,
reverse(substr(reverse(trim(substring_index(s.oaddress,' ',n.i))),1,
instr(reverse(trim(substring_index(s.oaddress,' ',n.i))),' '))),
trim(substring_index(s.oaddress,' ',n.i))) oth
from
app_schools s,
n
