您只更新已更改的字段还是所有字段? [英] Do you only update the changed fields or all the fields?

问题描述

我想知道在更新记录以检索现有记录，遍历字段以检查更改并仅将更改后的字段放入更新查询中是否值得花费服务器时间? (我正在使用MySQL和PHP.)

I'm wondering if it's worth the server time when updating a record to retrieve the existing record, loop through the fields checking for changes and only putting the changed fields in the update query? (I am using MySQL & PHP.)

执行此操作的主要原因是为了更改日志而减少更新查询的大小.通常，查询可能有15个字段，但实际上只有2个字段被更改.然后，该查询也可以用于记录日志，因为它仅包含更改的字段，因此更易于解析.

The main reason for doing this is to reduce the size of the update query for change log purposes. Normally the query might have 15 fields, but only 2 fields are actually being changed. This query can then also be used for logging as it will only contain the changed fields and therefore is easier to parse.

我担心的是检索现有记录所花费的时间.

My concern is the time that it takes to retrieve the existing record.

还是有一种方法可以从MySQL检索它更新的字段?

Or is there a way to retrieve from MySQL which fields it updated?

推荐答案

我认为值得更改-但可能不值得在插入之前进行选择.

I think it's worth changing - but probably not worth doing a select before insert.

我只更新已更改的字段，这是我的DbEntity类的操作的一部分，该类遵循activerecord模式.这样做的花费很少，因为我保留了当前记录和原始记录-只需在加载记录时复制即可.

I only update the fields that have changed, it's part of the operation of my DbEntity class which follows an activerecord pattern. It costs little extra to do this because I hold the current record and original records -simply copying whenever a record is loaded.

原因很简短-并非真正的表现.您也可以通过在更新字段的旧值上添加where子句并抛出适当的错误来检查并发修改.

Reasons are brevity - not really performance. Also you can check for concurrent modification by adding a where clause on the old value of the updated fields and throw the appropriate error.

在write/update方法中:

In the write/update method:

$s1 = "";

foreach ($this->record as $key => $value)
{
    // only update fields that have been changed
    if ($value != $this->orig_record[$key])
    {
        $s1 .= $comma."`$key`='".mysql_real_escape_string($value)."'";
        $comma = ", ";
    }
}

$query = "UPDATE ".$this->table." SET $s1 where {$this->id_field}='".$this->get_keyfield()."'";
$query .= $this->extra_sql_update;
mysql_query($query);

$ar = mysql_affected_rows();
//
// the number of affected rows is actually those changed by the update operation, which will 
// either be zero, or 1. If the query affects more than one row then we have a problem.
if ($ar < 0 || $ar > 1)
{
    cbf_error("cbf_dbentity: {$this->table} :: only one row (not $ar) must be affected by an insert operation. $query",
      E_USER_ERROR);
}
else
{
    $new_id = $this->get_keyfield();

    GlobalEventBus::notify_all(new AuditLogSQL($this->table, "update", $query));

}

$this->orig_record = Array();

foreach ($this->record as $key => $value)
    $this->orig_record[$key] = $value;


//
// sanity check - ensure that what we have just written is actually there.

$this->load($new_id);

foreach ($this->orig_record as $key => $value)
    if (trim($this->record[$key]) != trim($value) 
        && (!$this->record[$key] == "0" && $value=""))
        cbf_error("cbf_dbentity: {$this->table} :: record differs during write after reload: field $key was \"$value\", after write it is now \"".
              $this->record[$key]."\"",E_USER_ERROR);

在加载方法中

$this->orig_record = Array();
foreach ($this->record as $key => $value)
    $this->orig_record[$key] = $value;

这篇关于您只更新已更改的字段还是所有字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！