时间戳

首页 > 解决方案 > 将值拆分为多行

mysql - 将值拆分为多行

问题描述

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

测试位置（表 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;

我的问题

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

感谢大家提供的任何指导。

标签： mysqlsqlstringcsvunnest

解决方案

您使用数字表走在正确的轨道上。您应该从添加更多行开始，使其匹配（或超过）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）：

编号 | 状态 | 姓名 | 姓名        
-: | :-------- | :---------------------------------------- | :-----------
 1 | 阿拉巴马州 | 伯明翰；亨茨维尔；莫比尔；蒙哥马利 | 伯明翰  
 1 | 阿拉巴马州 | 伯明翰；亨茨维尔；莫比尔；蒙哥马利 | 亨茨维尔  
 1 | 阿拉巴马州 | 伯明翰；亨茨维尔；莫比尔；蒙哥马利 | 移动的      
 1 | 阿拉巴马州 | 伯明翰；亨茨维尔；莫比尔；蒙哥马利 | 蒙哥马利  
 2 | 佛罗里达 | 坦帕；杰克逊维尔；德斯廷 | 坦帕       
 2 | 佛罗里达 | 坦帕；杰克逊维尔；德斯廷 | 杰克逊维尔
 2 | 佛罗里达 | 坦帕；杰克逊维尔；德斯廷 | 德斯廷

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

推荐阅读