将值拆分为多个行

Question

以下是我在数据库mavenmovies中的两张表格。

testLocation（表格 1）

CREATE TABLE mavenmovies.testLocation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    State varchar(255),
    name varchar(255)
);
INSERT INTO testLocation (State, name)
VALUES 
('Alabama','Birmingham;Huntsville;Mobile;Montgomery'),
('Florida','Tampa;Jacksonville;Destin');

数字(表2)

CREATE TABLE mavenmovies.numbers (
  n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2);

这是我想要查询的内容

这是我当前正在查询的内容（请参见下面的我的当前查询/尝试）

我的当前查询/尝试

select
  testLocation.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(testLocation.name, ';', numbers.n), ';', -1) name
from
    numbers, testLocation;

我的问题

如何扩展查询以处理每行未知数量的分隔值？例如，一行可能有四个、二十个或零个分隔值。

感谢您提供的任何指导。

- Tony Pendleton

---

MySQL。抱歉。谢谢！ - Tony Pendleton

那是一個糟糕的SQL設計。 - nacho

@nacho，我并没有设计这个数据库，但是感谢你指出来。 - Tony Pendleton

将该列拆分为行。当您搜索MySQL拆分逗号分隔时，您会找到很多示例。 - nbk

你的MySQL服务器的确切版本是什么？ - Akina

Answer 1

您正在使用数字表格的正确方法。您应该首先添加更多行，以使其匹配（或超过）CSV列表中可能存在的最大元素数。

然后，您可以使用连接条件仅为每个名称生成相关数量的行。

select
    t.*,
    substring_index(substring_index(t.name, ';', n.n), ';', -1) name
from numbers n
inner join testLocation t   
    on n <= length(t.name) - length(replace(t.name, ';', '')) + 1

点此查看在 DB Fiddle 上的演示（我将数字扩展到了8）：

id | State   | name                                    | name        
-: | :------ | :-------------------------------------- | :-----------
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Birmingham  
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Huntsville  
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Mobile      
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Montgomery  
 2 | Florida | Tampa;Jacksonville;Destin               | Tampa       
 2 | Florida | Tampa;Jacksonville;Destin               | Jacksonville
 2 | Florida | Tampa;Jacksonville;Destin               | Destin      

请注意，正如其他人已经评论的那样，在数据库中存储 CSV 列表是不好的做法，几乎总是应该避免。推荐相关阅读：在数据库列中存储分隔列表真的很糟糕吗？

- GMB