SQL 将值拆分为多行

我有桌子:

id | name
1  | a,b,c
2  | b

i want output like this :

id | name
1  | a
1  | b
1  | c
2  | b
259322 次浏览
小开

我已经从这里使用更改了列名的引用。

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 ;
小开
最佳答案

如果可以创建一个数字表，其中包含从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

请看小提琴 给你。

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 给你.

小开 
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
小开

我的变体: 以表名、字段名和分隔符作为参数的存储过程。灵感来自后 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 ;

使用示例(规范化) :

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;
小开

这是我的解决办法

-- 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
小开

最初的问题通常是针对 MySQL 和 SQL 的。下面的示例适用于 MySQL 的新版本。不幸的是，在任何 SQL 服务器上都可以工作的通用查询是不可能的。有些服务器不支持 CTE，有些服务器不支持 substring _ index，还有些服务器具有内置函数，可以将字符串分割成多行。

- 答案如下

当服务器不提供内置功能时，递归查询非常方便，它们也可能成为瓶颈。

下面的查询是在 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;
小开

下面是我的尝试: 第一个 select 将 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
小开

如果 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;

结果:

| id  | name |
| --- | ---- |
| 1   | a    |
| 1   | b    |
| 1   | c    |
| 2   | b    |

数据库小提琴视图

如果以简单的 CSV 格式存储值，那么首先需要将其转换为 JSON:

select t.id, j.name
from mytable t
join json_table(
replace(json_array(t.name), ',', '","'),
'$[*]' columns (name varchar(50) path '$')
) j

结果:

| id  | name |
| --- | ---- |
| 1   | a    |
| 1   | b    |
| 1   | c    |
| 2   | b    |

数据库小提琴视图

小开

最佳实践。 Result:

SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX('ab,bc,cd',',',help_id+1),',',-1) AS oid
FROM
(
SELECT @xi:=@xi+1 as help_id from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT @xi:=-1) xc0
) a
WHERE
help_id < LENGTH('ab,bc,cd')-LENGTH(REPLACE('ab,bc,cd',',',''))+1

首先，创建一个数字表:

SELECT @xi:=@xi+1 as help_id from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT @xi:=-1) xc0;

| help_id  |
| --- |
| 0   |
| 1   |
| 2   |
| 3   |
| ...   |
| 24   |

Second, just split the str:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('ab,bc,cd',',',help_id+1),',',-1) AS oid
FROM
numbers_table
WHERE
help_id < LENGTH('ab,bc,cd')-LENGTH(REPLACE('ab,bc,cd',',',''))+1

| oid  |
| --- |
| ab   |
| bc   |
| cd   |
小开 
SELECT id, unnest(string_to_array(name, ',')) AS names
FROM datatable

希望这个对你有帮助: D

小开

Because you have to keep adding "select number union all" in the example above which can be a issue if you need a large number of splits.

    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

我决定一个更好的方法是这样做，即只为每个数字增加一个数字行。下面的例子是好的为1-1000添加另一行使它好的为1-10000等等。

    select
tablename.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from(SELECT @row := @row + 1 AS n FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as t3,
(SELECT @row:=0) as numbers)as numbers INNER JOIN tablename
on CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
id, n

提交答案