php - INSERT ... ON DUPLICATE KEY UPDATE - 不更新特定字段？

Question

我正在使用 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' 字段。

Answer 1

给出 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();
}

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