如何在knex/mysql中获取所有更新记录的列表 [英] How to get a list of all updated records in knex / mysql

问题描述

这是我正在处理的查询:

Here's the query I'm working on:

  return knex('table')
    .returning('id')
    .where('boolean', false)
    .andWhere('fooID', foo.id)
    .update({
      boolean : true
    })
    .limit(num)
    .then(function(ids) {
      console.log('\nids');
      console.log(ids); //outputs num

ids现在包含3，即受影响的行数.有什么方法可以获取那3行的ID?我印象中.returning()做到了，但似乎没有.

ids now contains 3, which is the number of affected rows. Is there any way to get the ids of those 3 rows? I was under the impression .returning() did that, but it appears to not.

推荐答案

Mysql数据库不支持returning语句，它仅返回更新行的计数

Mysql database doesn't support returning statement and it returns just count of updated rows http://dev.mysql.com/doc/refman/5.7/en/update.html.

在您看来，您必须先查询要更新的行的ID，然后在事务内进行更新并获取它们.

In your case looks like you must first query ids of the rows to be updated and then update and fetch them inside a transaction.

赞:

return knex.transaction(trx => {
  return trx('table')
    .select('id')
    .where('boolean', false)
    .andWhere('fooID', foo.id)
    .limit(num)
    .then(ids => {
      return trx('table').update({ boolean: true })
        .whereIn('id', ids)
        .then(() => {
          return trx('table').whereIn('id', ids);
        });
    });
});

这篇关于如何在knex/mysql中获取所有更新记录的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！