将字符串在SQL中拆分为多行

Question

我继承了一个数据库，在努力使其更加清洁和有用的过程中，我遇到了以下问题。

在将文件列移到单独的表后，我现在需要将这些文件分开成不同的行。请参见下面的示例。

key | jobid       | files                  |
--------------------------------------------
1     30012        file1.pdf;file2.pdf
2     30013        file3.pdf
3     30014        file4.pdf;file5.pdf;file6.pdf

我需要一条SQL语句，将表格转换成以下形式：

key | jobid       | files                  |
--------------------------------------------
1     30012        file1.pdf
2     30013        file3.pdf
3     30014        file4.pdf
4     30012        file2.pdf
5     30014        file5.pdf
6     30014        file6.pdf

无论是否需要删除原始条目以实现此目的，以下解决方案也是可行的：

key | jobid       | files                  |
--------------------------------------------
4     30012        file1.pdf
5     30013        file3.pdf
6     30014        file4.pdf
7     30012        file2.pdf
8     30014        file5.pdf
9     30014        file6.pdf

基本上我只需要将文件字符串按照分号分割，并创建一个包含分割字符串的新行即可。如能提供任何帮助，将不胜感激。

- user1132038

---

我理解这是一个一次性的工作 - 是这样吗？ - Eugen Rieck

可能是Mysql字符串拆分的重复问题。 - Michał Powaga

是的，Eugen，这是一次性的工作。 - user1132038

Answer 1

在 PHP 中（假设 $db 是有效的数据库连接，key 是自动增量）：

$sql="select `key`, jobid, files from filestable where files like '%\\;%'";
$qry=mysql_query($sql,$db);

$sql=array();
while (true) {
  $row=mysql_fetch_row($qry);
  if (!$row) break;

  $key=$row[0];
  $jobid=$row[1];
  $files=explode(';',$row[2]);
  foreach ($files as $file) {
    $file=mysql_real_escape_string($file,$db);
    $sql[]="insert into filestable (jobid,files) values ($jobid,'$file')";
  }
  $sql[]="delete from filestables where `key`=$key";
}

现在 $sql 有一个要运行的 SQL 语句数组 - 可以在 while 循环结束时运行它们，或者将它们分批处理，写入以备后用，以适应您的负载模式。

- Eugen Rieck

---

除了在SQL语句中添加通配符和在插入行中添加 "之外，这个程序完美地工作了。非常感谢！ - user1132038

很好的发现，我已经更正了缺失的引号和通配符，抱歉，这是一个典型的“在打第n行时就开始思考第n+1行”的错误。 - Eugen Rieck

问题是如何在SQL中实现。 - reinierpost

Answer 2

我有完全相同的问题，找到了一篇可能有所帮助的文章，他们提供了MySQL脚本

create table books (tags varchar(1000));

insert into books values
    ('A, B, C, D'),
    ('D, E'),
    ('F'),
    ('G, G, H')
;

select
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(B.tags, ',', NS.n), ',', -1)) as tag
from (
  select 1 as n 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 union all
  select 10
) NS
inner join books B ON NS.n <= CHAR_LENGTH(B.tags) - CHAR_LENGTH(REPLACE(B.tags, ',', '')) + 1

我已经在这里的游乐场中添加了键名
https://www.db-fiddle.com/f/kLeLYVPmuoFtLEuAb8ihuE/0

参考资料:
https://www.holistics.io/blog/splitting-array-string-into-rows-in-amazon-redshift-or-mysql/

- Samuel Chan