其他数据类型

除了模型基础指南中提到的最常见的数据类型之外，Sequelize 还提供了几种其他数据类型。

¥Apart from the most common data types mentioned in the Model Basics guide, Sequelize provides several other data types.

范围（仅限 PostgreSQL）

¥Ranges (PostgreSQL only)

DataTypes.RANGE(DataTypes.INTEGER)    // int4range
DataTypes.RANGE(DataTypes.BIGINT)     // int8range
DataTypes.RANGE(DataTypes.DATE)       // tstzrange
DataTypes.RANGE(DataTypes.DATEONLY)   // daterange
DataTypes.RANGE(DataTypes.DECIMAL)    // numrange

由于范围类型具有关于其绑定包含/排除的额外信息，因此仅使用元组在 JavaScript 中表示它们并不是很简单。

¥Since range types have extra information for their bound inclusion/exclusion it's not very straightforward to just use a tuple to represent them in javascript.

当提供范围作为值时，你可以从以下 API 中进行选择：

¥When supplying ranges as values you can choose from the following APIs:

// defaults to inclusive lower bound, exclusive upper bound
const range = [
  new Date(Date.UTC(2016, 0, 1)),
  new Date(Date.UTC(2016, 1, 1))
];
// '["2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'

// control inclusion
const range = [
  { value: new Date(Date.UTC(2016, 0, 1)), inclusive: false },
  { value: new Date(Date.UTC(2016, 1, 1)), inclusive: true },
];
// '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"]'

// composite form
const range = [
  { value: new Date(Date.UTC(2016, 0, 1)), inclusive: false },
  new Date(Date.UTC(2016, 1, 1)),
];
// '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'

const Timeline = sequelize.define('Timeline', {
  range: DataTypes.RANGE(DataTypes.DATE)
});

await Timeline.create({ range });

但是，检索到的范围值始终以对象数组的形式出现。例如，如果存储的值为 ("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"]，则在 finder 查询后你将得到：

¥However, retrieved range values always come in the form of an array of objects. For example, if the stored value is ("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"], after a finder query you will get:

[
  { value: Date, inclusive: false },
  { value: Date, inclusive: true }
]

使用范围类型更新实例后，你需要调用 reload() 或使用 returning: true 选项。

¥You will need to call reload() after updating an instance with a range type or use the returning: true option.

特别案例

¥Special Cases

// empty range:
Timeline.create({ range: [] }); // range = 'empty'

// Unbounded range:
Timeline.create({ range: [null, null] }); // range = '[,)'
// range = '[,"2016-01-01 00:00:00+00:00")'
Timeline.create({ range: [null, new Date(Date.UTC(2016, 0, 1))] });

// Infinite range:
// range = '[-infinity,"2016-01-01 00:00:00+00:00")'
Timeline.create({ range: [-Infinity, new Date(Date.UTC(2016, 0, 1))] });

网络地址

¥Network Addresses

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

序列化数据类型	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	雪花	db2	ibmi
CIDR	CIDR	❌	❌	❌	❌	❌	❌	❌
INET	INET	❌	❌	❌	❌	❌	❌	❌
MACADDR	MACADDR	❌	❌	❌	❌	❌	❌	❌

数组（仅限 PostgreSQL）

¥Arrays (PostgreSQL only)

// Defines an array of DataTypes.SOMETHING.
DataTypes.ARRAY(/* DataTypes.SOMETHING */)

// For example
// VARCHAR(255)[]
DataTypes.ARRAY(DataTypes.STRING)
// VARCHAR(255)[][]
DataTypes.ARRAY(DataTypes.ARRAY(DataTypes.STRING))

BLOB

DataTypes.BLOB                // BLOB (bytea for PostgreSQL)
DataTypes.BLOB('tiny')        // TINYBLOB (bytea for PostgreSQL)
DataTypes.BLOB('medium')      // MEDIUMBLOB (bytea for PostgreSQL)
DataTypes.BLOB('long')        // LONGBLOB (bytea for PostgreSQL)

blob 数据类型允许你以字符串和缓冲区的形式插入数据。但是，当使用 Sequelize 从数据库检索 blob 时，它将始终作为缓冲区检索。

¥The blob datatype allows you to insert data both as strings and as buffers. However, when a blob is retrieved from database with Sequelize, it will always be retrieved as a buffer.

枚举

¥ENUMs

ENUM 是一种仅接受少数值的数据类型，指定为列表。

¥The ENUM is a data type that accepts only a few values, specified as a list.

DataTypes.ENUM('foo', 'bar') // An ENUM with allowed values 'foo' and 'bar'

ENUM 也可以使用列定义的 values 字段来指定，如下所示：

¥ENUMs can also be specified with the values field of the column definition, as follows:

sequelize.define('foo', {
  states: {
    type: DataTypes.ENUM,
    values: ['active', 'pending', 'deleted']
  }
});

JSON（仅限 SQLite、MySQL、MariaDB、Oracle 和 PostgreSQL）

¥JSON (SQLite, MySQL, MariaDB, Oracle and PostgreSQL only)

仅 SQLite、MySQL、MariaDB、Oracle 和 PostgreSQL 支持 DataTypes.JSON 数据类型。但是，对 MSSQL 有最低限度的支持（见下文）。

¥The DataTypes.JSON data type is only supported for SQLite, MySQL, MariaDB, Oracle and PostgreSQL. However, there is a minimum support for MSSQL (see below).

PostgreSQL 的注意事项

¥Note for PostgreSQL

PostgreSQL 中的 JSON 数据类型将值存储为纯文本，而不是二进制表示形式。如果你只想存储和检索 JSON 表示形式，那么使用 JSON 将占用更少的磁盘空间和从其输入表示形式构建的时间。但是，如果你想对 JSON 值执行任何操作，你应该更喜欢下面描述的 JSONB 数据类型。

¥The JSON data type in PostgreSQL stores the value as plain text, as opposed to binary representation. If you simply want to store and retrieve a JSON representation, using JSON will take less disk space and less time to build from its input representation. However, if you want to do any operations on the JSON value, you should prefer the JSONB data type described below.

JSONB（仅限 PostgreSQL）

¥JSONB (PostgreSQL only)

PostgreSQL 还支持 JSONB 数据类型：DataTypes.JSONB。可以通过三种不同的方式查询：

¥PostgreSQL also supports a JSONB data type: DataTypes.JSONB. It can be queried in three different ways:

// Nested object
await Foo.findOne({
  where: {
    meta: {
      video: {
        url: {
          [Op.ne]: null
        }
      }
    }
  }
});

// Nested key
await Foo.findOne({
  where: {
    "meta.audio.length": {
      [Op.gt]: 20
    }
  }
});

// Containment
await Foo.findOne({
  where: {
    meta: {
      [Op.contains]: {
        site: {
          url: 'https://google.com'
        }
      }
    }
  }
});

MSSQL

MSSQL 没有 JSON 数据类型，但自 SQL Server 2016 以来，它确实通过某些函数对 JSON 存储为字符串提供了一些支持。使用这些函数，你将能够查询存储在字符串中的 JSON，但任何返回的值都需要单独解析。

¥MSSQL does not have a JSON data type, however it does provide some support for JSON stored as strings through certain functions since SQL Server 2016. Using these functions, you will be able to query the JSON stored in the string, but any returned values will need to be parsed separately.

// ISJSON - to test if a string contains valid JSON
await User.findAll({
  where: sequelize.where(sequelize.fn('ISJSON', sequelize.col('userDetails')), 1)
})

// JSON_VALUE - extract a scalar value from a JSON string
await User.findAll({
  attributes: [[ sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), 'address line 1']]
})

// JSON_VALUE - query a scalar value from a JSON string
await User.findAll({
  where: sequelize.where(sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), '14, Foo Street')
})

// JSON_QUERY - extract an object or array
await User.findAll({
  attributes: [[ sequelize.fn('JSON_QUERY', sequelize.col('userDetails'), '$.address'), 'full address']]
})

杂项数据类型

¥Miscellaneous DataTypes

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

序列化数据类型	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	雪花	db2	ibmi
GEOMETRY	GEOMETRY	GEOMETRY	GEOMETRY	❌	❌	❌	❌	❌
GEOMETRY('POINT')	GEOMETRY(POINT)	POINT	POINT	❌	❌	❌	❌	❌
GEOMETRY('POINT', 4326)	GEOMETRY(POINT,4326)	❌	❌	❌	❌	❌	❌	❌
GEOMETRY('POLYGON')	GEOMETRY(POLYGON)	POLYGON	POLYGON	❌	❌	❌	❌	❌
GEOMETRY('LINESTRING')	GEOMETRY(LINESTRING)	LINESTRING	LINESTRING	❌	❌	❌	❌	❌
GEOGRAPHY	GEOGRAPHY	❌	❌	❌	❌	❌	❌	❌
HSTORE	HSTORE	❌	❌	❌	❌	❌	❌	❌

注意

在 Postgres 中，GEOMETRY 和 GEOGRAPHY 类型是由 PostGIS 扩展 实现的。

¥In Postgres, the GEOMETRY and GEOGRAPHY types are implemented by the PostGIS extension.

在 Postgres 中，如果使用 DataTypes.HSTORE，则必须安装 pg-hstore 包

¥In Postgres, You must install the pg-hstore package if you use DataTypes.HSTORE