在SQL中将字符串拆分为多行 [英] Split string into multiple rows in SQL

问题描述

我继承了一个数据库，在努力使它更整洁，更有用的过程中，我遇到了以下问题.

I have inherited a database, and in my efforts to make it cleaner and more useful, I have encountered the following problem.

将files列移动到单独的表后，我现在的任务是将这些文件分开到不同的行中.请在下面查看我的示例.

After moving the files column to a seperate table, I now have the task of seperating out these files into different rows. Please see my example below.

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

我想要一条SQL语句，该语句可使表变成以下内容:

I would like an SQL statement that would make the table into the following:

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

是否必须删除原始条目以实现此目标并不重要，因此以下解决方案也是可以接受的:

It doesnt matter if the original entrys must be deleted to achieve this, so the following solution would also be acceptable:

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

基本上，我只需要在;上分割文件字符串即可；定界符和使用拆分字符串创建的新行.

Basically I just need the files string split on the ; delimiter and a new row created with the split strings.

您能提供的任何帮助将不胜感激.

Any help you can provide would be appreciated.

推荐答案

在PHP中(假设$ db是有效的数据库连接，而key是auto_increment):

In PHP (assuming $db is a valid db connection and key is auto_increment):

$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循环结束时运行它们，要么批处理它们，然后将它们写出来以供以后使用，不管它适合您的加载方式.

now $sql has an array of SQL statements to run - either run them at the end of the while loop, or batch them up, write them out for later, whatever fits your load pattern.

这篇关于在SQL中将字符串拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！