MySQL用另一个表替换列中的逗号分隔值，查找旧值以替换新值 - MySQL replace comma separated values in column with another table find old value to replace with new

MySQL replace comma separated values in column with another table find old value to replace with new

MySQL表想要用另一个表值替换column中的逗号分隔值

我有两个表

表中第一个以逗号分隔值(热键)的列。

第2表已添加了&/ul>

我想用replace oldid来搜索表的第一列

动作表(1)

id    hotkey
===   ======
1    2,3,4,5
2    3,2,14,7
3    4,5,6,11
4    9,2,11,5
5    11,5,3,8

tempID表(二)

id  oldid   newid 
===  ===    === 
1    5      4  
2    7      6  
3    3      8  
4    9      12  
5    11     14

OUTPUT table (Desired)

id    hotkey
===   ======
1    2,8,4,4
2    8,2,14,6
3    4,4,6,14
4    12,2,14,4
5    14,4,8,8

我用PHP脚本解决如下:

//db access
    $dbhost = "localhost";
    $dbuser   = "root";
    $dbpassword = "";
    $database = "dbaname";
    $dbcon = mysql_connect($dbhost, $dbuser, $dbpassword) or die(mysql_error());
    mysql_select_db($database,$dbcon) or die ('>>>'.mysql_errno()."Error1 :".mysql_error());
    //find & Replace 
    $q=mysql_query("SELECT oid,nid FROM tempid") or die(mysql_error());
    while($fetch_com=mysql_fetch_array($q))   {  
        $oid=$fetch_com['oid'];
        $nid=$fetch_com['nid'];     
        mysql_query("UPDATE action SET hotkey=REPLACE(hotkey,',$oid,',',$nid,')");  
    }