生成列

要使用此功能，您需要安装 drizzle-orm@0.32.0 或更高版本，以及 drizzle-kit@0.23.0 或更高版本

SQL 中的生成列是一项功能，允许您在表中创建列，其值基于同一表中其他列的表达式自动计算。这有助于确保数据一致性，简化数据库设计，提高查询性能。

生成列有两种类型：

虚拟（或非持久性）生成列：这些列在查询时动态计算。它们在数据库中不占用存储空间。
存储（或持久性）生成列：这些列在插入或更新行时计算，并将其值存储在数据库中。这使它们可以被索引，并且可以提高查询性能，因为值不需要在每个查询中重新计算。

生成列对于以下情况特别有用：

从现有列派生新数据
自动化计算以避免手动更新
强制数据完整性和一致性
通过将复杂计算保留在数据库架构中简化应用逻辑

生成列的实现和使用在不同的 SQL 数据库中可能有显著差异。PostgreSQL、MySQL 和 SQLite 在生成列方面各自有独特的特性、能力和限制。在这一部分中，我们将详细探讨这些差异，以帮助您了解如何在每个数据库系统中最好地利用生成列。

PostgreSQL

MySQL

SQLite

SingleStore(WIP)

MSSQL

CockroachDB

数据库端

类型: STORED 仅

工作原理

在插入或更新时自动计算基于其他列的值。

能力

通过预计算复杂表达式简化数据访问。
通过生成列的索引支持增强查询性能。

限制

不能指定默认值。
表达式不能引用其他生成列或包含子查询。
修改生成列表达式需要更改架构。
不能直接用于主键、外键或唯一约束。

欲了解更多信息，请查看 PostgreSQL 文档。

Drizzle 端

在 Drizzle 中，你可以在任何列类型上指定 .generatedAlwaysAs() 函数，并添加一个支持的 SQL 查询，这将为你生成该列的数据。

特性

此函数可以以两种方式接受生成表达式：

IMPORTANT

从 1.0.0-beta.12 版本开始的更改

在之前的版本中，.generatedAlwaysAs() 也接受字面量作为表达式。

string

export const test = pgTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(`'hello world!'`),
});

CREATE TABLE "test" (
    "gen_name" text GENERATED ALWAYS AS ('hello world!') STORED
);

sql 标签 - 如果您希望 Drizzle 为您转义一些值

export const test = pgTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(sql`'hello "world"!'`),
});

CREATE TABLE "test" (
    "gen_name" text GENERATED ALWAYS AS ('hello "world"!') STORED
);

callback - 如果您需要引用表中的列

export const test = pgTable("test", {
    name: text("first_name"),
    generatedName: text("gen_name").generatedAlwaysAs(
      (): SQL => sql`'hi, ' || ${test.name} || '!'`
    ),
});

CREATE TABLE "test" (
    "first_name" text,
    "gen_name" text GENERATED ALWAYS AS ('hi, ' || "test"."first_name" || '!') STORED
);

示例生成列与全文搜索

schema.ts

import { SQL, sql } from "drizzle-orm";
import { customType, index, integer, pgTable, text } from "drizzle-orm/pg-core";

const tsVector = customType<{ data: string }>({
  dataType() {
    return "tsvector";
  },
});

export const test = pgTable(
  "test",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    content: text("content"),
    contentSearch: tsVector("content_search", {
      dimensions: 3,
    }).generatedAlwaysAs(
      (): SQL => sql`to_tsvector('english', ${test.content})`
    ),
  },
  (t) => [
    index("idx_content_search").using("gin", t.contentSearch)
  ]
);

CREATE TABLE "test" (
	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "test_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
	"content" text,
	"content_search" "tsvector" GENERATED ALWAYS AS (to_tsvector('english', "test"."content")) STORED
);
--> statement-breakpoint
CREATE INDEX "idx_content_search" ON "test" USING gin ("content_search");

数据库端

类型: STORED, VIRTUAL

工作原理

通过表架构中的表达式定义。
虚拟列在读取操作时计算。
存储列在写入操作时计算并存储。

能力

可用于 SELECT、INSERT、UPDATE 和 DELETE 语句。
可以被索引，包括虚拟和存储列。
可以指定 NOT NULL 和其他约束。

限制

无法直接插入或更新生成列中的值。

欲了解更多信息，请查看 MySQL Alter Generated 文档和 MySQL create generated 文档。

Drizzle 端

特性

此函数可以以两种方式接受生成表达式：

IMPORTANT

从 1.0.0-beta.12 版本开始的更改

在之前的版本中，.generatedAlwaysAs() 也接受字面量作为表达式。

string

export const test = mysqlTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(`'hello world!'`),
});

CREATE TABLE "test" (
    "gen_name" text GENERATED ALWAYS AS ('hello world!') VIRTUAL
);

sql 标签 - 如果您希望 Drizzle 为您转义一些值

export const test = mysqlTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(sql`'hello "world"!'`),
});

CREATE TABLE `test` (
    `gen_name` text GENERATED ALWAYS AS ('hello "world"!') VIRTUAL
);

callback - 如果您需要引用表中的列

export const test = mysqlTable("test", {
    name: text("first_name"),
    generatedName: text("gen_name").generatedAlwaysAs(
      (): SQL => sql`'hi, ' || ${test.name} || '!'`
    ),
});

CREATE TABLE `test` (
  `first_name` text,
  `gen_name` text GENERATED ALWAYS AS ('hi, ' || `test`.`first_name` || '!') VIRTUAL
);

限制

Drizzle Kit 也将在 push 命令中有限制：

您不能使用 push 更改生成约束表达式和类型。Drizzle-kit 会忽略此更改。要使其工作，您需要先 drop 列，push，然后 add 带有新表达式的列。这是由于数据库端复杂的映射，其中架构表达式将在数据库端被修改，而在反向工程时，我们将得到不同的字符串。我们无法确定您是更改了此表达式，还是它被数据库更改和格式化。由于这些是生成列且 push 通常用于本地数据库的原型，drop 和 create 生成列应该是快速的。由于这些列是 generated，所有数据将会被恢复。
generate 应该没有限制。

schema.ts

export const users = mysqlTable("users", {
    id: int("id"),
    id2: int("id2"),
    name: text("name"),
    storedGenerated: text("stored_gen").generatedAlwaysAs(
      (): SQL => sql`concat(${users.name}, ' ', 'hello')`,
      { mode: "stored" }
    ),
    virtualGenerated: text("virtual_gen").generatedAlwaysAs(
      (): SQL => sql`concat(${users.name}, ' ', 'hello')`,
      { mode: "virtual" }
    ),
});

CREATE TABLE `users` (
  `id` int,
  `id2` int,
  `name` text,
  `stored_gen` text GENERATED ALWAYS AS (concat(`users`.`name`, ' ', 'hello')) STORED,
  `virtual_gen` text GENERATED ALWAYS AS (concat(`users`.`name`, ' ', 'hello')) VIRTUAL
);

数据库端

类型: STORED, VIRTUAL

工作原理

通过表架构中的表达式定义。
虚拟列在读取操作时计算。
存储列在写入操作时计算并存储。

能力

可用于 SELECT、INSERT、UPDATE 和 DELETE 语句。
可以被索引，包括虚拟和存储列。
可以指定 NOT NULL 和其他约束。

限制

无法直接插入或更新生成列中的值。

欲了解更多信息，请查看 SQLite 文档。

Drizzle 端

特性

此函数可以以两种方式接受生成表达式：

IMPORTANT

从 1.0.0-beta.12 版本开始的更改

在 1.0.0-beta.12 之前的版本，.generatedAlwaysAs() 也接受字面量作为表达式。

string

export const test = sqliteTable("test", {
    id: int("id").primaryKey(),
    generatedName: text("gen_name").generatedAlwaysAs(`'hello world!'`),
});

CREATE TABLE `test` (
    `id` integer PRIMARY KEY,
    `gen_name` text GENERATED ALWAYS AS ('hello world!') VIRTUAL
);

sql 标签 - 如果您希望 Drizzle 为您转义一些值

export const test = sqliteTable("test", {
    id: int("id").primaryKey(),
    generatedName: text("gen_name").generatedAlwaysAs(sql`'hello "world"!'`),
});

CREATE TABLE `test` (
  `id` integer PRIMARY KEY,
  `gen_name` text GENERATED ALWAYS AS ('hello "world"!') VIRTUAL
);

callback - 如果您需要引用表中的列

export const test = sqliteTable("test", {
    name: text("first_name"),
    generatedName: text("gen_name").generatedAlwaysAs(
      (): SQL => sql`'hi,' || ${test.name} || '!'`
    ),
});

CREATE TABLE `test` (
  `first_name` text,
  `gen_name` text GENERATED ALWAYS AS ('hi,' || "first_name" || '!') VIRTUAL
);

限制

Drizzle Kit 也将在 push 和 generate 命令中有限制：

您不能使用存储类型更改现有表中的生成约束表达式。您需要删除此表并重新创建。这是由于 SQLite 对此类操作的限制。我们将在未来的版本中处理此情况（这将涉及创建一个新表并进行数据迁移）。
您不能将 stored 生成表达式添加到现有列中，由于以上原因。但是，您可以向现有列添加 virtual 表达式。
您不能更改现有列中的 stored 生成表达式，由于以上原因。但是，您可以更改 virtual 表达式。
您不能将生成约束类型从 virtual 更改为 stored，由于以上原因。但是，您可以从 stored 更改为 virtual。

schema.ts

export const users = sqliteTable("users", {
  id: int("id"),
  name: text("name"),
  storedGenerated: text("stored_gen").generatedAlwaysAs(
    (): SQL => sql`${users.name} || 'hello'`,
    { mode: "stored" }
  ),
  virtualGenerated: text("virtual_gen").generatedAlwaysAs(
    (): SQL => sql`${users.name} || 'hello'`,
    { mode: "virtual" }
  ),
});

CREATE TABLE `users` (
    `id` integer,
    `name` text,
    `stored_gen` text GENERATED ALWAYS AS ("name" || 'hello') STORED,
    `virtual_gen` text GENERATED ALWAYS AS ("name" || 'hello') VIRTUAL
);

数据库端

类型: PERSISTED, VIRTUAL

工作原理

通过表架构中的表达式定义。
虚拟列在读取操作时计算。
持久列在写入操作时计算并存储。

欲了解更多信息，请参阅 MSSQL 文档

Drizzle 端

特性

此函数可以以两种方式接受生成表达式：

IMPORTANT

从 1.0.0-beta.12 版本开始的更改

在之前的版本中，.generatedAlwaysAs() 也接受字面量作为表达式。

string

export const test = mssqlTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(`'hello world!'`),
});

CREATE TABLE [test] (
    [gen_name] AS ('hello world!')
);

sql 标签 - 如果您希望 Drizzle 为您转义一些值

export const test = mssqlTable("test", {
    id: int("id"),
    generatedName: text("gen_name").generatedAlwaysAs(sql`hello "world"!`),
});

CREATE TABLE [test] (
    [id] int,
    [gen_name] AS ('hello "world"!') 
);

callback - 如果您需要引用表中的列

export const test = mssqlTable("test", {
    name: text("first_name"),
    generatedName: text("gen_name").generatedAlwaysAs(
      (): SQL => sql`concat('hi,', ' ', ${test.name}, '!')`
    ),
});

CREATE TABLE [test] (
	[first_name] text,
	[gen_name] AS (concat('hi,', ' ', [test].[first_name], '!')) 
);

在 Drizzle 中，你可以在任何列类型上指定 .generatedAlwaysAs() 函数，并添加一个支持的 SQL 查询，这将为你生成该列的数据。

特性

此函数可以以两种方式接受生成表达式：

IMPORTANT

从 1.0.0-beta.12 版本开始的更改

在之前的版本中，.generatedAlwaysAs() 也接受字面量作为表达式。

string

export const test = cockroachTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(`'hello world!'`),
});

CREATE TABLE "test" (
	"gen_name" string GENERATED ALWAYS AS ('hello world!') STORED
);

sql 标签 - 如果您希望 Drizzle 为您转义一些值

export const test = cockroachTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(sql`'hello "world"!'`),
});

CREATE TABLE "test" (
    "gen_name" string GENERATED ALWAYS AS ('hello "world"!') STORED
);

callback - 如果您需要引用表中的列

export const test = cockroachTable("test", {
    name: text("first_name"),
    generatedName: text("gen_name").generatedAlwaysAs(
      (): SQL => sql`'hi, ' || ${test.name} || '!'`
    ),
});

CREATE TABLE "test" (
	"first_name" string,
	"gen_name" string GENERATED ALWAYS AS ('hi, ' || "test"."first_name" || '!') STORED
);

示例生成列与全文搜索

schema.ts

import { SQL, sql } from "drizzle-orm";
import { customType, index, int4, cockroachTable, text } from "drizzle-orm/cockroach-core";

const tsVector = customType<{ data: string }>({
  dataType() {
    return "tsvector";
  },
});

export const test = cockroachTable(
  "test",
  {
    id: int4().primaryKey().generatedAlwaysAsIdentity(),
    content: text("content"),
    contentSearch: tsVector("content_search", {
      dimensions: 3,
    }).generatedAlwaysAs(
      (): SQL => sql`to_tsvector('english', ${test.content})`
    ),
  },
  (t) => [
    index("idx_content_search").using("gin", t.contentSearch)
  ]
);

CREATE TABLE "test" (
	"id" int4 PRIMARY KEY GENERATED ALWAYS AS IDENTITY (INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
	"content" string,
	"content_search" tsvector GENERATED ALWAYS AS (to_tsvector('english', "test"."content")) STORED
);
CREATE INDEX "idx_content_search" ON "test" USING gin ("content_search");