PHP、MySQL - 当使用重复键时，从输入数组追加到单元格

Question

我有一个 MySQL 表，类似于

sometable

id   |   val1   |   val2   |   val3
 1   |    ...   |    ...   |    ... 
 2   |    ...   |    ...   |    ... 
 3   |    ...   |    ...   |    ... 

其中 val1 设置为 UNIQUE 键。

我同时插入多行。数字是可变的，因此查询按以下方式构造:

for(...building array earlier in code...){
    $arr[] = array('val1' => $somVal[$i][0], 
                    'val2' => $somVal[$i][1], 
                    'val3' => $somVal[$i][2]);
}

$datafields = array('val1', 'val2', 'val3');

$insert_values = array();
$qms = array();

foreach($arr as $d){
    $qms[] = '('  . dbplaceholders('?', sizeof($d)) . ')';
    $insert_values = array_merge($insert_values, array_values($d));
}

$db = new PDO(...);
$db->beginTransaction();
$stmt = $db->prepare('INSERT INTO sometable (' . implode(',', $datafields ) . ') 
                 VALUES ' . implode(',', $qms) . 
                 ' ON DUPLICATE KEY UPDATE val3 = "New Val3 Value"');

if($stmt->execute($insert_values)){
    $db->commit();
}

地点:

function dbplaceholders($text, $count=0, $separator=","){
    $result = array();
    if($count > 0){
        for($x=0; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}

这很好用。当 val1 重复时，val3 中的值更改为“New Val3 Value”。

但这并不是我想要的。

我想要的是将新的 val3 附加到当前的末尾

类似于:

ON DUPLICATE KEY UPDATE val3 = val3 + ";" + $newVal3Val

但问题是 $newVal3Val 被埋在了 $insert_values 中。

当找到重复项时，如何将新的 val3 附加到当前值，并使用分号分隔符？

Answer 1

http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values

In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in the ON DUPLICATE KEY UPDATE clause of INSERT statements and returns NULL otherwise.

您应该能够使用 values() 函数引用您在 $datafields 中使用的值

SQL:ON DUPLICATE KEY UPDATE val3 = CONCAT_WS(';',val3,VALUES(val3))