logo标签列表

将 SQL 中的值拆分为多行

mysqlsqldelimitercsv
130

我有一个表：

id | name    
1  | a,b,c    
2  | b
我希望您能够提供这样的输出:
id | name    
1  | a    
1  | b    
1  | c    
2  | b
4
我同意你的观点，至少这是学术知识告诉我们的。然而，在我的公司中有很多情况他们会在单个列中以分隔符字符串的形式存储多个值，他们声称这种做法更高效（无需连接操作，且所需处理的成本不高）。我不知道哪种立场更应该被采纳。 - Veverke
1
如果你在JSON数据类型中存储原始的JSON格式，也会遇到这个问题。规范化结构更好，但它也有缺点，需要更多的前期开发，并且容易在响应更改时出现问题，如果你决定更改JSON中想要的内容，则必须重新开发。 - Chris Strickland
1
@GrahamGriffiths请专注于回答问题，而不是质疑需要。如果您要提供无关信息，请先回答问题。 - Michael Peterson
12个回答
177

如果您能够创建一个数字表，其中包含从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
请查看此处的fiddle：here。
如果您无法创建表格，则可以采用以下解决方案：
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

一个示例 fiddle 在这里。

20
@user2577038，你可以不用数字表来完成它，可以在这里看到 http://sqlfiddle.com/#!2/a213e4/1 - fthiella
3
需要注意的重要事项是，在第二个例子中，用逗号分隔的“fields”数量最多为5。您可以通过像这里提供的方法检查字符串中出现的次数：https://dev59.com/qGct5IYBdhLWcg3wHJzF。请继续向“numbers”内联视图添加“select [number] union all”子句，直到返回的行数停止增加。 - Bret Weinraub
1
像往常一样，我又偶然发现了你的有用代码。如果有人想要快速创建类似于这里显示的顶部块的表格，可以使用此例程的链接在这里。那个操作是针对单个字符串而不是它们的表格。 - Drew
1
一个 SQLite 版本会是什么样子？我得到了以下错误：could not prepare statement (1 no such function: SUBSTRING_INDEX)。 - Remi Sture
数字表格：不是一个好的解决方案（存储一系列数字有什么意义？） 第二个查询：当您确定最大计数时是一个好的解决方案...但对于包含100个逗号的字符串不适用。 - Guile
显示剩余6条评论
35
如果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：

在DB Fiddle上查看

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    |

View on DB Fiddle

1
我在使用MySQL 5.7.17的DataGrip中遇到了这个错误，有什么想法吗？我还尝试了从DB Fiddle复制粘贴完全相同的代码，在那里执行但本地不执行。[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( concat('[', replace(json_quote(t.name), ',', '","'), ']'), '$[*]' column' at line 3 - Ian Nastajus
怀疑需要升级到8.x版本。 - Ian Nastajus
1
@IanNastajus - 是的，你至少需要 MySQL 8.0.4。 - Paul Spiegel
...并已确认。唉，升级数据库可能会很麻烦。8.x安装程序只想将部分升级到最新的5.7.y版本，所以我意识到为了满足安装程序的要求，我必须首先卸载 5.x，然后使用完全相同的8.x安装程序重新安装 ... 唉 :眼睛翻白: ...幸运的是，它正常工作了，而且这只是我的个人项目，这次并没有充当大型生产系统的全职DBA... - Ian Nastajus
10
这里是我的翻译：
第一个选择器呈现了要拆分的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
请勿使用 UNION，因为 UNION 会返回 DISTINCT 值。使用 UNION ALL 会更好。 - Frank
MySQL 中没有 CTE。 - Kermit
3
@Kermit MySQL现在已经支持CTE达7年之久。 - Faizan Akram Dar
9

我参考了这里的内容，但更改了列名。

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 ;
6

我的变体：存储过程接受表名、字段名和分隔符作为参数。灵感来自于文章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;
1
写得非常好。经过一些更改，我能够将其整合到我的数据库中，以确保它处于第一范式。谢谢。 - raviabhiram
3

以上示例中需要不断添加“select number union all”，如果需要大量拆分，则可能出现问题。

    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
2

这里有一个技巧。数字20是逗号分隔列表中最大值的数量。

我们使用单个查询，没有存储过程。

如果tbl表格的行数超过了单个逗号分隔列表中的最大值数量，则可以从查询中删除“inner join tbl a inner join tbl c”部分。我添加这个是因为只有2行。

CREATE TABLE tbl(id int NOT NULL,name varchar(50),PRIMARY KEY (`id`));
insert into tbl values(1, 'a,b,c'), (2, 'd');


select id ,SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', k.n), ',', -1) as name
from tbl
INNER JOIN (
    SELECT * 
      FROM (
         SELECT @n:=@n+1 AS n
           FROM tbl inner join tbl a inner join tbl c
           INNER JOIN (SELECT @n:=0) AS _a
      ) AS _a WHERE _a.n <= 20
)AS k ON k.n <= LENGTH(name) - LENGTH(replace(name, ',','')) + 1
order by id

这是一种提取逗号分隔列表中第n个值的技巧：
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', k.n), ',', -1)
1
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
1
原始问题是关于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 count(1) from tablename）都会挂起或花费非常长的时间。我必须关闭 MySQL Workbench 并重新打开才能避免后续查询挂起。此外，我想使用这个解决方案将结果插入到一个新表中。然而，如果你的逗号分隔值中有 NULL 值，这个解决方案将无法工作。我仍然会使用 @fthiella 提供的解决方案，但很高兴找到了这个解决方案。 - kimbaudi
顺便说一下，我在一个拥有近600万条记录的表格上使用MySQL 8.0.16运行了这个查询。 - kimbaudi
0

这是我的解决方案

-- 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 中没有 CTE。 - Kermit
自2018年发布的MySQL 8.0版本开始，支持CTE。 - Lexius
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接