Knex.js(PostgreSQL)：仅数据实际变更时返回update操作计数

我希望更新数据库列时，仅当数据确实发生变更时，从Knex.js的.update()方法获取大于0的计数，以此判断更新是否实际执行。目前即使数据库中的值与待更新的目标值一致，该方法也总是返回1。

当前代码如下：

const updatedRowsCount = await getKnexWithSchema<JobTableColumns, JobTableColumns[]>(trx)
  .from(JOBS_TABLE)
  .whereIn('uuid', uuids)
  .whereNotIn('status', [JobStatus.SUCCESS, JobStatus.ABORTED, JobStatus.FAILED])
  .where(convertJobTypeToDB(where || {}))
  .update(convertJobTypeToDB(updatedData));

方法一：添加字段差异过滤条件 #

在更新语句的where子句中加入字段值不匹配的判断，确保只有当待更新字段与数据库现有值不同时才执行更新。

const dbUpdatedData = convertJobTypeToDB(updatedData);
const updatedRowsCount = await getKnexWithSchema<JobTableColumns, JobTableColumns[]>(trx)
  .from(JOBS_TABLE)
  .whereIn('uuid', uuids)
  .whereNotIn('status', [JobStatus.SUCCESS, JobStatus.ABORTED, JobStatus.FAILED])
  .where(convertJobTypeToDB(where || {}))
  // 动态添加字段差异判断，多个字段用OR连接
  .where(function() {
    const conditions = Object.entries(dbUpdatedData);
    if (conditions.length === 0) return;
    this.whereNot(conditions[0][0], conditions[0][1]);
    for (let i = 1; i < conditions.length; i++) {
      const [key, value] = conditions[i];
      this.orWhereNot(key, value);
    }
  })
  .update(dbUpdatedData);

生成的SQL会包含WHERE ... AND (field1 != val1 OR field2 != val2 ...)的条件，只有真正修改了数据的行才会被计入返回的计数。

方法二：利用数据库原生函数获取实际变更行数 #

部分数据库提供原生函数返回实际被修改的行数（区别于匹配的行数），以MySQL为例，可通过ROW_COUNT()获取：

const dbUpdatedData = convertJobTypeToDB(updatedData);
// 执行更新操作
await getKnexWithSchema<JobTableColumns, JobTableColumns[]>(trx)
  .from(JOBS_TABLE)
  .whereIn('uuid', uuids)
  .whereNotIn('status', [JobStatus.SUCCESS, JobStatus.ABORTED, JobStatus.FAILED])
  .where(convertJobTypeToDB(where || {}))
  .update(dbUpdatedData);

// 查询实际变更的行数
const [{ affectedRows }] = await trx.raw('SELECT ROW_COUNT() as affectedRows');
const updatedRowsCount = affectedRows;

注意：不同数据库的语法不同，比如PostgreSQL需要使用GET DIAGNOSTICS语法，这种方法依赖具体数据库特性。

内容的提问来源于stack exchange，提问作者Ballon Ura