sql-create-table

SQL 最佳实践：`CREATE TABLE` 语句

本教程将指导你使用现代 SQL 创建数据库表。CREATE TABLE 命令是数据库模式设计的基石，它定义了存储应用程序数据所用的结构。

在关系型数据库管理系统（RDBMS）中，表将数据组织成行和列。列（或字段）定义了数据的属性和类型，而行（或记录）表示单个条目。可以把它看作是一个结构化电子表格的蓝图。

掌握 CREATE TABLE 是构建健壮和可扩展应用程序的第一步。我们将不仅探讨其基本语法，还会深入了解现代数据类型、约束以及专业开发人员所使用的最佳实践。

SQL `CREATE TABLE` 语句

CREATE TABLE 语句用于在数据库中创建一个新表。一个定义良好的表需要一个在数据库模式中唯一的名称、一个列的列表，以及为每个列指定的数据类型和可选约束。

现代语法

这是包含现代约束的通用语法：

CREATE TABLE table_name (
    column1_name data_type PRIMARY KEY,
    column2_name data_type NOT NULL UNIQUE,
    column3_name data_type DEFAULT default_value,
    column4_name data_type CHECK (condition),
    column5_name data_type REFERENCES other_table(other_column),
    ...
);

主要组成部分：

CREATE TABLE table_name：用于创建一个具有指定唯一名称的新表的命令。
column_name data_type：每个列必须有一个名称和一个数据类型，用于定义它可以存储的数据种类。
Constraints（约束）：应用于列以确保数据完整性的规则。常见的约束包括 PRIMARY KEY（主键）、NOT NULL（非空）、UNIQUE（唯一）、DEFAULT（默认值）、CHECK（检查）和 FOREIGN KEY（外键）。

选择正确的数据类型

选择正确的数据类型对于性能和数据完整性至关重要。以下是一些现代常用的数据类型：

标识符： BIGINT 或 UUID。BIGINT 用于自动递增的数字。UUID 用于分布式系统以避免冲突。
文本： VARCHAR(n) 用于具有已知最大长度的字符串（例如，用户名），TEXT 用于可变长度字符串（例如，博客文章）。
数字： INTEGER 用于整数，DECIMAL(p, s) 用于精确的财务计算，FLOAT 或 DOUBLE PRECISION 用于科学计算。
时间/日期： TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) 是存储时间点的最佳实践，因为它在不同时区之间没有歧义。DATE 用于不带时间组件的日期。
布尔值： BOOLEAN（或某些系统中的 BIT）用于真/假值。
JSON/JSONB： 用于将设置或日志等半结构化数据直接存储在数据库中。JSONB（在 PostgreSQL 中）通常更受青睐，因为它支持索引且更高效。

示例：一个现代的 `users` 表

让我们为一个 Web 应用程序创建一个 users 表，并应用现代最佳实践。此示例使用了标准 SQL 特性。

CREATE TABLE users (
    -- 对 ID 使用 BIGINT，并使用 identity 列进行自动递增。
    -- 标准 SQL：GENERATED ALWAYS AS IDENTITY。MySQL：AUTO_INCREMENT。Postgres：SERIAL 或 GENERATED...
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,

    -- 对面向公众的非顺序 ID 使用 UUID。
    public_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(), -- gen_random_uuid() 是一个 PostgreSQL 函数

    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,

    -- 使用 TIMESTAMPTZ 来表示明确的时间戳。
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    last_login_at TIMESTAMP WITH TIME ZONE,

    -- 使用 CHECK 约束进行数据验证。
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT username_min_length CHECK (char_length(username) >= 3)
);

**专家提示：** 命名规范很重要。对表名和列名使用 `snake_case` 是一种常见且易读的标准。避免使用 SQL 保留关键字作为名称。

验证

创建表后，你应该验证其结构。尽管有些数据库（如 MySQL）有简单的 DESC users; 命令，但标准方法是查询 INFORMATION_SCHEMA。

-- 验证表列和类型的标准 SQL 方式
SELECT
    column_name,
    data_type,
    is_nullable
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = 'users';

此查询提供了表的列的详细、与供应商无关的视图。

SQL `CREATE TABLE IF NOT EXISTS`

尝试创建一个已经存在的表将导致错误。为了防止这种情况，尤其是在自动化脚本中，你可以使用 IF NOT EXISTS 子句。

CREATE TABLE IF NOT EXISTS users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
    -- ... other columns
);

如果 users 表已经存在，此命令不执行任何操作并无错误地继续。如果它不存在，则创建该表。请注意，这是一个常见但并非普遍标准的扩展。

**开发工作流注意事项：** 在现代应用程序开发中，模式变更通常由 Flyway 或 Liquibase 等迁移工具管理。这些工具对数据库模式进行版本控制并自动处理此类检查。

从现有表创建新表

你可以根据 SELECT 查询的结果创建一个新表。这对于创建数据备份、快照或物化视图非常有用。

语法

CREATE TABLE new_table_name AS
SELECT column1, column2
FROM existing_table_name
WHERE condition;

示例：创建 `active_users_report`

让我们创建一个新表，其中只包含 users 表中的活跃用户。

CREATE TABLE active_users_report AS
SELECT id, username, email, last_login_at
FROM users
WHERE is_active = TRUE;

**警告：** 此方法复制数据和基本列类型，但它**不复制** `PRIMARY KEY`、`FOREIGN KEY`、`UNIQUE` 或 `DEFAULT` 值等约束。新表是该时刻数据的简单、无约束的副本。

sql-create-table

SQL 最佳实践：CREATE TABLE 语句

SQL CREATE TABLE 语句