如何在mysql中使用重复键更新时无法更新的列

I'm working on a query that has to deal with insert records and update the column values if primary key is duplicate.

我正在处理一个必须处理插入记录的查询,并在主键重复时更新列值。

In my case column name and values are coming as JSON object like below

在我的情况下,列名称和值将作为JSON对象,如下所示

{
    "col1": "val1",
    "col2": "val2",
    .....
}

My table has 5 columns and in the JSON object I may have only 2 or 3 columns out of 5.

我的表有5列,在JSON对象中,我可能只有2列或3列。

Now How do I nullify the columns which are not updated with the query?

现在如何使未使用查询更新的列无效?

This is what I've tried thus far.

这是我到目前为止所尝试过的。

$columns = implode(', ', array_keys($requirement));
$values = array_values($requirement);
$placeholders = implode(', ', array_fill(0, count($values), '?'));

$updatedReq = $requirement;
unset($updatedReq['requirement_id']);
unset($updatedReq['buyer_id']);

$updated = array_keys($updatedReq);
array_walk($updated, function(&$value, $key) { $value .= '= ?'; });
$updatedPlaceholders = implode(', ', $updated);
$updatedVal = array_values($updatedReq);

$finalVal = array_merge($values, $updatedVal); 

$sql = "INSERT INTO requirements ($columns) VALUES ($placeholders) ".
                    "ON DUPLICATE KEY UPDATE $updatedPlaceholders";
$db->query($sql);
$db->executeWithArray($finalVal);

$requirement is an associative array with keys as column names and values are the values to be updated in the table

$ requirement是一个关联数组,其中键作为列名,值是要在表中更新的值

Table Structure

CREATE TABLE requirement(
       id integer PRIMARY KEY AUTO_INCREMENT,
       col1 varchar(20) DEFAULT NULL,
       col2 varchar(20) DEFAULT NULL,
       col3 varchar(20) DEFAULT NULL,
       col4 varchar(20) DEFAULT NULL,
       col5 varchar(20) DEFAULT NULL,
       created datetime DEFAULT CURRENT_TIMESTAMP,
       updated datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

NOTE: REPLACE INTO is not an option because one of the columns have a date value which shouldn't not be modified while updating the value.

注意:REPLACE INTO不是一个选项,因为其中一列具有日期值,在更新值时不应修改该日期值。

1 个解决方案

#1

Extend your ON DUPLICATE KEY to include all the columns you want to affect, using the VALUES() function like so:

使用VALUES()函数扩展ON DUPLICATE KEY以包含您想要影响的所有列,如下所示:

ON DUPLICATE KEY UPDATE
    `val1` = VALUES(`val1`),
    `val2` = VALUES(`val2`),
    `val3` = VALUES(`val3`),
    `val4` = VALUES(`val4`),
    `val5` = VALUES(`val5`)

If your INSERT did not pass a value for a given column, then VALUES(`unpassed_column`) will yield NULL and this will be the newly saved value for that row.

如果您的INSERT未传递给定列的值,则VALUES(`unpassed_column`)将产生NULL,这将是该行新保存的值。

#1