生成列

要使用此功能，您需要 [email protected] 或更高版本以及 [email protected] 或更高版本

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

生成列有两种类型

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

生成列特别适用于

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

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

PostgreSQL

MySQL

SQLite

SingleStore (WIP)

数据库端

类型：仅 STORED

工作原理

在插入或更新时根据其他列自动计算值。

功能

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

限制

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

更多信息，请查阅 PostgreSQL 文档

Drizzle 端

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

特性

此函数可以通过 3 种方式接受生成表达式

字符串

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

CREATE TABLE IF NOT EXISTS "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 IF NOT EXISTS "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 IF NOT EXISTS "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 IF NOT EXISTS "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 IF NOT EXISTS "idx_content_search" ON "test" USING gin ("content_search");

数据库端

类型：STORED，VIRTUAL

工作原理

在表模式中通过表达式定义。
虚拟列在读取操作期间计算。
存储列在写入操作期间计算并存储。

功能

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

限制

不能直接在生成列中插入或更新值

更多信息，请查阅 MySQL Alter Generated 文档和 MySQL create generated 文档

Drizzle 端

特性

字符串

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 the column（删除列），push（推送），然后 add a column with a new expression（添加带有新表达式的列）。这是由于数据库端的复杂映射造成的，数据库端的模式表达式将被修改，而在自省时，我们将得到一个不同的字符串。我们无法确定您是否更改了此表达式，或者它是否被数据库更改和格式化。只要这些是生成列，并且 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`${users.name} || 'hello'`,
      { mode: "stored" }
    ),
    virtualGenerated: text("virtual_gen").generatedAlwaysAs(
      (): SQL => sql`${users.name} || 'hello'`,
      { mode: "virtual" }
    ),
})

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

数据库端

类型：STORED，VIRTUAL

工作原理

在表模式中通过表达式定义。
虚拟列在读取操作期间计算。
存储列在写入操作期间计算并存储。

功能

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

限制

不能直接在生成列中插入或更新值

更多信息，请查阅 SQLite 文档

Drizzle 端

特性

字符串

export const test = sqliteTable("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 = sqliteTable("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 = 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。

index.ts

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
);