插入..重复键更新时 - 不更新特定字段 - INSERT ... ON DUPLICATE KEY UPDATE - don't update specific field?

INSERT ... ON DUPLICATE KEY UPDATE - don't update specific field?

本文关键字：更新字段插入 | 更新日期: 2023-09-27

我正在使用MySQL查询来插入或更新记录（如果它已经存在）。

问题是，我不想更新我的'created'日期时间字段。

我的事件数据：

$event = [
    'userId'        => $this->userId,
    'googleEventId' => $googleEventId,
    'isAppointment' => $isAppointment,
    'eventTitle'       => $title,
    'eventLocation'    => $location,
    'eventDescription' => $description,
    'eventStart'    => $start,
    'eventEnd'      => $end,
    'created'       => date('Y-m-d H:i:s'), // This field should be ignored when updating
    'updated'       => date('Y-m-d H:i:s')
];

我正在使用此函数在重复键（$update参数）上插入或插入更新：

public function insert($table, $data, $update = 0)
{
    ksort($data);
    $fieldNames  = implode(', ', array_keys($data));
    $fieldValues = ':' . implode(', :', array_keys($data));
    $updateOnDuplicate = '';
    if ($update)
    {
        $updateOnDuplicate = ' ON DUPLICATE KEY UPDATE ';
        foreach ($data as $key=> $value) {
            $updateOnDuplicate .= "$key=:$key,";
        }
        $updateOnDuplicate = rtrim($updateOnDuplicate, ',');
    }
    $stmt = $this->prepare("INSERT INTO $table ($fieldNames) VALUES ($fieldValues) $updateOnDuplicate");
    foreach ($data as $key => $value)
    {
        $stmt->bindValue(":$key", $value);
    }
    $stmt->execute();
    return $stmt->rowCount();
}

我想我需要以某种方式检测此函数中的'created'字段。

给出if($key!='created'){}语句是解决方案，

public function insert($table, $data, $update = 0)
{
    ksort($data);
    $fieldNames  = implode(', ', array_keys($data));
    $fieldValues = ':' . implode(', :', array_keys($data));
    $updateOnDuplicate = '';
    if ($update)
    {
        $updateOnDuplicate = ' ON DUPLICATE KEY UPDATE ';
        foreach ($data as $key=> $value) {
            if($key!='created'){ // Here you can do what you want
            $updateOnDuplicate .= "$key=:$key,"; 
            } // Here you can do what you want
        }
        $updateOnDuplicate = rtrim($updateOnDuplicate, ',');
    }
    $stmt = $this->prepare("INSERT INTO $table ($fieldNames) VALUES ($fieldValues) $updateOnDuplicate");
    foreach ($data as $key => $value)
    {
        $stmt->bindValue(":$key", $value);
    }
    $stmt->execute();
    return $stmt->rowCount();
}

现在，您可以运行此修复代码。祝你好运