您只更新更改的字段还是所有字段?

2022-01-17 00:00:00 sql-update mysql changelog

我想知道在更新记录以检索现有记录时是否值得服务器时间，循环检查更改的字段并仅将更改的字段放入更新查询中?(我正在使用 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.

在写/更新方法中:

$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;

相关文章