mysql - 如何使用 MySQL View 或过程模拟 Explode() 函数

Question

我有一个 MySQL 表，其中数据如下所示

Struct : 
Id | Field N°1 

Data : 
1 | "File1.doc, File1.xls, File1.pdf"
2 | "File2.doc, File2.xls, File2.pdf"

我希望通过 MySQL VIEW 得到什么:

1 | "File1.doc"
1 | "File2.xls"
1 | "File1.pdf"
2 | "File2.doc"
2 | "File1.xls"
2 | "File2.pdf"

有人有什么想法吗？我不想抛出 Php 脚本，我需要用 SQL 来完成。

Answer 1

<?php

$data = <<<STR
1 | "File1.doc, File1.xls, File1.pdf"
2 | "File2.doc, File2.xls, File2.pdf"
STR;

$rows = explode("\n", $data);

$result = array();

foreach ($rows as $row)
{
    $num = $row[0];
    $str = substr($row, 5, strlen($row) - 6);
    $files = explode(', ', $str);
    foreach ($files as $file)
    {
        $result[] = $num.' | "'.$file.'"';
    }
}

foreach ($result as $file)
{
    echo '<p>'.$file.'</p>';
}
?>

结果将是:

1 | "File1.doc"

1 | "File1.xls"

1 | "File1.pdf"

2 | "File2.doc"

2 | "File2.xls"

2 | "File2.pdf"