sql-data-types

SQL 数据类型：数据库的基石

什么是 SQL 数据类型？

SQL 数据类型（Data Type）是指定数据库表中列可以存储何种数据的一种规则。你可以将其想象成一个为特定目的设计的容器：一个用于数字，一个用于文本，一个用于日期等等。通过为每个列定义数据类型，你告诉数据库期望什么，从而确保数据完整性并优化存储。

创建表时，每个列都必须分配两个关键属性：

• 列名（例如，user_email）
• 数据类型（例如，VARCHAR(255)）

核心思想：列定义了你数据的 结构 和 规则，而行则包含遵循这些规则的实际数据记录。

为什么选择正确的数据类型至关重要

选择合适的数据类型是一项关键的设计决策，具有重要影响：

• 数据完整性：它防止存储不正确的数据。你不能将像 ‘hello’ 这样的文本放入 INTEGER（整数）列中。
• 性能：当数据库处理大小和类型正确的数据时，查询速度会更快。搜索整数列比搜索文本列快得多。
• 存储效率：分配正确数量的空间可以防止内存和磁盘空间的浪费。将人的年龄（0-150）存储在 TINYINT 中比使用 BIGINT 效率高得多。

核心数据类型类别

虽然不同数据库系统（如 PostgreSQL、MySQL、SQL Server）的具体名称可能略有不同，但大多数都支持相同的基础数据类型类别。

1. 数值类型

用于存储数值数据，从整数到小数。

Common Type	Description	Typical Use Case
INTEGER (or INT)	整数（无小数）。有各种大小，如 TINYINT、SMALLINT、INTEGER、BIGINT，用于存储不同范围的数字。	用户 ID、数量、年龄。
DECIMAL(p, s) (or NUMERIC)	具有精确精度的定点数。p 是总位数，s 是小数点后的位数。	财务数据、货币、需要精度的科学测量。
FLOAT, REAL, DOUBLE PRECISION	用于近似值的浮点数。适用于对绝对精度要求不高的科学计算。	科学计算、大型统计数据集。

2. 字符串（字符）类型

用于存储文本数据。

Common Type	Description	Typical Use Case
CHAR(n)	固定长度字符串。如果存储的字符串短于 n，则用空格填充。仅当所有值长度相同时使用。	两位字母的州代码（例如，‘CA’，‘TX’）、状态标志（‘Y’/‘N’）。
VARCHAR(n)	可变长度字符串，最大长度为 n 个字符。这是最常见的字符串类型。	用户名、电子邮件地址、文章标题、任何长度可变的文本。
TEXT	用于非常长文本的可变长度字符串。在许多数据库中，现代等效类型是 VARCHAR(MAX) (SQL Server) 或直接使用 TEXT (PostgreSQL, MySQL)。	博客文章、产品描述、评论。

小贴士：处理国际文本时，请确保你的数据库字符集设置为 UTF8MB4，以正确存储各种字符和表情符号。

3. 日期和时间类型

用于存储时间信息。

Common Type	Description	Typical Use Case
DATE	仅存储日期（年、月、日）。	出生日期、发布日期。
TIME	仅存储时间（小时、分钟、秒）。	商店营业时间、事件开始时间。
TIMESTAMP (or DATETIME)	存储日期和时间。强烈建议为服务不同地区用户的应用程序使用 TIMESTAMP WITH TIME ZONE，因为它存储时区信息。	帖子创建时间 (created_at)、事件日志、预约时间。

4. 布尔类型

Common Type	Description	Typical Use Case
BOOLEAN (or BIT)	存储 TRUE 或 FALSE 值。某些数据库使用 BIT(1) 或 TINYINT(1) 来模拟，1 表示 true，0 表示 false。	is_active（是否激活）、has_subscribed（是否已订阅）、email_verified（电子邮件是否已验证）等标志。

5. 特殊类型

现代数据库支持用于特定用例的高级数据类型。

Common Type	Description	Typical Use Case
JSON / JSONB	直接在数据库中存储半结构化 JSON 数据。JSONB 是一种二进制格式，通常查询速度更快。	存储用户设置、产品属性、API 响应。
UUID	存储通用唯一标识符（Universally Unique Identifier），一个 128 位数字，用于在计算机系统中唯一标识信息。	分布式系统中的主键，以避免 ID 冲突。
ENUM	从预定义允许值列表中选择的值。例如，ENUM('pending', 'processing', 'shipped')。	订单状态、用户角色、优先级。

在表中定义数据类型

你可以在使用 CREATE TABLE 语句创建表时定义数据类型。现代约定是 SQL 关键字使用小写。

create table users (
    id         UUID primary key default gen_random_uuid(), -- 现代、健壮的主键
    username   VARCHAR(50) not null unique,
    email      VARCHAR(255) not null unique,
    is_active  BOOLEAN not null default true,
    created_at TIMESTAMP with time zone not null default now(),
    settings   JSONB
);

在这个现代示例中：

• id 是 UUID 类型，确保它在系统间是唯一的。
• username 和 email 是 VARCHAR 类型，带有 NOT NULL 和 UNIQUE 约束，以保证数据完整性。
• is_active 是 BOOLEAN 类型，具有合理的默认值。
• created_at 使用带时区的时间戳，这对于全球性应用程序至关重要。
• settings 使用 JSONB 来存储灵活的、无模式的数据。

特定方言的数据类型（简要说明）

虽然核心概念是通用的，但实现细节有所不同。例如：

• SQL Server: 使用 NVARCHAR 处理 Unicode 字符串，DATETIME2 用于更高精度的日期，以及 MONEY 用于货币。
• MySQL: TIMESTAMP 的行为可能因版本而异。ENUM 是一种流行的原生类型。
• Oracle: 使用 VARCHAR2 作为其标准变量字符串，并使用 NUMBER(p,s) 表示所有数值类型。
• MS Access: 一款桌面数据库，具有 Short Text 和 Long Text 等类型。它通常不用于现代 Web 或应用程序开发。

请务必查阅你特定数据库版本的官方文档，以查看所有可用数据类型及其语法。

最佳实践和常见陷阱

• 具体明确： 不要对所有内容都使用 VARCHAR(255)。如果一个值（如国家代码）总是 2 个字符，请使用 CHAR(2) 或 VARCHAR(2)。
• 数字存储为数字： 绝不要将你打算进行计算的数字（如价格或数量）存储为字符串。这会严重影响性能并导致错误。
• 使用时区感知时间戳： 对于任何拥有不同时区用户的应用程序，TIMESTAMP WITH TIME ZONE 是必需的，而不是可选项。
• 避免废弃类型： 在 SQL Server 中，避免使用 TEXT、NTEXT 和 IMAGE。请改用 VARCHAR(MAX)、NVARCHAR(MAX) 和 VARBINARY(MAX)。
• 陷阱：FLOAT 问题： 避免将 FLOAT 或 REAL 用于财务数据。它们的近似性质可能导致舍入误差。请使用 DECIMAL 或 NUMERIC 来表示货币。