SQL 基础数据类型

Begindev 编辑部 March 31, 2026

数据类型是任何数据库系统的基础，它们定义了可以存储在列中的值的种类。选择正确的数据类型对于数据完整性、存储效率和查询性能至关重要。PostgreSQL 提供了丰富的数据类型，本章将重点介绍其中最常见且必不可少的四种：整数 (Integer)、文本 (Text)、日期 (Date) 和布尔值 (Boolean)。

1. 整数类型 (Integer Data Type)

整数数据类型用于存储正负整数。PostgreSQL 提供了几种整数类型，每种类型具有不同的存储大小和取值范围。选择合适的整数类型取决于你需要存储的预期值范围。

1.1 PostgreSQL 中的整数类型

PostgreSQL 提供以下整数类型：

SMALLINT: 这是一个 2 字节的整数，可以存储从 -32,768 到 32,767 的值。
INTEGER 或 INT: 这是最常用的整数选择。它是一个 4 字节的整数，可以存储从 -2,147,483,648 到 2,147,483,647 的值。
BIGINT: 这是一个 8 字节的整数，可以存储从 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 的值。

1.2 整数使用示例

假设我们要设计一个管理图书馆的数据库。

SMALLINT: 你可能会使用 SMALLINT 来存储当前借出的一本书的副本数量，前提是图书馆每本书的副本数量不会过多。
INTEGER: 你可以使用 INTEGER 来存储一本书的总页数或图书馆中的藏书总数。
BIGINT: 如果你需要追踪多年来多地图书馆中某本书被借阅的总次数，你可能需要 BIGINT 来容纳非常大的计数。

以下是如何使用这些整数类型定义表的示例：

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY, -- 自动生成唯一的整数 ID
    title VARCHAR(255),         -- 书名
    pages INTEGER,              -- 页数
    copies_checked_out SMALLINT, -- 已借出的副本数
    total_borrows BIGINT        -- 总借阅次数
);

代码解释：

book_id 使用了 SERIAL。这是 PostgreSQL 中的一种特殊类型，它会自动创建一个整数序列（通常是 INTEGER），并将其设置为新行的默认值。此列将作为 books 表的主键，确保每本书都有一个唯一的 ID。我们将在“定义约束”章节中详细讨论主键。
pages 使用 INTEGER，因为一本书的页数通常在标准整数的范围内。
copies_checked_out 使用 SMALLINT，这很合适，因为每本书借出的副本数量不太可能超过 SMALLINT 的上限。
total_borrows 使用 BIGINT，以适应随着时间推移可能产生的极其巨大的借阅量。

1.3 实际应用注意事项

存储空间： 选择能够容纳你预期值范围的最小整数类型。使用不必要的大类型会浪费存储空间。
性能： 虽然不同整数类型之间的性能差异通常可以忽略不计，但使用合适的大小有助于优化内存使用，尤其是在大型数据集中。
溢出 (Overflow)： 注意整数溢出的可能性。如果一个值超过了其类型的最大值，将会导致错误（或在某些情况下出现意外行为）。
假设场景： 假设你正在设计一个系统来追踪网站流量。你需要存储每天页面被访问的次数。如果你预计每天最多 10,000 次点击，SMALLINT 可能就足够了。但是，如果你预计每天有数百万次点击，那么 INTEGER 甚至 BIGINT 会更合适。

2. 文本类型 (Text Data Type)

文本数据类型用于存储字符串。PostgreSQL 提供了几种文本类型，在如何存储和管理文本数据方面提供了极大的灵活性。

2.1 PostgreSQL 中的文本类型

PostgreSQL 提供以下主要的文本类型：

VARCHAR(n): 存储指定最大长度 n 的可变长度字符串。PostgreSQL 仅为实际存储的字符分配空间（最高不超过限制 n）。如果你尝试存储长于 n 个字符的字符串，PostgreSQL 将报错，除非多出的字符全是空格，在这种情况下，字符串将被截断为长度 n。
TEXT: 存储没有指定最大长度的可变长度字符串。PostgreSQL 会根据需要动态分配空间。
CHAR(n): 存储固定长度的字符串。如果存储的字符串短于 n 个字符，它将被填充空格以达到指定的长度。CHAR(1) 通常用于存储单字符代码。

2.2 文本使用示例

继续使用图书馆数据库的例子：

VARCHAR(n): 你可能会使用 VARCHAR(255) 来存储书名，将书名长度限制为 255 个字符。
TEXT: 你可以使用 TEXT 来存储一本书的摘要或描述，因为它们的长度是不固定的。
CHAR(n): 你可能会使用 CHAR(3) 来存储州/省份代码（例如，“CA” 代表加利福尼亚州）。

以下是包含文本类型的 books 表的更新定义：

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255),    -- 限制为 255 个字符的书名
    author TEXT,           -- 长度不限的作者名
    genre CHAR(50),        -- 假设图书馆里的类型名称最大长度为 50（不足会用空格填充）
    pages INTEGER,
    copies_checked_out SMALLINT,
    total_borrows BIGINT
);

代码解释：

title 定义为 VARCHAR(255)，将书名限制为 255 个字符。
author 定义为 TEXT，因为作者名字的长度可能会有很大差异。
genre 定义为 CHAR(50)。如果图书馆中的流派种类有限且字符数相近，这可能是合适的（不过实际开发中往往更倾向于 VARCHAR）。

2.3 实际应用注意事项

VARCHAR(n) vs. TEXT: 除非你有特定原因需要限制字符串的长度，否则通常优先使用 TEXT 而不是 VARCHAR(n)。TEXT 提供了更大的灵活性并避免了潜在的截断问题。在大多数情况下，两者之间没有显著的性能差异。
CHAR(n): CHAR(n) 的使用频率低于 VARCHAR(n) 或 TEXT，因为如果存储的字符串短于 n 个字符，它会浪费存储空间。它通常仅在你需要强制固定长度时使用，例如存储状态代码或其他标准化缩写。
存储大小：虽然 TEXT 没有固定的容量上限，但 PostgreSQL 对任何列值的最大大小施加了实际限制（通常约为 1GB）。
真实场景：考虑一个社交媒体平台。用户名可以存储为 VARCHAR(50) 以强制执行长度限制，而用户简介可以存储为 TEXT 以允许更广泛的描述。
假设场景：你正在为一个电子商务网站设计数据库。产品名称可以存储为 VARCHAR(255)，而产品描述可以存储为 TEXT。

3. 日期类型 (Date Data Type)

日期数据类型用于存储日历日期（年、月和日）。PostgreSQL 提供了几种日期和时间类型，包括 DATE、TIME 和 TIMESTAMP。本章将重点介绍 DATE 类型。

3.1 PostgreSQL 中的日期类型

DATE: 存储日历日期（年、月、日）。

3.2 日期使用示例

回到图书馆数据库：

DATE: 你会使用 DATE 来存储一本书的出版日期或图书馆会员的加入日期。

再次扩展 books 表并新增一个 members 表：

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    author TEXT,
    genre CHAR(50),
    publication_date DATE, -- 出版日期
    pages INTEGER,
    copies_checked_out SMALLINT,
    total_borrows BIGINT
);

CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    join_date DATE         -- 会员加入日期
);

代码解释：

books 表中的 publication_date 存储图书出版的日期。
members 表中的 join_date 存储会员加入图书馆的日期。

3.3 处理日期操作

PostgreSQL 提供了各种用于处理日期的函数，例如：

CURRENT_DATE: 返回当前日期。
AGE(date): 计算给定日期与当前日期之间的年龄（时间间隔）。
EXTRACT(field FROM date): 从日期中提取特定字段（例如：年、月、日）。

你可以使用多种格式将日期插入表中，但通常推荐使用 ISO 8601 格式 (YYYY-MM-DD)：

INSERT INTO books (title, author, genre, publication_date, pages, copies_checked_out, total_borrows)
VALUES ('指环王', 'J.R.R. 托尔金', '奇幻', '1954-07-29', 1178, 2, 1000);

INSERT INTO members (name, join_date)
VALUES ('爱丽丝·史密斯', '2023-01-15');

3.4 实际应用注意事项

时区： DATE 类型不存储时区信息。如果你需要存储时区信息，请使用 TIMESTAMP WITH TIME ZONE 数据类型，这将在后续章节中介绍。
日期格式：保持日期格式的一致性以避免歧义。ISO 8601 格式 (YYYY-MM-DD) 是一个安全的选择。
日期范围： PostgreSQL 中的 DATE 类型可以存储从公元前 4713 年到公元 294276 年的日期。
真实场景：在电子商务平台中，你可以使用 DATE 类型来存储订单日期、发货日期或预计送达日期。
假设场景：你正在构建一个患者管理系统。你可以使用 DATE 类型来存储患者的出生日期或他们最后一次就诊的日期。

4. 布尔类型 (Boolean Data Type)

布尔数据类型存储真/假值。PostgreSQL 将这些值表示为 TRUE、FALSE 和 NULL（代表未知或缺失的值）。

4.1 PostgreSQL 中的布尔类型

BOOLEAN: 存储真/假值。

4.2 布尔使用示例

向 books 表添加一个状态标志：

ALTER TABLE books
ADD COLUMN is_available BOOLEAN; -- 添加“是否可用”列

现在，books 表有一个 is_available 列，指示一本书目前在图书馆中是否可供借阅。

-- 假设 5 是总副本数。如果借出的少于 5 本，则设为 TRUE (可用)
UPDATE books SET is_available = TRUE WHERE copies_checked_out < 5; 

-- 如果借出的达到或超过 5 本，则设为 FALSE (不可用)
UPDATE books SET is_available = FALSE WHERE copies_checked_out >= 5;

4.3 实际应用注意事项

真值表示： PostgreSQL 接受 TRUE 和 FALSE 的各种输入表示形式（例如，'t'、'f'、1、0），但为了代码的清晰度，建议明确使用 TRUE 和 FALSE 关键字。
NULL 值：请记住，NULL 与 FALSE 不同。NULL 代表未知或缺失的值。
索引：布尔列可以被索引，但索引的有效性取决于 TRUE 和 FALSE 值的数据分布比例。
真实场景：在用户管理系统中，你可能会使用布尔列来指示用户的帐户处于激活状态还是非激活状态。
假设场景：你正在构建一个任务管理应用程序。你可以使用布尔列来指示任务是否已完成。