Knex 查询构建器 #

¥Knex Query Builder

该库的核心 knex 查询构建器是用于构建和执行标准 SQL 查询（例如 select、insert、update、delete）的接口。

¥The heart of the library, the knex query builder is the interface used for building and executing standard SQL queries, such as select, insert, update, delete.

标识符语法 #

¥Identifier Syntax

在 API 的许多地方，表名或列名等标识符可以传递给方法。

¥In many places in APIs identifiers like table name or column name can be passed to methods.

最常见的是，只需要普通的 tableName.columnName、tableName 或 columnName，但在许多情况下，还需要传递一个别名，以便稍后在查询中引用该标识符。

¥Most commonly one needs just plain tableName.columnName, tableName or columnName, but in many cases one also needs to pass an alias how that identifier is referred later on in the query.

有两种方法可以声明标识符的别名。可以直接为标识符提供 as aliasName 后缀（例如 identifierName as aliasName），也可以传递对象 { aliasName: 'identifierName' }。

¥There are two ways to declare an alias for identifier. One can directly give as aliasName suffix for the identifier (e.g. identifierName as aliasName) or one can pass an object { aliasName: 'identifierName' }.

如果对象有多个别名 { alias1: 'identifier1', alias2: 'identifier2' }，则所有别名标识符都将扩展为逗号分隔列表。

¥If the object has multiple aliases { alias1: 'identifier1', alias2: 'identifier2' }, then all the aliased identifiers are expanded to comma separated list.

knex({ a: 'table', b: 'table' })
  .select({
    aTitle: 'a.title',
    bTitle: 'b.title'
  })
  .whereRaw('?? = ??', ['a.column_1', 'b.column_2'])

常用 #

¥Common

knex #

knex(tableName, options={only: boolean}) knex.[methodName]

查询构建器通过指定要查询的 tableName 或直接调用 knex 对象上的任何方法来开始。这启动了一个类似 jQuery 的链，你可以根据需要调用其他查询构建器方法来构造查询，最终调用任何接口方法，以转换为字符串，或使用 Promise、回调或流执行查询 。可选的第二个参数仅用于传递选项：*：如果是 true，则在 tableName 之前使用 ONLY 关键字来丢弃继承表的数据。

¥The query builder starts off either by specifying a tableName you wish to query against, or by calling any method directly on the knex object. This kicks off a jQuery-like chain, with which you can call additional query builder methods as needed to construct the query, eventually calling any of the interface methods, to either convert toString, or execute the query with a promise, callback, or stream. Optional second argument for passing options:* only: if true, the ONLY keyword is used before the tableName to discard inheriting tables' data.

与 TypeScript 一起使用 #

¥Usage with TypeScript

如果使用 TypeScript，你可以将数据库行的类型作为类型参数传递，以获得更好的自动补齐支持。

¥If using TypeScript, you can pass the type of database row as a type parameter to get better autocompletion support down the chain.

interface User {
  id: number;
  name: string;
  age: number;
}

knex('users')
  .where('id')
  .first(); // Resolves to any

knex<User>('users') // User is the type of row in database
  .where('id', 1) // Your IDE will be able to help with the completion of id
  .first(); // Resolves to User | undefined

通过 JSDoc 注释以纯 JavaScript 编写代码时，还可以利用泛型类型参数的自动补齐支持（在支持 TypeScript 的 IDE 中）。

¥It is also possible to take advantage of auto-completion support (in TypeScript-aware IDEs) with generic type params when writing code in plain JavaScript through JSDoc comments.

/**

 * @typedef {Object} User

 * @property {number} id

 * @property {number} age

 * @property {string} name

 *  * @returns {Knex.QueryBuilder<User, {}>}
 */
const Users = () => knex('Users')

// 'id' property can be autocompleted by editor
Users().where('id', 1) 

关于类型推断和可变 Fluent API 的警告 #

¥Caveat with type inference and mutable fluent APIs

大多数 knex API 都会改变当前对象并返回它。此模式不适用于类型推断。

¥Most of the knex APIs mutate current object and return it. This pattern does not work well with type-inference.

knex<User>('users')
  .select('id')
  .then((users) => { // Type of users is inferred as Pick<User, "id">[]
    // Do something with users
  });

knex<User>('users')
  .select('id')
  .select('age')
  .then((users) => { // Type of users is inferred as Pick<User, "id" | "age">[]
    // Do something with users
  });

// The type of usersQueryBuilder is determined here
const usersQueryBuilder = knex<User>('users').select('id');

if (someCondition) {
  // This select will not change the type of usersQueryBuilder
  // We can not change the type of a pre-declared variable in TypeScript
  usersQueryBuilder.select('age');
}
usersQueryBuilder.then((users) => {
  // Type of users here will be Pick<User, "id">[]
  // which may not be what you expect.
});

// You can specify the type of result explicitly through a second type parameter:
const queryBuilder = knex<User, Pick<User, "id" | "age">>('users');

// But there is no type constraint to ensure that these properties have actually been
// selected.

// So, this will compile:
queryBuilder.select('name').then((users) => {
  // Type of users is Pick<User, "id"> but it will only have name
})

如果你不想手动指定结果类型，建议始终使用链的最后一个值的类型，并将任何未来链延续的结果分配给单独的变量（该变量将具有不同的类型）。

¥If you don't want to manually specify the result type, it is recommended to always use the type of last value of the chain and assign result of any future chain continuation to a separate variable (which will have a different type).

timeout #

.timeout(ms, options={cancel: boolean})

设置查询超时，如果超过超时将抛出 TimeoutError。该错误包含有关查询、绑定和设置的超时的信息。对于你想要确保执行时间不会太长的复杂查询很有用。用于传递选项的可选第二个参数：* 取消：如果是 true，如果超时则取消查询。

¥Sets a timeout for the query and will throw a TimeoutError if the timeout is exceeded. The error contains information about the query, bindings, and the timeout that was set. Useful for complex queries that you want to make sure are not taking too long to execute. Optional second argument for passing options:* cancel: if true, cancel query if timeout is reached.

knex.select()
  .from('books')
  .timeout(1000)

knex.select()
  .from('books')
  .timeout(1000, { 
    cancel: true // MySQL and PostgreSQL only
  }) 

select #

*.select([columns])

创建一个选择查询，为查询采用可选的列数组，如果在构建查询时未指定任何列，则最终默认为 *。select 调用的响应将解析为从数据库中选择的对象数组。

¥Creates a select query, taking an optional array of columns for the query, eventually defaulting to * if none are specified when the query is built. The response of a select call will resolve with an array of objects selected from the database.

knex.select('title', 'author', 'year')
  .from('books')

knex.select()
  .table('books')

与 TypeScript 一起使用 #

¥Usage with TypeScript

只要选择参数与记录类型中的键名称完全匹配，我们通常就可以根据选择的列推断结果类型。然而，别名和作用域可能会妨碍推断。

¥We are generally able to infer the result type based on the columns being selected as long as the select arguments match exactly the key names in record type. However, aliasing and scoping can get in the way of inference.

knex.select('id')
  .from<User>('users'); // Resolves to Pick<User, "id">[]

knex.select('users.id')
  .from<User>('users'); // Resolves to any[]
// ^ TypeScript doesn't provide us a way to look into a string and infer the type
//   from a substring, so we fall back to any

// We can side-step this using knex.ref:
knex.select(knex.ref('id').withSchema('users'))
  .from<User>('users'); // Resolves to Pick<User, "id">[]

knex.select('id as identifier')
  .from<User>('users'); // Resolves to any[], for same reason as above

// Refs are handy here too:
knex.select(knex.ref('id').as('identifier'))
  .from<User>('users'); // Resolves to { identifier: number; }[]

as #

.as(name)

允许为子查询添加别名，采用你希望命名当前查询的字符串。如果查询不是子查询，它将被忽略。

¥Allows for aliasing a subquery, taking the string you wish to name the current query. If the query is not a sub-query, it will be ignored.

knex.avg('sum_column1')
  .from(function() {
    this.sum('column1 as sum_column1')
      .from('t1')
      .groupBy('column1')
      .as('t1')
  })
  .as('ignored_alias')

column #

.column(columns)

具体设置要在选择查询上选择的列，采用数组、对象或列名称列表。传递对象将自动为具有给定键的列添加别名。

¥Specifically set the columns to be selected on a select query, taking an array, an object or a list of column names. Passing an object will automatically alias the columns with the given keys.

knex.column('title', 'author', 'year')
  .select()
  .from('books')

knex.column(['title', 'author', 'year'])
  .select()
  .from('books')

knex.column('title', { by: 'author' }, 'year')
  .select()
  .from('books')

from #

.from([tableName], options={only: boolean})

指定当前查询中使用的表，如果已指定，则替换当前表名称。这通常用在高级 where 或 union 方法中执行的子查询中。可选的第二个参数仅用于传递选项：*：如果是 true，则在 tableName 之前使用 ONLY 关键字来丢弃继承表的数据。

¥Specifies the table used in the current query, replacing the current table name if one has already been specified. This is typically used in the sub-queries performed in the advanced where or union methods. Optional second argument for passing options:* only: if true, the ONLY keyword is used before the tableName to discard inheriting tables' data.

knex.select('*')
  .from('users')

与 TypeScript 一起使用 #

¥Usage with TypeScript

我们可以通过 TRecord 类型参数指定数据库行的类型

¥We can specify the type of database row through the TRecord type parameter

knex.select('id')
  .from('users'); // Resolves to any[]

knex.select('id')
  .from<User>('users'); // Results to Pick<User, "id">[]

fromRaw #

.fromRaw(sql, [bindings])

knex.select('*')
  .fromRaw('(select * from "users" where "age" > ?)', '18')

with #

.with(alias, [columns], callback|builder|raw)

向查询添加 "with" 子句。PostgreSQL、Oracle、SQLite3 和 MSSQL 支持 "使用" 子句。别名后可以提供可选的列列表；如果提供，则必须至少包含一个列名称。

¥Add a "with" clause to the query. "With" clauses are supported by PostgreSQL, Oracle, SQLite3 and MSSQL. An optional column list can be provided after the alias; if provided, it must include at least one column name.

knex
  .with(
    'with_alias', 
    knex.raw(
      'select * from "books" where "author" = ?', 
      'Test'
    )
  )
  .select('*')
  .from('with_alias')

knex
  .with(
    'with_alias', 
    ["title"], 
    knex.raw(
      'select "title" from "books" where "author" = ?', 
      'Test'
    )
  )
  .select('*')
  .from('with_alias')

knex
  .with('with_alias', (qb) => {
    qb.select('*')
      .from('books')
      .where('author', 'Test')
  })
  .select('*')
  .from('with_alias')

withRecursive #

.withRecursive(alias, [columns], callback|builder|raw)

与 with 方法相同，只是 "recursive" 附加到 "with"（或不附加，根据目标数据库的要求）以使自引用 CTE 成为可能。请注意，某些数据库（例如 Oracle）在使用 rCTE 时需要提供列列表。

¥Identical to the with method except "recursive" is appended to "with" (or not, as required by the target database) to make self-referential CTEs possible. Note that some databases, such as Oracle, require a column list be provided when using an rCTE.

knex
  .withRecursive('ancestors', (qb) => {
    qb.select('*')
      .from('people')
      .where('people.id', 1)
      .union((qb) => {
        qb.select('*')
          .from('people')
          .join(
            'ancestors', 
            'ancestors.parentId', 
            'people.id'
          )
      })
  })
  .select('*')
  .from('ancestors')

knex
  .withRecursive('family', ['name', 'parentName'], (qb) => {
    qb.select('name', 'parentName')
      .from('folks')
      .where({ name: 'grandchild' })
      .unionAll((qb) =>
        qb
          .select('folks.name', 'folks.parentName')
          .from('folks')
          .join(
            'family',
            knex.ref('family.parentName'),
            knex.ref('folks.name')
          )
      )
  })
  .select('name')
  .from('family')

withMaterialized #

.withMaterialized(alias, [columns], callback|builder|raw)

将 "with" 具体化子句添加到查询中。PostgreSQL 和 SQLite3 支持 "使用" 物化子句。别名后可以提供可选的列列表；如果提供，则必须至少包含一个列名称。

¥Add a "with" materialized clause to the query. "With" materialized clauses are supported by PostgreSQL and SQLite3. An optional column list can be provided after the alias; if provided, it must include at least one column name.

knex
  .withMaterialized(
    'with_alias', 
    knex.raw(
      'select * from "books" where "author" = ?', 
      'Test'
    )
  )
  .select('*')
  .from('with_alias')

knex
  .withMaterialized(
    'with_alias', 
    ["title"], 
    knex.raw(
      'select "title" from "books" where "author" = ?', 
      'Test'
    )
  )
  .select('*')
  .from('with_alias')

knex
  .withMaterialized('with_alias', (qb) => {
    qb.select('*')
      .from('books')
      .where('author', 'Test')
  })
  .select('*')
  .from('with_alias')

withNotMaterialized #

.withNotMaterialized(alias, [columns], callback|builder|raw)

将 "with" not Materialized 子句添加到查询中。PostgreSQL 和 SQLite3 支持 "使用" 未具体化子句。别名后可以提供可选的列列表；如果提供，则必须至少包含一个列名称。

¥Add a "with" not materialized clause to the query. "With" not materialized clauses are supported by PostgreSQL and SQLite3. An optional column list can be provided after the alias; if provided, it must include at least one column name.

knex
  .withNotMaterialized(
    'with_alias', 
    knex.raw(
      'select * from "books" where "author" = ?', 
      'Test'
    )
  )
  .select('*')
  .from('with_alias')

knex
  .withNotMaterialized(
    'with_alias', 
    ["title"], 
    knex.raw(
      'select "title" from "books" where "author" = ?', 
      'Test'
    )
  )
  .select('*')
  .from('with_alias')

knex
  .withNotMaterialized('with_alias', (qb) => {
    qb.select('*')
      .from('books')
      .where('author', 'Test')
  })
  .select('*')
  .from('with_alias')

withSchema #

.withSchema([schemaName])

指定用作表名前缀的架构。

¥Specifies the schema to be used as prefix of table name.

knex.withSchema('public')
  .select('*')
  .from('users')

jsonExtract #

.jsonExtract(column|builder|raw|array[], path, [alias], [singleValue])

从给定 JsonPath 的 json 列中提取值。可以指定别名。singleValue 布尔值可用于通过 Oracle 或 MSSQL 指定函数返回的值是单个值还是数组/对象值。通过一次调用此函数，可以使用数组的数组来指定多次提取。

¥Extract a value from a json column given a JsonPath. An alias can be specified. The singleValue boolean can be used to specify, with Oracle or MSSQL, if the value returned by the function is a single value or an array/object value. An array of arrays can be used to specify multiple extractions with one call to this function.

knex('accounts')
  .jsonExtract('json_col', '$.name')

knex('accounts')
  .jsonExtract('json_col', '$.name', 'accountName')

knex('accounts')
  .jsonExtract('json_col', '$.name', 'accountName', true)

knex('accounts')
  .jsonExtract([ 
    ['json_col', '$.name', 'accountName'], 
    ['json_col', '$.lastName', 'accountLastName'] 
  ])

所有 json*() 函数都可以直接从 knex 对象使用并且可以嵌套。

¥All json*() functions can be used directly from knex object and can be nested.

knex('cities')
  .jsonExtract([
    [
      knex.jsonRemove('population', '$.min'), 
      '$', 
      'withoutMin'
    ],
    [
      knex.jsonRemove('population', '$.max'), 
      '$', 
      'withoutMax'
    ],
    [
      knex.jsonSet('population', '$.current', '1234'),
      '$',
      'currentModified',
    ]
  ])

jsonSet #

.jsonSet(column|builder|raw, path, value, [alias])

返回一个 json 值/对象/数组，其中给定值在给定 JsonPath 处设置。值可以是单个值或 json 对象。如果给定位置已存在某个值，则该值将被替换。Redshift 和 Oracle 21c 之前的版本不支持。

¥Return a json value/object/array where a given value is set at the given JsonPath. Value can be single value or json object. If a value already exists at the given place, the value is replaced. Not supported by Redshift and versions before Oracle 21c.

knex('accounts')
  .jsonSet('json_col', '$.name', 'newName', 'newNameCol')

knex('accounts')
  .jsonSet(
    'json_col', 
    '$.name', 
    { "name": "newName" }, 
    'newNameCol'
  )

jsonInsert #

.jsonInsert(column|builder|raw, path, value, [alias])

返回一个 json 值/对象/数组，其中给定值插入给定的 JsonPath。值可以是单个值或 json 对象。如果给定路径中存在某个值，则不会替换该值。Redshift 和 Oracle 21c 之前的版本不支持。

¥Return a json value/object/array where a given value is inserted at the given JsonPath. Value can be single value or json object. If a value exists at the given path, the value is not replaced. Not supported by Redshift and versions before Oracle 21c.

knex('accounts')
  .jsonInsert('json_col', '$.name', 'newName', 'newNameCol')

knex('accounts')
  .jsonInsert(
    'json_col', 
    '$.name', 
    { "name": "newName" }, 
    'newNameCol'
  )

knex('accounts')
  .jsonInsert(
    knex.jsonExtract('json_col', '$.otherAccount'), 
    '$.name', 
    { "name": "newName" }, 
    'newNameCol'
  )

jsonRemove #

.jsonRemove(column|builder|raw, path, [alias])

返回一个 json 值/对象/数组，其中给定值在给定 JsonPath 处被删除。Redshift 和 Oracle 21c 之前的版本不支持。

¥Return a json value/object/array where a given value is removed at the given JsonPath. Not supported by Redshift and versions before Oracle 21c.

knex('accounts')
  .jsonRemove('json_col', '$.name', 'colWithRemove')

knex('accounts')
  .jsonInsert(
    'json_col', 
    '$.name', 
    { "name": "newName" }, 
    'newNameCol'
  )

offset #

.offset(value, options={skipBinding: boolean})

向查询添加偏移子句。可以指定可选的 skipBinding 参数，这将避免将偏移量设置为准备值（某些数据库不允许偏移量的准备值）。

¥Adds an offset clause to the query. An optional skipBinding parameter may be specified which would avoid setting offset as a prepared value (some databases don't allow prepared values for offset).

knex.select('*')
  .from('users')
  .offset(10)

knex.select('*')
  .from('users')
  .offset(10)
  .toSQL()
  .sql

// Offset value isn't a prepared value.
knex.select('*')
  .from('users')
  .offset(10, {skipBinding: true})
  .toSQL()
  .sql

limit #

.limit(value, options={skipBinding: boolean})

向查询添加限制子句。可以指定可选的 skipBinding 参数以避免将限制添加为准备值（某些数据库不允许为限制添加准备值）。

¥Adds a limit clause to the query. An optional skipBinding parameter may be specified to avoid adding limit as a prepared value (some databases don't allow prepared values for limit).

knex.select('*')
  .from('users')
  .limit(10)
  .offset(30)

knex.select('*')
  .from('users')
  .limit(10)
  .offset(30)
  .toSQL()
  .sql

// Limit value isn't a prepared value.
knex.select('*')
  .from('users')
  .limit(10, {skipBinding: true})
  .offset(30)
  .toSQL()
  .sql

union #

*.union([queries], [wrap])

创建联合查询，采用数组或回调列表、构建器或原始语句来构建联合语句，并带有可选的布尔换行。如果 wrap 参数是 true，则查询将单独括在括号中。

¥Creates a union query, taking an array or a list of callbacks, builders, or raw statements to build the union statement, with optional boolean wrap. If the wrap parameter is true, the queries will be individually wrapped in parentheses.

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .union(function() {
    this.select('*')
      .from('users')
      .whereNull('first_name')
  })

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .union([
    knex.select('*')
      .from('users')
      .whereNull('first_name')
  ])

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .union(
    knex.raw(
      'select * from users where first_name is null'
    ),
    knex.raw(
      'select * from users where email is null'
    )
  )

如果要将 orderBy、groupBy、limit、offset 或 having 应用于联合的输入，则需要使用 knex.union 作为基本语句。如果你不这样做，这些子句将被附加到联合的末尾。

¥If you want to apply orderBy, groupBy, limit, offset or having to inputs of the union you need to use knex.union as a base statement. If you don't do this, those clauses will get appended to the end of the union.

// example showing how clauses get appended to the end of the query
knex('users')
  .select('id', 'name')
  .groupBy('id')
  .union(
    knex('invitations')
      .select('id', 'name')
      .orderBy('expires_at')
  )

knex.union([
  knex('users').select('id', 'name').groupBy('id'),
  knex('invitations').select('id', 'name').orderBy('expires_at')
])

before 和 after

¥before and after

unionAll #

*.unionAll([queries], [wrap])

创建一个 union all 查询，其方法签名与 union 方法相同。如果 wrap 参数是 true，则查询将单独括在括号中。

¥Creates a union all query, with the same method signature as the union method. If the wrap parameter is true, the queries will be individually wrapped in parentheses.

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .unionAll(function() {
    this.select('*').from('users').whereNull('first_name');
  })

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .unionAll([
    knex.select('*').from('users').whereNull('first_name')
  ])

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .unionAll(
    knex.raw(
      'select * from users where first_name is null'
    ),
    knex.raw(
      'select * from users where email is null'
    )
  )

intersect #

*.intersect([queries], [wrap])

创建相交查询，采用数组或回调列表、构建器或原始语句来构建相交语句，并带有可选的布尔换行。如果 wrap 参数是 true，则查询将单独括在括号中。MySQL 不支持 intersect 方法。

¥Creates an intersect query, taking an array or a list of callbacks, builders, or raw statements to build the intersect statement, with optional boolean wrap. If the wrap parameter is true, the queries will be individually wrapped in parentheses. The intersect method is unsupported on MySQL.

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .intersect(function() {
    this.select('*').from('users').whereNull('first_name')
  })

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .intersect([
    knex.select('*').from('users').whereNull('first_name')
  ])

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .intersect(
    knex.raw(
      'select * from users where first_name is null'
    ),
    knex.raw(
      'select * from users where email is null'
    )
  )

except #

*.except([queries], [wrap])

创建一个 except 查询，采用回调、构建器或原始语句的数组或列表来构建 except 语句，并带有可选的布尔换行。如果 wrap 参数是 true，则查询将单独括在括号中。MySQL 不支持 except 方法。

¥Creates an except query, taking an array or a list of callbacks, builders, or raw statements to build the except statement, with optional boolean wrap. If the wrap parameter is true, the queries will be individually wrapped in parentheses. The except method is unsupported on MySQL.

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .except(function() {
    this.select('*').from('users').whereNull('first_name')
  })

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .except([
    knex.select('*').from('users').whereNull('first_name')
  ])

knex.select('*')
  .from('users')
  .whereNull('last_name')
  .except(
    knex.raw(
      'select * from users where first_name is null'
    ),
    knex.raw(
      'select * from users where email is null'
    )
  )

insert #

.insert(data, [returning], [options])

创建插入查询，采用要插入到行中的属性散列或插入数组，作为单个插入命令执行。如果返回数组被传递，例如 ['id', 'title']，它使用包含指定列的所有添加行的数组来解决 promise/履行回调。这是 返回方式 的快捷方式

¥Creates an insert query, taking either a hash of properties to be inserted into the row, or an array of inserts, to be executed as a single insert command. If returning array is passed e.g. ['id', 'title'], it resolves the promise / fulfills the callback with an array of all the added rows with specified columns. It's a shortcut for returning method

// Returns [1] in "mysql", "sqlite", "oracle"; 
// [] in "postgresql" 
// unless the 'returning' parameter is set.
knex('books').insert({title: 'Slaughterhouse Five'})

// Normalizes for empty keys on multi-row insert:
knex('coords').insert([{x: 20}, {y: 30},  {x: 10, y: 20}])

// Returns [2] in "mysql", "sqlite"; [2, 3] in "postgresql"
knex
  .insert(
    [
      { title: 'Great Gatsby' }, 
      { title: 'Fahrenheit 451' }
    ], 
    ['id']
  )
  .into('books')

对于 MSSQL，表上的触发器可以中断从标准插入语句返回有效值。你可以添加 includeTriggerModifications 选项来解决此问题。这会修改 SQL，以便可以返回正确的值。仅当你使用 MSSQL、指定返回值并设置 includeTriggerModifications 选项时，这才会修改语句。

¥For MSSQL, triggers on tables can interrupt returning a valid value from the standard insert statements. You can add the includeTriggerModifications option to get around this issue. This modifies the SQL so the proper values can be returned. This only modifies the statement if you are using MSSQL, a returning value is specified, and the includeTriggerModifications option is set.

// Adding the option includeTriggerModifications 
// allows you to run statements on tables 
// that contain triggers. Only affects MSSQL.
knex('books')
  .insert(
    {title: 'Alice in Wonderland'}, 
    ['id'], 
    { includeTriggerModifications: true }
  )

如果希望将未定义的键替换为 NULL 而不是 DEFAULT，则可以在 knex 配置中给出 useNullAsDefault 配置参数。

¥If one prefers that undefined keys are replaced with NULL instead of DEFAULT one may give useNullAsDefault configuration parameter in knex config.

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  },
  useNullAsDefault: true
});

knex('coords').insert([{x: 20}, {y: 30}, {x: 10, y: 20}])

insert into `coords` (`x`, `y`) values (20, NULL), (NULL, 30), (10, 20)"

onConflict #

insert(..).onConflict(column) insert(..).onConflict([column1, column2, ...]) insert(..).onConflict(knex.raw(...))

为 PostgreSQL、MySQL 和 SQLite 数据库实现。插入查询的修饰符，指定发生冲突时的替代行为。当表的某列上有 PRIMARY KEY 或 UNIQUE 索引（或一组列上的复合索引）并且正在插入的行与表中这些列中已存在的行具有相同的值时，就会发生冲突（ s）。发生冲突时的默认行为是引发错误并中止查询。使用此方法，你可以更改此行为，以使用 .onConflict().ignore() 静默忽略错误，或使用 .onConflict().merge() 使用新数据更新现有行（执行 "UPSERT"）。

¥Implemented for the PostgreSQL, MySQL, and SQLite databases. A modifier for insert queries that specifies alternative behaviour in the case of a conflict. A conflict occurs when a table has a PRIMARY KEY or a UNIQUE index on a column (or a composite index on a set of columns) and a row being inserted has the same value as a row which already exists in the table in those column(s). The default behaviour in case of conflict is to raise an error and abort the query. Using this method you can change this behaviour to either silently ignore the error by using .onConflict().ignore() or to update the existing row with new data (perform an "UPSERT") by using .onConflict().merge().

knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe",
    active: true
  })
    // ignore only on email conflict and active is true.
  .onConflict(knex.raw('(email) where active'))
  .ignore()

有关更多详细信息，请参阅有关 .ignore() 和 .merge() 方法的文档。

¥See documentation on .ignore() and .merge() methods for more details.

ignore #

insert(..).onConflict(..).ignore()

为 PostgreSQL、MySQL 和 SQLite 数据库实现。修改插入查询，并使其在发生冲突时被静默删除而不会出现错误。在 MySQL 中使用 INSERT IGNORE，并在 PostgreSQL 和 SQLite 中的 insert 语句中添加 ON CONFLICT（列）DO NOTHING 子句。

¥Implemented for the PostgreSQL, MySQL, and SQLite databases. Modifies an insert query, and causes it to be silently dropped without an error if a conflict occurs. Uses INSERT IGNORE in MySQL, and adds an ON CONFLICT (columns) DO NOTHING clause to the insert statement in PostgreSQL and SQLite.

knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe"
  })
  .onConflict('email')
  .ignore()

merge #

insert(..).onConflict(..).merge() insert(..).onConflict(..).merge(updates)

为 PostgreSQL、MySQL 和 SQLite 数据库实现。修改插入查询，将其转换为 'upsert' 操作。在 MySQL 中使用 ON DUPLICATE KEY UPDATE，并在 PostgreSQL 和 SQLite 中的 insert 语句中添加 ON CONFLICT（列）DO UPDATE 子句。默认情况下，它合并所有列。

¥Implemented for the PostgreSQL, MySQL, and SQLite databases. Modifies an insert query, to turn it into an 'upsert' operation. Uses ON DUPLICATE KEY UPDATE in MySQL, and adds an ON CONFLICT (columns) DO UPDATE clause to the insert statement in PostgreSQL and SQLite. By default, it merges all columns.

knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe"
  })
  .onConflict('email')
  .merge()

这也适用于批量插入：

¥This also works with batch inserts:

knex('tableName')
  .insert([
    { email: "john@example.com", name: "John Doe" },
    { email: "jane@example.com", name: "Jane Doe" },
    { email: "alex@example.com", name: "Alex Doe" },
  ])
  .onConflict('email')
  .merge()

还可以指定发生冲突时要合并的列的子集。例如，你可能希望在插入时设置 'created_at' 列，但如果该行已存在，则不想更新它：

¥It is also possible to specify a subset of the columns to merge when a conflict occurs. For example, you may want to set a 'created_at' column when inserting but would prefer not to update it if the row already exists:

const timestamp = Date.now();
knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe",
    created_at: timestamp,
    updated_at: timestamp,
  })
  .onConflict('email')
  .merge(['email', 'name', 'updated_at'])

还可以指定要更新的数据与要插入的数据分开。如果你想使用不同的数据更新插入，这非常有用。例如，如果该行已存在，你可能想要更改值：

¥It is also possible to specify data to update separately from the data to insert. This is useful if you want to update with different data to the insert. For example, you may want to change a value if the row already exists:

const timestamp = Date.now();
knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe",
    created_at: timestamp,
    updated_at: timestamp,
  })
  .onConflict('email')
  .merge({
    name: "John Doe The Second",
  })

仅对于 PostgreSQL/SQLite 数据库，还可以添加 WHERE 子句 以有条件地仅更新匹配的行：

¥For PostgreSQL/SQLite databases only, it is also possible to add a WHERE clause to conditionally update only the matching rows:

const timestamp = Date.now();
knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe",
    created_at: timestamp,
    updated_at: timestamp,
  })
  .onConflict('email')
  .merge({
    name: "John Doe",
    updated_at: timestamp,
  })
  .where('updated_at', '<', timestamp)

upsert #

.upsert(data, [returning], [options])

为 CockroachDB 实现。创建一个 upsert 查询，采用要插入到行中的属性散列或 upsert 数组，作为单个 upsert 命令执行。如果返回数组被传递，例如 ['id', 'title']，它使用包含指定列的所有添加行的数组来解决 promise/履行回调。这是 返回方式 的快捷方式

¥Implemented for the CockroachDB. Creates an upsert query, taking either a hash of properties to be inserted into the row, or an array of upserts, to be executed as a single upsert command. If returning array is passed e.g. ['id', 'title'], it resolves the promise / fulfills the callback with an array of all the added rows with specified columns. It's a shortcut for returning method

// insert new row with unique index on title column
knex('books').upsert({title: 'Great Gatsby'})

// update row by unique title 'Great Gatsby' 
// and insert row with title 'Fahrenheit 451'
knex('books').upsert([
  {title: 'Great Gatsby'}, 
  {title: 'Fahrenheit 451'}
], ['id'])

// Normalizes for empty keys on multi-row upsert, 
// result sql: 
// ("x", "y") values (20, default), (default, 30), (10, 20):
knex('coords').upsert([{x: 20}, {y: 30}, {x: 10, y: 20}])

update #

.update(data, [returning], [options]) .update(key, value, [returning], [options])

创建更新查询，根据其他查询约束获取要更新的属性哈希或键/值对。如果返回数组被传递，例如 ['id'，'title']，它使用指定列的所有更新行的数组来解决 promise/履行回调。这是 返回方式 的快捷方式

¥Creates an update query, taking a hash of properties or a key/value pair to be updated based on the other query constraints. If returning array is passed e.g. ['id', 'title'], it resolves the promise / fulfills the callback with an array of all the updated rows with specified columns. It's a shortcut for returning method

knex('books')
  .where('published_date', '<', 2000)
  .update({
    status: 'archived',
    thisKeyIsSkipped: undefined
  })

// Returns [1] in "mysql", "sqlite", "oracle"; 
// [] in "postgresql" 
// unless the 'returning' parameter is set.
knex('books').update('title', 'Slaughterhouse Five')

/** Returns  

 * [{ 

 *   id: 42, 

 *   title: "The Hitchhiker's Guide to the Galaxy" 

 * }] **/
knex('books')
  .where({ id: 42 })
  .update({ 
    title: "The Hitchhiker's Guide to the Galaxy" 
  }, ['id', 'title'])

对于 MSSQL，表上的触发器可以中断从标准更新语句返回有效值。你可以添加 includeTriggerModifications 选项来解决此问题。这会修改 SQL，以便可以返回正确的值。仅当你使用 MSSQL、指定返回值并设置 includeTriggerModifications 选项时，这才会修改语句。

¥For MSSQL, triggers on tables can interrupt returning a valid value from the standard update statements. You can add the includeTriggerModifications option to get around this issue. This modifies the SQL so the proper values can be returned. This only modifies the statement if you are using MSSQL, a returning value is specified, and the includeTriggerModifications option is set.

// Adding the option includeTriggerModifications allows you
// to run statements on tables that contain triggers.
// Only affects MSSQL.
knex('books')
  .update(
    {title: 'Alice in Wonderland'}, 
    ['id', 'title'], 
    { includeTriggerModifications: true }
  )

updateFrom #

.updateFrom(tableName)

可用于在 PostgreSQL 中定义具有显式 'from' 语法的更新语句，该语法可在 'where' 条件中引用。

¥Can be used to define in PostgreSQL an update statement with explicit 'from' syntax which can be referenced in 'where' conditions.

knex('accounts')
  .update({ enabled: false })
  .updateFrom('clients')
  .where('accounts.id', '=', 'clients.id')
  .where('clients.active', '=', false)

del / delete #

.del([returning], [options])

别名为 del，因为删除是 JavaScript 中的保留字，此方法根据查询中指定的其他条件删除一行或多行。使用查询的受影响行数解决 promise/履行回调。

¥Aliased to del as delete is a reserved word in JavaScript, this method deletes one or more rows, based on other conditions specified in the query. Resolves the promise / fulfills the callback with the number of affected rows for the query.

knex('accounts')
  .where('activated', false)
  .del()

对于 MSSQL，表上的触发器可以中断从标准删除语句返回有效值。你可以添加 includeTriggerModifications 选项来解决此问题。这会修改 SQL，以便可以返回正确的值。仅当你使用 MSSQL、指定返回值并设置 includeTriggerModifications 选项时，这才会修改语句。

¥For MSSQL, triggers on tables can interrupt returning a valid value from the standard delete statements. You can add the includeTriggerModifications option to get around this issue. This modifies the SQL so the proper values can be returned. This only modifies the statement if you are using MSSQL, a returning value is specified, and the includeTriggerModifications option is set.

// Adding the option includeTriggerModifications allows you
// to run statements on tables that contain triggers. 
// Only affects MSSQL.
knex('books')
  .where('title', 'Alice in Wonderland')
  .del(
    ['id', 'title'], 
    { includeTriggerModifications: true }
  )

对于 PostgreSQL，经典 'join' 语法和 'using' 语法都支持带连接的删除语句。

¥For PostgreSQL, Delete statement with joins is both supported with classic 'join' syntax and 'using' syntax.

knex('accounts')
  .where('activated', false)
  .join('accounts', 'accounts.id', 'users.account_id')
  .del()

using #

.using(tableName|tableNames)

可用于在 PostgreSQL 中定义带有显式 'using' 语法连接的删除语句。也可以使用经典的连接语法。

¥Can be used to define in PostgreSQL a delete statement with joins with explicit 'using' syntax. Classic join syntax can be used too.

knex('accounts')
  .where('activated', false)
  .using('accounts')
  .whereRaw('accounts.id = users.account_id')
  .del()

returning #

.returning(column, [options]) .returning([column1, column2, ...], [options])

由 PostgreSQL、MSSQL、SQLite 和 Oracle 数据库使用，返回方法指定插入、更新和删除方法应返回哪一列。传递的列参数可以是字符串或字符串数组。SQL 结果将报告为对象数组，每个对象包含每个指定列的单个属性。Amazon Redshift 不支持返回方法。

¥Utilized by PostgreSQL, MSSQL, SQLite, and Oracle databases, the returning method specifies which column should be returned by the insert, update and delete methods. Passed column parameter may be a string or an array of strings. The SQL result be reported as an array of objects, each containing a single property for each of the specified columns. The returning method is not supported on Amazon Redshift.

// Returns [ { id: 1 } ]
knex('books')
  .returning('id')
  .insert({title: 'Slaughterhouse Five'})

// Returns [{ id: 2 } ] in "mysql", "sqlite"; 
// [ { id: 2 }, { id: 3 } ] in "postgresql"
knex('books')
  .returning('id')
  .insert([
    {title: 'Great Gatsby'}, 
    {title: 'Fahrenheit 451'}
  ])

// Returns [ { id: 1, title: 'Slaughterhouse Five' } ]
knex('books')
  .returning(['id','title'])
  .insert({title: 'Slaughterhouse Five'})

对于 MSSQL，表上的触发器可以中断从标准 DML 语句返回有效值。你可以添加 includeTriggerModifications 选项来解决此问题。这会修改 SQL，以便可以返回正确的值。仅当你使用 MSSQL、指定返回值并设置 includeTriggerModifications 选项时，这才会修改语句。

¥For MSSQL, triggers on tables can interrupt returning a valid value from the standard DML statements. You can add the includeTriggerModifications option to get around this issue. This modifies the SQL so the proper values can be returned. This only modifies the statement if you are using MSSQL, a returning value is specified, and the includeTriggerModifications option is set.

// Adding the option includeTriggerModifications allows you
// to run statements on tables that contain triggers. 
// Only affects MSSQL.
knex('books')
  .returning(
    ['id','title'], 
    { includeTriggerModifications: true }
  )
  .insert({title: 'Slaughterhouse Five'})

transacting #

.transacting(transactionObj)

由 knex.transaction 使用，事务方法可以链接到任何查询，并传递你希望作为事务一部分加入查询的对象。

¥Used by knex.transaction, the transacting method may be chained to any query and passed the object you wish to join the query as part of the transaction for.

const Promise = require('bluebird');
knex.transaction(function(trx) {
  knex('books').transacting(trx).insert({name: 'Old Books'})
    .then(function(resp) {
      const id = resp[0];
      return someExternalMethod(id, trx);
    })
    .then(trx.commit)
    .catch(trx.rollback);
})
.then(function(resp) {
  console.log('Transaction complete.');
})
.catch(function(err) {
  console.error(err);
});

forUpdate #

.transacting(t).forUpdate()

forUpdate 是在指定事务后动态添加的，在 PostgreSQL 和 MySQL 中，forUpdate 在 select 语句期间添加 FOR UPDATE。由于缺少表锁，Amazon Redshift 不支持。

¥Dynamically added after a transaction is specified, the forUpdate adds a FOR UPDATE in PostgreSQL and MySQL during a select statement. Not supported on Amazon Redshift due to lack of table locks.

knex('tableName')
  .transacting(trx)
  .forUpdate()
  .select('*')

forShare #

.transacting(t).forShare()

forShare 在指定事务后动态添加，在 PostgreSQL 中添加 FOR SHARE，在 MySQL 中在 select 语句期间添加 LOCK IN SHARE MODE。由于缺少表锁，Amazon Redshift 不支持。

¥Dynamically added after a transaction is specified, the forShare adds a FOR SHARE in PostgreSQL and a LOCK IN SHARE MODE for MySQL during a select statement. Not supported on Amazon Redshift due to lack of table locks.

knex('tableName')
  .transacting(trx)
  .forShare()
  .select('*')

forNoKeyUpdate #

.transacting(t).forNoKeyUpdate()

forNoKeyUpdate 在指定事务后动态添加，在 PostgreSQL 中添加 FOR NO KEY UPDATE。

¥Dynamically added after a transaction is specified, the forNoKeyUpdate adds a FOR NO KEY UPDATE in PostgreSQL.

knex('tableName')
  .transacting(trx)
  .forNoKeyUpdate()
  .select('*')

forKeyShare #

.transacting(t).forKeyShare()

forKeyShare 是在指定事务后动态添加的，在 PostgreSQL 中添加了 FOR KEY SHARE。

¥Dynamically added after a transaction is specified, the forKeyShare adds a FOR KEY SHARE in PostgreSQL.

knex('tableName')
  .transacting(trx)
  .forKeyShare()
  .select('*')

skipLocked #

.skipLocked()

仅限 MySQL 8.0+、MariaDB-10.6+ 和 PostgreSQL 9.5+。在使用 forUpdate 或 forShare 指定锁定模式后可以使用此方法，并且将导致查询跳过任何锁定的行，如果没有可用的行，则返回空集。

¥MySQL 8.0+, MariaDB-10.6+ and PostgreSQL 9.5+ only. This method can be used after a lock mode has been specified with either forUpdate or forShare, and will cause the query to skip any locked rows, returning an empty set if none are available.

knex('tableName')
  .select('*')
  .forUpdate()
  .skipLocked()

noWait #

.noWait()

仅限 MySQL 8.0+、MariaDB-10.3+ 和 PostgreSQL 9.5+。此方法可以在使用 forUpdate 或 forShare 指定锁定模式后使用，并且如果当前锁定任何选定的行，将导致查询立即失败。

¥MySQL 8.0+, MariaDB-10.3+ and PostgreSQL 9.5+ only. This method can be used after a lock mode has been specified with either forUpdate or forShare, and will cause the query to fail immediately if any selected rows are currently locked.

knex('tableName')
  .select('*')
  .forUpdate()
  .noWait()

count #

.count(column|columns|raw, [options])

对指定的列或列数组执行计数（请注意，某些驱动程序不支持多列）。还接受原始表达式。从 count （和其他聚合查询）返回的值是一个对象数组，例如：[{'COUNT(*)': 1}]。实际的键是特定于方言的，因此通常我们想要指定一个别名（请参阅下面的示例）。请注意，在 Postgres 中，count 返回 bigint 类型，它将是字符串而不是数字 (更多信息)。

¥Performs a count on the specified column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions. The value returned from count (and other aggregation queries) is an array of objects like: [{'COUNT(*)': 1}]. The actual keys are dialect specific, so usually we would want to specify an alias (Refer examples below). Note that in Postgres, count returns a bigint type which will be a String and not a Number (more info).

knex('users').count('active')

knex('users').count('active', {as: 'a'})

knex('users').count('active as a')

knex('users').count({ a: 'active' })

knex('users').count({ a: 'active', v: 'valid' })

knex('users').count('id', 'active')

knex('users').count({ count: ['id', 'active'] })

knex('users').count(knex.raw('??', ['active']))

与 TypeScript 一起使用 #

¥Usage with TypeScript

默认情况下，count 的值的类型为 string | number。这可能是违反直觉的，但是当 javascript 的 Number 类型对于值来说不够大时，某些连接器（例如 postgres）会自动将 BigInt 结果转换为字符串。

¥The value of count will, by default, have type of string | number. This may be counter-intuitive but some connectors (eg. postgres) will automatically cast BigInt result to string when javascript's Number type is not large enough for the value.

    knex('users').count('age') // Resolves to: Record<string, number | string>
    
    knex('users').count({count: '*'}) // Resolves to { count?: string | number | undefined; }

如果你不使用大型表，则使用 string | number 可能会很不方便。有两种选择：

¥Working with string | number can be inconvenient if you are not working with large tables. Two alternatives are available:

    // Be explicit about what you want as a result:
    knex('users').count<Record<string, number>>('age');
    
    // Setup a one time declaration to make knex use number as result type for all
    // count and countDistinct invocations (for any table)
    declare module "knex/types/result" {
        interface Registry {
            Count: number;
        }
    }

使用 countDistinct 在聚合函数内添加不同的表达式。

¥Use countDistinct to add a distinct expression inside the aggregate function.

    knex('users').countDistinct('active')

min #

.min(column|columns|raw, [options])

获取指定列或列数组的最小值（请注意，某些驱动程序不支持多列）。还接受原始表达式。

¥Gets the minimum value for the specified column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions.

knex('users').min('age')

knex('users').min('age', {as: 'a'})

knex('users').min('age as a')

knex('users').min({ a: 'age' })

knex('users').min({ a: 'age', b: 'experience' })

knex('users').min('age', 'logins')

knex('users').min({ min: ['age', 'logins'] })

knex('users').min(knex.raw('??', ['age']))

max #

.max(column|columns|raw, [options])

获取指定列或列数组的最大值（请注意，某些驱动程序不支持多列）。还接受原始表达式。

¥Gets the maximum value for the specified column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions.

knex('users').max('age')

knex('users').max('age', {as: 'a'})

knex('users').max('age as a')

knex('users').max({ a: 'age' })

knex('users').max('age', 'logins')

knex('users').max({ max: ['age', 'logins'] })

knex('users').max({ max: 'age', exp: 'experience' })

knex('users').max(knex.raw('??', ['age']))

sum #

.sum(column|columns|raw)

检索给定列或列数组的值的总和（请注意，某些驱动程序不支持多列）。还接受原始表达式。

¥Retrieve the sum of the values of a given column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions.

knex('users').sum('products')

knex('users').sum('products as p')

knex('users').sum({ p: 'products' })

knex('users').sum('products', 'orders')

knex('users').sum({ sum: ['products', 'orders'] })

knex('users').sum(knex.raw('??', ['products']))

使用 sumDistinct 在聚合函数内添加不同的表达式。

¥Use sumDistinct to add a distinct expression inside the aggregate function.

    knex('users').sumDistinct('products')

avg #

.avg(column|columns|raw)

检索给定列或列数组的平均值（请注意，某些驱动程序不支持多列）。还接受原始表达式。

¥Retrieve the average of the values of a given column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions.

knex('users').avg('age')

knex('users').avg('age as a')

knex('users').avg({ a: 'age' })

knex('users').avg('age', 'logins')

knex('users').avg({ avg: ['age', 'logins'] })

knex('users').avg(knex.raw('??', ['age']))

使用 avgDistinct 在聚合函数内添加不同的表达式。

¥Use avgDistinct to add a distinct expression inside the aggregate function.

    knex('users').avgDistinct('age')

increment #

.increment(column, amount)

将列值增加指定的量。column 支持对象语法。

¥Increments a column value by the specified amount. Object syntax is supported for column.

knex('accounts')
  .where('userid', '=', 1)
  .increment('balance', 10)

knex('accounts')
  .where('id', '=', 1)
  .increment({
    balance: 10,
    times: 1,
  })

decrement #

.decrement(column, amount)

将列值减少指定的量。column 支持对象语法。

¥Decrements a column value by the specified amount. Object syntax is supported for column.

knex('accounts')
  .where('userid', '=', 1)
  .decrement('balance', 5)

knex('accounts')
  .where('id', '=', 1)
  .decrement({
    balance: 50,
  })

truncate #

.truncate()

截断当前表。

¥Truncates the current table.

knex('accounts').truncate()

pluck #

.pluck(id)

这将从结果中的每一行中提取指定的列，产生一个解析为所选值数组的 promise。

¥This will pluck the specified column from each row in your results, yielding a promise which resolves to the array of values selected.

knex.table('users')
  .pluck('id')
  .then(function(ids) { console.log(ids); });

first #

.first([columns])

与 select 类似，但仅检索并解析查询中的第一条记录。

¥Similar to select, but only retrieves & resolves with the first record from the query.

knex.table('users')
  .first('id', 'name')
  .then(function(row) { console.log(row); });

hintComment #

.hintComment(hint|hints)

使用类似注释的语法 /*+ ... */ 向查询添加提示。MySQL 和 Oracle 使用此语法进行优化器提示。此外，各种数据库代理和路由也使用此语法来传递提示以改变其行为。在其他方言中，提示会被视为简单的注释而被忽略。

¥Add hints to the query using comment-like syntax /*+ ... */. MySQL and Oracle use this syntax for optimizer hints. Also various DB proxies and routers use this syntax to pass hints to alter their behavior. In other dialects the hints are ignored as simple comments.

knex('accounts')
  .where('userid', '=', 1)
  .hintComment('NO_ICP(accounts)')

comment #

.comment(comment)

使用语法 /* ... */ 将注释添加到 sql 查询中。有些字符是禁止使用的，例如 /*、*/ 和 ?。

¥Prepend comment to the sql query using the syntax /* ... */. Some characters are forbidden such as /*, */ and ?.

knex('users')
  .where('id', '=', 1)
  .comment('Get user by id')

clone #

.clone()

克隆当前查询链，对于在其他查询中重复使用部分查询片段而不改变原始查询非常有用。

¥Clones the current query chain, useful for re-using partial query snippets in other queries without mutating the original.

denseRank #

.denseRank(alias, mixed)

在你的查询中添加 dense_rank() 调用。对于以下所有查询，如果不需要，可以将别名设置为假值。

¥Add a dense_rank() call to your query. For all the following queries, alias can be set to a falsy value if not needed.

字符串语法 — .denseRank(alias, orderByClause, [partitionByClause]) ：

¥String Syntax — .denseRank(alias, orderByClause, [partitionByClause]) :

knex('users')
  .select('*')
  .denseRank('alias_name', 'email', 'firstName')

它还接受字符串数组作为参数：

¥It also accepts arrays of strings as argument :

knex('users')
  .select('*')
  .denseRank(
    'alias_name', 
    ['email', 'address'], 
    ['firstName', 'lastName']
  )

原始语法 — .denseRank(alias, rawQuery) ：

¥Raw Syntax — .denseRank(alias, rawQuery) :

knex('users').select('*')
  .denseRank(
    'alias_name', 
    knex.raw('order by ??', ['email']
  ))

函数语法 — .denseRank(别名, 函数) :

¥Function Syntax — .denseRank(alias, function) :

使用 orderBy() 和 partitionBy() （均可链式）来构建查询：

¥Use orderBy() and partitionBy() (both chainable) to build your query :

knex('users')
  .select('*')
  .denseRank('alias_name', function() {
    this.orderBy('email').partitionBy('firstName')
  })

rank #

.rank(alias, mixed)

将 rank() 调用添加到你的查询中。对于以下所有查询，如果不需要，可以将别名设置为假值。

¥Add a rank() call to your query. For all the following queries, alias can be set to a falsy value if not needed.

字符串语法 — .rank(alias, orderByClause, [partitionByClause]) ：

¥String Syntax — .rank(alias, orderByClause, [partitionByClause]) :

knex('users')
  .select('*')
  .rank('alias_name', 'email', 'firstName')

它还接受字符串数组作为参数：

¥It also accepts arrays of strings as argument :

knex('users')
  .select('*')
  .rank(
    'alias_name', 
    ['email', 'address'], 
    ['firstName', 'lastName']
  )

原始语法 — .rank(alias, rawQuery) ：

¥Raw Syntax — .rank(alias, rawQuery) :

knex('users')
  .select('*')
  .rank('alias_name', knex.raw('order by ??', ['email']))

函数语法 — .rank(别名, 函数) :

¥Function Syntax — .rank(alias, function) :

使用 orderBy() 和 partitionBy() （均可链式）来构建查询：

¥Use orderBy() and partitionBy() (both chainable) to build your query :

knex('users').select('*').rank('alias_name', function() {
  this.orderBy('email').partitionBy('firstName')
})

rowNumber #

.rowNumber(alias, mixed)

将 row_number() 调用添加到你的查询中。对于以下所有查询，如果不需要，可以将别名设置为假值。

¥Add a row_number() call to your query. For all the following queries, alias can be set to a falsy value if not needed.

字符串语法 — .rowNumber(alias, orderByClause, [partitionByClause]) ：

¥String Syntax — .rowNumber(alias, orderByClause, [partitionByClause]) :

knex('users')
  .select('*')
  .rowNumber('alias_name', 'email', 'firstName')

它还接受字符串数组作为参数：

¥It also accepts arrays of strings as argument :

knex('users')
  .select('*')
  .rowNumber(
    'alias_name', 
    ['email', 'address'], 
    ['firstName', 'lastName']
  )

原始语法 — .rowNumber(alias, rawQuery) ：

¥Raw Syntax — .rowNumber(alias, rawQuery) :

knex('users')
  .select('*')
  .rowNumber(
    'alias_name', 
    knex.raw('order by ??', ['email'])
  )

函数语法 — .rowNumber(别名, 函数) :

¥Function Syntax — .rowNumber(alias, function) :

使用 orderBy() 和 partitionBy() （均可链式）来构建查询：

¥Use orderBy() and partitionBy() (both chainable) to build your query :

knex('users')
  .select('*')
  .rowNumber('alias_name', function() {
    this.orderBy('email').partitionBy('firstName')
  })

partitionBy #

.partitionBy(column, direction)

对 rowNumber、denseRank、特定列之后的排名进行分区。如果未提供方向，则默认为升序。

¥Partitions rowNumber, denseRank, rank after a specific column or columns. If direction is not supplied it will default to ascending order.

无方向排序：

¥No direction sort :

knex('users')
  .select('*')
  .rowNumber('alias_name', function() {
    this.partitionBy('firstName');
  });

使用方向排序：

¥With direction sort :

knex('users')
  .select('*')
  .rowNumber('alias_name', function() {
    this.partitionBy('firstName', 'desc');
  });

对于多对象：

¥With multiobject :

knex('users')
  .select('*')
  .rowNumber('alias_name', function() {
    this.partitionBy([
      { column: 'firstName', order: 'asc' }, 
      { column: 'lastName', order: 'desc' }
    ]);
  });

modify #

*.modify(fn, arguments)

允许将查询片段和常见行为封装和重用为函数。回调函数应接收查询构建器作为其第一个参数，然后是传递用于修改的其余（可选）参数。

¥Allows encapsulating and re-using query snippets and common behaviors as functions. The callback function should receive the query builder as its first argument, followed by the rest of the (optional) parameters passed to modify.

const withUserName = function(queryBuilder, foreignKey) {
  queryBuilder.leftJoin(
    'users', 
    foreignKey, 
    'users.id'
  ).select('users.user_name');
};
knex.table('articles')
  .select('title', 'body')
  .modify(withUserName, 'articles_user.id')
  .then(function(article) {
    console.log(article.user_name);
  });

columnInfo #

.columnInfo([columnName])

返回一个对象，其中包含有关当前表的列信息，或者返回一个单独的列（如果传递了一个列），返回一个具有以下键的对象：

¥Returns an object with the column info about the current table, or an individual column if one is passed, returning an object with the following keys:

• defaultValue:列的默认值

  ¥defaultValue: the default value for the column

• type:列类型

  ¥type: the column type

• maxLength:为列设置的最大长度

  ¥maxLength: the max length set for the column

• nullable:该列是否可以为空

  ¥nullable: whether the column may be null

knex('users').columnInfo().then(function(info) { /*...*/ });

debug #

.debug([enabled])

覆盖当前查询链的全局调试设置。如果省略 enabled，则将打开查询调试。

¥Overrides the global debug setting for the current query chain. If enabled is omitted, query debugging will be turned on.

connection #

.connection(dbConnection)

该方法设置用于查询的数据库连接，而不使用连接池。你应该将相应驱动程序的 acquireConnection() 返回的同一对象传递给它

¥The method sets the db connection to use for the query without using the connection pool. You should pass to it the same object that acquireConnection() for the corresponding driver returns

const Pool = require('pg-pool');
const pool = new Pool({ /* ... */ });
const connection = await pool.connect();

try {
  return await knex.connection(connection); // knex here is a query builder with query already built
} catch (error) {
  // Process error
} finally {
  connection.release();
}

options #

.options()

允许混合数据库客户端特定库定义的其他选项：

¥Allows for mixing in additional options as defined by database client specific libraries:

    knex('accounts as a1')
      .leftJoin('accounts as a2', function() {
        this.on('a1.email', '<>', 'a2.email');
      })
      .select(['a1.email', 'a2.email'])
      .where(knex.raw('a1.id = 1'))
      .options({ nestTables: true, rowMode: 'array' })
      .limit(2)
      .then({ /*...*/ })

queryContext #

.queryContext(context)

允许配置要传递到 wrapIdentifier 和 postProcessResponse 钩子的上下文：

¥Allows for configuring a context to be passed to the wrapIdentifier and postProcessResponse hooks:

    knex('accounts as a1')
      .queryContext({ foo: 'bar' })
      .select(['a1.email', 'a2.email'])

上下文可以是任何类型的值，并且将不加修改地传递给钩子。但是，请注意，当查询构建器实例为 cloned 时，对象将进行浅克隆，这意味着它们将包含原始对象的所有属性，但不会是相同的对象引用。这允许修改克隆的查询构建器实例的上下文。

¥The context can be any kind of value and will be passed to the hooks without modification. However, note that objects will be shallow-cloned when a query builder instance is cloned, which means that they will contain all the properties of the original object but will not be the same object reference. This allows modifying the context for the cloned query builder instance.

不带参数调用 queryContext 将返回为查询构建器实例配置的任何上下文。

¥Calling queryContext with no arguments will return any context configured for the query builder instance.

扩展查询构建器 #

¥Extending Query Builder

重要的：此功能是实验性的，其 API 将来可能会发生变化。

¥Important: this feature is experimental and its API may change in the future.

它允许向查询构建器添加自定义函数。

¥It allows to add custom function to the Query Builder.

示例：

¥Example:

const { knex } = require('knex');
knex.QueryBuilder.extend('customSelect', function(value) {
  return this.select(this.client.raw(`${value} as value`));
});

const meaningOfLife = await knex('accounts')
  .customSelect(42);

如果使用 TypeScript，你可以使用自定义方法扩展 QueryBuilder 接口。

¥If using TypeScript, you can extend the QueryBuilder interface with your custom method.

1. 在 @types 文件夹（或任何其他文件夹）内创建 knex.d.ts 文件。

   ¥Create a knex.d.ts file inside a @types folder (or any other folder).

// knex.d.ts

import { Knex as KnexOriginal } from 'knex';

declare module 'knex' {
  namespace Knex {
    interface QueryInterface {
      customSelect<TRecord, TResult>(value: number): KnexOriginal.QueryBuilder<TRecord, TResult>;
    }
  }
}

2. 将新的 @types 文件夹添加到 tsconfig.json 中的 typeRoots。

   ¥Add the new @types folder to typeRoots in your tsconfig.json.

// tsconfig.json
{
  "compilerOptions": {
    "typeRoots": [
      "node_modules/@types",
      "@types"
    ],
  }
}

Where 子句 #

¥Where Clauses

存在多种方法来辅助动态 where 子句。在许多地方，可以使用函数代替值，构造子查询。在大多数地方，现有的 knex 查询可用于组成子查询等。请查看每种方法的一些示例以了解使用说明：

¥Several methods exist to assist in dynamic where clauses. In many places functions may be used in place of values, constructing subqueries. In most places existing knex queries may be used to compose sub-queries, etc. Take a look at a few of the examples for each method for instruction on use:

重要的：为 knex 中的任何 where 函数提供 undefined 值将导致 knex 在 sql 编译期间抛出错误。这既是为了你，也是为了我们。Knex 无法知道如何处理 where 子句中未定义的值，并且通常一开始就提供一个值将是一个编程错误。该错误将抛出一条包含查询类型和已编译查询字符串的消息。示例：

¥Important: Supplying knex with an undefined value to any of the where functions will cause knex to throw an error during sql compilation. This is both for yours and our sake. Knex cannot know what to do with undefined values in a where clause, and generally it would be a programmatic error to supply one to begin with. The error will throw a message containing the type of query and the compiled query-string. Example:

knex('accounts')
  .where('login', undefined)
  .select()
  .toSQL()

where #

.where(mixed) .orWhere

对象语法：

¥Object Syntax:

knex('users').where({
  first_name: 'Test',
  last_name:  'User'
}).select('id')

键，值：

¥Key, Value:

knex('users').where('id', 1)

功能：

¥Functions:

knex('users')
  .where((builder) =>
    builder
      .whereIn('id', [1, 11, 15])
      .whereNotIn('id', [17, 19])
  )
  .andWhere(function() {
    this.where('id', '>', 10)
  })

分组链：

¥Grouped Chain:

knex('users').where(function() {
  this.where('id', 1).orWhere('id', '>', 10)
}).orWhere({name: 'Tester'})

运算符：

¥Operator:

knex('users').where('columnName', 'like', '%rowlikeme%')

上面的查询演示了返回在指定列中出现特定模式的所有用户的常见用例。

¥The above query demonstrates the common use case of returning all users for which a specific pattern appears within a designated column.

knex('users').where('votes', '>', 100)

const subquery = knex('users')
  .where('votes', '>', 100)
  .andWhere('status', 'active')
  .orWhere('name', 'John')
  .select('id');

knex('accounts').where('id', 'in', subquery)

带有对象的 .orWhere 会自动封装语句并创建 or (and - and - and) 子句

¥.orWhere with an object automatically wraps the statement and creates an or (and - and - and) clause

knex('users')
  .where('id', 1)
  .orWhere({votes: 100, user: 'knex'})

whereNot #

.whereNot(mixed) .orWhereNot

对象语法：

¥Object Syntax:

knex('users').whereNot({
  first_name: 'Test',
  last_name:  'User'
}).select('id')

键，值：

¥Key, Value:

knex('users').whereNot('id', 1)

分组链：

¥Grouped Chain:

knex('users').whereNot(function() {
  this.where('id', 1).orWhereNot('id', '>', 10)
}).orWhereNot({name: 'Tester'})

运算符：

¥Operator:

knex('users').whereNot('votes', '>', 100)

const subquery = knex('users')
  .whereNot('votes', '>', 100)
  .andWhere('status', 'active')
  .orWhere('name', 'John')
  .select('id');

knex('accounts').where('id', 'not in', subquery)

whereIn #

.whereIn(column|columns, array|callback|builder) .orWhereIn

.where('id', 'in', obj) 的简写形式，.whereIn 和 .orWhereIn 方法向查询添加 "在哪里" 子句。请注意，传递空数组作为值会导致查询永远不会返回任何行 (WHERE 1 = 0)

¥Shorthand for .where('id', 'in', obj), the .whereIn and .orWhereIn methods add a "where in" clause to the query. Note that passing empty array as the value results in a query that never returns any rows (WHERE 1 = 0)

knex.select('name').from('users')
  .whereIn('id', [1, 2, 3])
  .orWhereIn('id', [4, 5, 6])

knex.select('name').from('users')
  .whereIn('account_id', function() {
    this.select('id').from('accounts');
  })

const subquery = knex.select('id').from('accounts');

knex.select('name').from('users')
  .whereIn('account_id', subquery)

knex.select('name').from('users')
  .whereIn(
    ['account_id', 'email'], 
    [
      [3, 'test3@example.com'], 
      [4, 'test4@example.com']
    ]
  )

knex.select('name').from('users')
  .whereIn(
    ['account_id', 'email'], 
    knex.select('id', 'email')
      .from('accounts')
  )

whereNotIn #

.whereNotIn(column, array|callback|builder) .orWhereNotIn

knex('users').whereNotIn('id', [1, 2, 3])

knex('users')
  .where('name', 'like', '%Test%')
  .orWhereNotIn('id', [1, 2, 3])

whereNull #

.whereNull(column) .orWhereNull

knex('users').whereNull('updated_at')

whereNotNull #

.whereNotNull(column) .orWhereNotNull

knex('users').whereNotNull('created_at')

whereExists #

.whereExists(builder | callback) .orWhereExists

knex('users').whereExists(function() {
  this.select('*')
    .from('accounts')
    .whereRaw('users.account_id = accounts.id');
})

knex('users')
  .whereExists(
    knex.select('*')
      .from('accounts')
      .whereRaw('users.account_id = accounts.id')
  )

whereNotExists #

.whereNotExists(builder | callback) .orWhereNotExists

knex('users').whereNotExists(function() {
  this.select('*')
    .from('accounts')
    .whereRaw('users.account_id = accounts.id');
})

knex('users')
  .whereNotExists(
    knex.select('*')
      .from('accounts')
      .whereRaw('users.account_id = accounts.id')
  )

whereBetween #

.whereBetween(column, range) .orWhereBetween

knex('users').whereBetween('votes', [1, 100])

whereNotBetween #

.whereNotBetween(column, range) .orWhereNotBetween

knex('users').whereNotBetween('votes', [1, 100])

whereRaw #

.whereRaw(query, [bindings])

.where(knex.raw(query)) 的便捷助手。

¥Convenience helper for .where(knex.raw(query)).

knex('users').whereRaw('id = ?', [1])

whereLike #

.whereLike(column, string|builder|raw) .orWhereLike

添加一个 where 子句，对具有给定值的给定列进行区分大小写的子字符串比较。

¥Adds a where clause with case-sensitive substring comparison on a given column with a given value.

knex('users').whereLike('email', '%mail%')

knex('users')
  .whereLike('email', '%mail%')
  .andWhereLike('email', '%.com')
  .orWhereLike('email', '%name%')

whereILike #

.whereILike(column, string|builder|raw) .orWhereILike

添加一个 where 子句，对具有给定值的给定列进行不区分大小写的子字符串比较。

¥Adds a where clause with case-insensitive substring comparison on a given column with a given value.

knex('users').whereILike('email', '%mail%')

knex('users')
  .whereILike('email', '%MAIL%')
  .andWhereILike('email', '%.COM')
  .orWhereILike('email', '%NAME%')

whereJsonObject #

.whereJsonObject(column, string|json|builder|raw)

添加一个 where 子句，对给定的 json 列进行 json 对象比较。

¥Adds a where clause with json object comparison on given json column.

knex('users')
  .whereJsonObject('json_col', { "name" : "user_name"})

whereJsonPath #

.whereJsonPath(column, jsonPath, operator, value)

添加一个 where 子句，对给定运算符和值的 JsonPath 返回的值进行比较。

¥Adds a where clause with comparison of a value returned by a JsonPath given an operator and a value.

knex('users')
  .whereJsonPath('json_col', '$.age', '>', 18)

knex('users')
  .whereJsonPath('json_col', '$.name', '=', 'username')

whereJsonSupersetOf #

.whereJsonSupersetOf(column, string|json|builder|raw)

添加一个 where 子句，如果列给出的 json 包含给定值，则比较结果为 true。仅适用于 MySQL、PostgreSQL 和 CockroachDB。

¥Adds a where clause where the comparison is true if a json given by the column include a given value. Only on MySQL, PostgreSQL and CockroachDB.

knex('users')
  .whereJsonSupersetOf('hobbies', { "sport" : "foot" })

whereJsonSubsetOf #

.whereJsonSubsetOf(column, string|json|builder|raw)

添加一个 where 子句，如果给定值中包含列给出的 json，则比较结果为 true。仅适用于 MySQL、PostgreSQL 和 CockroachDB。

¥Adds a where clause where the comparison is true if a json given by the column is included in a given value. Only on MySQL, PostgreSQL and CockroachDB.

// given a hobby column with { "sport" : "tennis" }, 
// the where clause is true
knex('users')
  .whereJsonSubsetOf(
    'hobby', 
    { "sport" : "tennis", "book" : "fantasy" }
  )

连接方法 #

¥Join Methods

提供了多种有助于构建连接的方法。

¥Several methods are provided which assist in building joins.

join #

.join(table, first, [operator], second)

连接构建器可用于指定表之间的连接，第一个参数是连接表，接下来的三个参数分别是第一个连接列、连接运算符和第二个连接列。

¥The join builder can be used to specify joins between tables, with the first argument being the joining table, the next three arguments being the first join column, the join operator and the second join column, respectively.

knex('users')
  .join('contacts', 'users.id', '=', 'contacts.user_id')
  .select('users.id', 'contacts.phone')

knex('users')
  .join('contacts', 'users.id', 'contacts.user_id')
  .select('users.id', 'contacts.phone')

对于分组联接，指定一个函数作为联接查询的第二个参数，并使用 on 与 orOn 或 andOn 来创建用括号分组的联接。

¥For grouped joins, specify a function as the second argument for the join query, and use on with orOn or andOn to create joins that are grouped with parentheses.

knex.select('*').from('users').join('accounts', function() {
  this
    .on('accounts.id', '=', 'users.account_id')
    .orOn('accounts.owner_id', '=', 'users.id')
})

对于嵌套连接语句，指定一个函数作为 on、orOn 或 andOn 的第一个参数

¥For nested join statements, specify a function as first argument of on, orOn or andOn

knex.select('*').from('users').join('accounts', function() {
  this.on(function() {
    this.on('accounts.id', '=', 'users.account_id')
    this.orOn('accounts.owner_id', '=', 'users.id')
  })
})

也可以使用对象来表示连接语法。

¥It is also possible to use an object to represent the join syntax.

knex.select('*')
  .from('users')
  .join('accounts', {'accounts.id': 'users.account_id'})

如果需要在联接中使用字面量值（字符串、数字或布尔值）而不是列，请使用 knex.raw。

¥If you need to use a literal value (string, number, or boolean) in a join instead of a column, use knex.raw.

knex.select('*')
  .from('users')
  .join(
    'accounts', 
    'accounts.type',
    knex.raw('?', ['admin'])
  )

innerJoin #

.innerJoin(table, mixed)

knex
  .from('users')
  .innerJoin('accounts', 'users.id', 'accounts.user_id')

knex
  .table('users')
  .innerJoin(
    'accounts', 
    'users.id', 
    '=', 
    'accounts.user_id'
  )

knex('users')
  .innerJoin('accounts', function() {
    this
      .on('accounts.id', '=', 'users.account_id')
      .orOn('accounts.owner_id', '=', 'users.id')
  })

leftJoin #

.leftJoin(table, mixed)

knex.select('*')
  .from('users')
  .leftJoin('accounts', 'users.id', 'accounts.user_id')

knex.select('*')
  .from('users')
  .leftJoin('accounts', function() {
    this
      .on('accounts.id', '=', 'users.account_id')
      .orOn('accounts.owner_id', '=', 'users.id')
  })

leftOuterJoin #

.leftOuterJoin(table, mixed)

knex.select('*')
  .from('users')
  .leftOuterJoin('accounts', 'users.id', 'accounts.user_id')

knex.select('*')
  .from('users')
  .leftOuterJoin('accounts', function() {
    this
      .on('accounts.id', '=', 'users.account_id')
      .orOn('accounts.owner_id', '=', 'users.id')
  })

rightJoin #

.rightJoin(table, mixed)

knex.select('*')
  .from('users')
  .rightJoin('accounts', 'users.id', 'accounts.user_id')

knex.select('*')
  .from('users')
  .rightJoin('accounts', function() {
    this
      .on('accounts.id', '=', 'users.account_id')
      .orOn('accounts.owner_id', '=', 'users.id')
  })

rightOuterJoin #

.rightOuterJoin(table, mixed)

knex.select('*')
  .from('users')
  .rightOuterJoin(
    'accounts', 
    'users.id', 
    'accounts.user_id'
  )

knex.select('*')
  .from('users')
  .rightOuterJoin('accounts', function() {
    this
      .on('accounts.id', '=', 'users.account_id')
      .orOn('accounts.owner_id', '=', 'users.id')
  })

fullOuterJoin #

.fullOuterJoin(table, mixed)

knex.select('*')
  .from('users')
  .fullOuterJoin('accounts', 'users.id', 'accounts.user_id')

knex.select('*')
  .from('users')
  .fullOuterJoin('accounts', function() {
    this
      .on('accounts.id', '=', 'users.account_id')
      .orOn('accounts.owner_id', '=', 'users.id')
  })

crossJoin #

.crossJoin(table, mixed)

仅 MySQL 和 SQLite3 支持交叉连接条件。对于连接条件，请使用 innerJoin。

¥Cross join conditions are only supported in MySQL and SQLite3. For join conditions rather use innerJoin.

knex.select('*')
  .from('users')
  .crossJoin('accounts')

knex.select('*')
  .from('users')
  .crossJoin('accounts', 'users.id', 'accounts.user_id')

knex.select('*')
  .from('users')
  .crossJoin('accounts', function() {
    this
      .on('accounts.id', '=', 'users.account_id')
      .orOn('accounts.owner_id', '=', 'users.id')
  })

joinRaw #

.joinRaw(sql, [bindings])

knex.select('*')
  .from('accounts')
  .joinRaw('natural full join table1').where('id', 1)

knex.select('*')
  .from('accounts')
  .join(knex.raw('natural full join table1')).where('id', 1)

OnClauses #

onIn #

.onIn(column, values)

向查询添加 onIn 子句。

¥Adds a onIn clause to the query.

knex.select('*')
  .from('users')
  .join('contacts', function() {
    this
      .on('users.id', '=', 'contacts.id')
      .onIn('contacts.id', [7, 15, 23, 41])
  })

onNotIn #

.onNotIn(column, values)

向查询添加 onNotIn 子句。

¥Adds a onNotIn clause to the query.

knex.select('*')
  .from('users')
  .join('contacts', function() {
    this
      .on('users.id', '=', 'contacts.id')
      .onNotIn('contacts.id', [7, 15, 23, 41])
  })

onNull #

.onNull(column)

向查询添加 onNull 子句。

¥Adds a onNull clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this
    .on('users.id', '=', 'contacts.id')
    .onNull('contacts.email')
})

onNotNull #

.onNotNull(column)

向查询添加 onNotNull 子句。

¥Adds a onNotNull clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this
    .on('users.id', '=', 'contacts.id')
    .onNotNull('contacts.email')
})

onExists #

.onExists(builder | callback)

向查询添加 onExists 子句。

¥Adds a onExists clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this
    .on('users.id', '=', 'contacts.id')
    .onExists(function() {
      this.select('*')
        .from('accounts')
        .whereRaw('users.account_id = accounts.id');
    })
})

onNotExists #

.onNotExists(builder | callback)

向查询添加 onNotExists 子句。

¥Adds a onNotExists clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this
    .on('users.id', '=', 'contacts.id')
    .onNotExists(function() {
      this.select('*')
        .from('accounts')
        .whereRaw('users.account_id = accounts.id');
    })
})

onBetween #

.onBetween(column, range)

向查询添加 onBetween 子句。

¥Adds a onBetween clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this
    .on('users.id', '=', 'contacts.id')
    .onBetween('contacts.id', [5, 30])
})

onNotBetween #

.onNotBetween(column, range)

向查询添加 onNotBetween 子句。

¥Adds a onNotBetween clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this
    .on('users.id', '=', 'contacts.id')
    .onNotBetween('contacts.id', [5, 30])
})

onJsonPathEquals #

.onJsonPathEquals(column, range)

向查询添加 onJsonPathEquals 子句。该子句对两个 json 列上的两个 json 路径返回的值执行联接。

¥Adds a onJsonPathEquals clause to the query. The clause performs a join on value returned by two json paths on two json columns.

knex('cities')
  .select(
    'cities.name as cityName', 
    'country.name as countryName'
  )
  .join('country', function () {
    this.onJsonPathEquals(
      // json column in cities
      'country_name', 
      // json path to country name in 'country_name' column
      '$.country.name', 
      // json column in country
      'description', 
       // json field in 'description' column
      '$.name'
    );
  })

ClearClauses #

clear #

.clear(statement)

从查询中清除指定的运算符。可用的运算符：'select' 别名 'columns'、'with'、'select'、'columns'、'where'、'union'、'join'、'group'、'order'、'having'、'limit'、'offset'、'counter'、'counters'。Counter(s) alias for method .clearCounter()

¥Clears the specified operator from the query. Available operators: 'select' alias 'columns', 'with', 'select', 'columns', 'where', 'union', 'join', 'group', 'order', 'having', 'limit', 'offset', 'counter', 'counters'. Counter(s) alias for method .clearCounter()

knex.select('email', 'name')
  .from('users')
  .where('id', '<', 10)
  .clear('select')
  .clear('where')

clearSelect #

.clearSelect()

已弃用，请使用清除('select')。清除查询中的所有 select 子句，不包括子查询。

¥Deprecated, use clear('select'). Clears all select clauses from the query, excluding subqueries.

    knex.select('email', 'name').from('users').clearSelect()

clearWhere #

.clearWhere()

已弃用，请使用清除('where')。清除查询中的所有 where 子句，不包括子查询。

¥Deprecated, use clear('where'). Clears all where clauses from the query, excluding subqueries.

knex.select('email', 'name')
  .from('users')
  .where('id', 1)
  .clearWhere()

clearGroup #

.clearGroup()

已弃用，请使用清除('group')。清除查询中的所有组子句，不包括子查询。

¥Deprecated, use clear('group'). Clears all group clauses from the query, excluding subqueries.

knex.select().from('users').groupBy('id').clearGroup()

clearOrder #

.clearOrder()

已弃用，请使用清除('order')。清除查询中的所有 order 子句，不包括子查询。

¥Deprecated, use clear('order'). Clears all order clauses from the query, excluding subqueries.

knex.select()
  .from('users')
  .orderBy('name', 'desc')
  .clearOrder()

clearHaving #

.clearHaving()

已弃用，请使用清除('having')。清除查询中的所有 having 子句，不包括子查询。

¥Deprecated, use clear('having'). Clears all having clauses from the query, excluding subqueries.

knex.select()
  .from('users')
  .having('id', '>', 5)
  .clearHaving()

clearCounters #

.clearCounters()

清除查询中的所有增量/减量子句。

¥Clears all increments/decrements clauses from the query.

  knex('accounts')
    .where('id', '=', 1)
    .update({ email: 'foo@bar.com' })
    .decrement({
      balance: 50,
    })
    .clearCounters()