如何拆分管道分隔列并将每个值插入新表一次?

下面是我使用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)");
    }
}

在发现没有正式的split函数之后,我只使用MySQL解决了这个问题,比如:

1: 我创建了strSplit函数

CREATE FUNCTION strSplit(x varchar(21845), delim varchar(255), pos int) returns varchar(255)
return replace(
replace(
substring_index(x, delim, pos),
substring_index(x, delim, pos - 1),
''
),
delim,
''
);

然后,我将新的标记插入到新表中(更改了实名和collumns,以保持简单)

INSERT IGNORE INTO tag (SELECT null, strSplit(`Tag`,'|',1) AS T FROM `old_venue` GROUP BY T)

冲洗和重复,每一个柱增加一个POS(在这种情况下,我最多有8个分离器)。

第三个得到关系

INSERT INTO `venue_tag_rel` 
(Select a.`venue_id`, b.`tag_id` from `old_venue` a, `tag` b 
     WHERE 
     (         
     a.`Tag` LIKE CONCAT('%|',b.`tag_nm`) 
     OR a.`Tag` LIKE CONCAT(b.`tag_nm`,'|%') 
     OR a.`Tag` LIKE CONCAT(CONCAT('%|',b.`tag_nm`),'|%') 
     OR  a.`Tag` LIKE b.`tag_nm`
     ) 
)