sql - 如何分割以竖线分隔的列并将每个值插入到新表一次？

我有一个旧的数据库，其中有大量记录（或多或少）只有一个标签列（标签是用管道分隔的），看起来像这样：

    Breakfast
    Breakfast|Brunch|Buffet|Burger|Cakes|Crepes|Deli|Dessert|Dim Sum|Fast Food|Fine Wine|Spirits|Kebab|Noodles|Organic|Pizza|Salad|Seafood|Steakhouse|Sushi|Tapas|Vegetarian
    Breakfast|Brunch|Buffet|Burger|Deli|Dessert|Fast Food|Fine Wine|Spirits|Noodles|Pizza|Salad|Seafood|Steakhouse|Vegetarian
    Breakfast|Brunch|Buffet|Cakes|Crepes|Dessert|Fine Wine|Spirits|Salad|Seafood|Steakhouse|Tapas|Teahouse
    Breakfast|Brunch|Burger|Crepes|Salad
    Breakfast|Brunch|Cakes|Dessert|Dim Sum|Noodles|Pizza|Salad|Seafood|Steakhouse|Vegetarian
    Breakfast|Brunch|Cakes|Dessert|Dim Sum|Noodles|Pizza|Salad|Seafood|Vegetarian
    Breakfast|Brunch|Deli|Dessert|Organic|Salad
    Breakfast|Brunch|Dessert|Dim Sum|Hot Pot|Seafood
    Breakfast|Brunch|Dessert|Dim Sum|Seafood
    Breakfast|Brunch|Dessert|Fine Wine|Spirits|Noodles|Pizza|Salad|Seafood
    Breakfast|Brunch|Dessert|Fine Wine|Spirits|Salad|Vegetarian

有没有一种方法可以只使用MySQL检索每个标记并将其插入到新表中？

最佳答案

下面是我使用PHP的尝试…，我想用一个聪明的MySQL查询会更有效。我也把这段感情放在那里了。没有转义和错误检查。

$rs = mysql_query('SELECT `venue_id`, `tag` FROM `venue` AS a');
while ($row = mysql_fetch_array($rs)) {
    $tag_array = explode('|',$row['tag']);
    $venueid = $row['venue_id'];
    foreach ($tag_array as $tag) {
        $rs2 = mysql_query("SELECT `tag_id` FROM `tag` WHERE tag_nm = '$tag'");
        $tagid = 0;
        while ($row2 = mysql_fetch_array($rs2)) $tagid = $row2['tag_id'];
        if (!$tagid) {
            mysql_execute("INSERT INTO `tag` (`tag_nm`) VALUES ('$tag')");
            $tagid = mysql_insert_id;
        }
        mysql_execute("INSERT INTO `venue_tag_rel` (`venue_id`, `tag_id`) VALUES ($venueid, $tagid)");
    }
}

关于sql - 如何分割以竖线分隔的列并将每个值插入到新表一次？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/4201567/