理解SQL数据类型：一份全面指南

引言

在 SQL (Structured Query Language) 中，数据库表的每个列（Column）都旨在存储特定类型的数据。SQL 数据类型（Data Type）定义了列可以存储的值的类型。例如，如果有一个存储用户年龄的列，你会将其数据类型设置为 INT，以确保只存储整数。正确使用数据类型对于维护数据完整性（Data Integrity）、优化存储（Storage Optimization）和确保数据库高效运行至关重要。

SQL 数据类型大致可分为以下几类：

数值数据类型 (Numeric Data Types)：如 INT (整数), TINYINT (微小整数), BIGINT (大整数), FLOAT (浮点数), REAL (实数) 等。
日期和时间数据类型 (Date and Time Data Types)：如 DATE (日期), TIME (时间), DATETIME (日期时间) 等。
字符和字符串数据类型 (Character and String Data Types)：如 CHAR (定长字符串), VARCHAR (变长字符串), TEXT (长文本) 等。
Unicode 字符字符串数据类型 (Unicode Character String Data Types)：如 NCHAR (定长 Unicode 字符串), NVARCHAR (变长 Unicode 字符串), NTEXT (长 Unicode 文本) 等。
二进制数据类型 (Binary Data Types)：如 BINARY (定长二进制), VARBINARY (变长二进制) 等。
杂项数据类型 (Miscellaneous Data Types)：如 CLOB (字符大型对象), BLOB (二进制大型对象), XML, BOOLEAN (布尔值), UUID (通用唯一标识符) 等。

尽管许多数据类型是标准化的，但它们的名称和行为在不同的 SQL 数据库系统（如 MySQL、PostgreSQL 和 SQL Server）中可能略有不同。

选择正确的 SQL 数据类型

选择合适的数据类型不仅仅是技术形式，它对应用程序有着实际的影响：

存储效率 (Storage Efficiency)：选择能够安全容纳数据范围的最小数据类型可以节省大量磁盘空间。
性能 (Performance)：更小的数据类型意味着数据库可以在单次操作中从内存或磁盘读取更多数据，从而加快查询速度。
数据完整性 (Data Integrity)：数据类型对数据施加规则，防止不符合类型的值被存储，从而维护数据的一致性和准确性。

关系型数据库厂商差异

尽管核心 SQL 数据类型被广泛使用，但其实现因数据库系统而异：

对标准类型的不同支持 (Varying Support for Standard Types)：并非所有数据库厂商都支持相同的数据类型集。
专有数据类型 (Proprietary Data Types)：许多厂商引入了自己的专用数据类型，这些数据类型不属于 ANSI SQL 标准。
不同的最大尺寸限制 (Different Maximum Size Limits)：例如，VARCHAR 的最大尺寸在不同数据库系统之间可能有所不同。

因此，最佳实践是始终查阅特定数据库厂商的官方文档。

常见的 SQL 数据类型

数值数据类型 (Numeric Data Types)

用于存储数字数据，包括精确的整数和小数类型，以及近似的浮点数类型。

Data Type	存储大小 (Storage Size)	值范围 (Range of Values)	常见用例 (Common Use Case)
`TINYINT`	1 byte	`0` to `255` (无符号) 或 `-128` to `127` (有符号)	存储小于 256 的小整数，如年龄。
`SMALLINT`	2 bytes	`-32,768` to `32,767`	存储小整数，如书本页数。
`INT`	4 bytes	`-2,147,483,648` to `2,147,483,647`	标准整数，常用于用户 ID 或产品计数。
`BIGINT`	8 bytes	`-9.22E+18` to `9.22E+18`	存储非常大的整数，如全球系统中的事务 ID。
`DECIMAL`(p,s)	Variable	`-10^38 + 1` to `10^38 - 1`	存储精确小数，用于金融和货币数据。`p` 是总位数，`s` 是小数位数。
`NUMERIC`(p,s)	Variable	`-10^38 + 1` to `10^38 - 1`	功能上与 `DECIMAL` 相同。
`FLOAT`(n)	4 or 8 bytes	`-1.79E+308` to `1.79E+308`	存储近似浮点数，用于科学计算（允许轻微的精度损失）。
`REAL`	4 bytes	`-3.40E+38` to `3.40E+38`	单精度，近似浮点数。比 `FLOAT` 存储范围小。

日期和时间数据类型 (Date and Time Data Types)

用于存储日期和时间信息。

Data Type	描述 (Description)
`DATE`	仅存储日期，格式为 `YYYY-MM-DD`。
`TIME`	仅存储时间，格式为 `HH:MI:SS`。
`DATETIME`	存储日期和时间，格式为 `YYYY-MM-DD HH:MI:SS`。
`TIMESTAMP`	存储自 Unix 纪元（`'1970-01-01 00:00:00' UTC`）以来的秒数，通常用于记录行创建或修改时间。
`YEAR`	存储年份，格式为 2 位或 4 位（例如，`1901` 到 `2155`）。

字符和字符串数据类型 (Character and String Data Types)

用于存储文本数据。

Data Type	描述与用途 (Description & Purpose)	Unicode 支持 (Unicode Support)	最大尺寸 (Max Size, SQL Server)	常见用例 (Common Use Cases)
`CHAR(n)`	固定长度字符串，不足时用空格填充。	No	8,000 characters	两位国家代码、产品 ID 等固定长度数据。
`VARCHAR(n)`	可变长度字符串，最大 `n` 个字符。	No	8,000 characters (MySQL: 65,535 bytes)	用户名、电子邮件、文章标题。最适合非 Unicode 文本。
`TEXT`	可变长度，用于存储非常大的文本块。	No	2 GB	长的产品描述、文章内容、用户评论。
`NCHAR(n)`	固定长度的 Unicode 字符串。	Yes	4,000 characters	固定长度的多语言标识符。
`NVARCHAR(n)`	可变长度的 Unicode 字符串。	Yes	4,000 characters (`NVARCHAR(MAX)` up to 2GB)	姓名、评论、来自世界各地的通用文本。
`NTEXT`	遗留类型，用于存储大型 Unicode 文本块。	Yes	2 GB	(已弃用，在现代 SQL Server 中推荐使用 `NVARCHAR(MAX)`)。

注意： MySQL 处理 Unicode 的方式不同。它不直接使用 NCHAR、NVARCHAR、NTEXT。相反，您使用标准的 VARCHAR 或 TEXT 类型，并将列的字符集设置为 Unicode 兼容的，例如 utf8mb4。

二进制数据类型 (Binary Data Types)

用于存储原始二进制数据，如文件、图片等。

Data Type	描述 (Description)	常见用例 (Common Use Cases)	最大尺寸 (Maximum Size)
`BINARY(n)`	固定长度二进制类型，不足 `n` 字节时用零填充。	存储始终相同大小的数据，如加密哈希值 (MD5 hash)。	8,000 bytes (SQL Server); 255 bytes (MySQL)
`VARBINARY(n)`	可变长度二进制类型，最大为 `n` 字节，不填充。	存储小型的可变大小二进制数据，如缩略图、QR 码。	8,000 bytes (SQL Server); 65,535 bytes (MySQL)
`BLOB`	Binary Large Object (二进制大型对象)。用于存储非常大的二进制数据。	直接在数据库中存储文件，如图像、音频文件、PDF 文档。	4 GB (`LONGBLOB` in MySQL)
`IMAGE` (Legacy)	SQL Server 的旧版数据类型，功能类似于 `BLOB`。	(已弃用，现代 SQL Server 推荐使用 `VARBINARY(MAX)`)。	2 GB

注意： 尽管在 BLOB 中存储小型图像或文件很方便，但对于大型、高流量的应用程序，通常最好将文件存储在专用文件系统或对象存储服务（如 DigitalOcean Spaces 或 AWS S3）中，然后在数据库中只存储 URL 或文件路径。

杂项数据类型 (Miscellaneous Data Types)

处理现代数据结构，如 JSON、地理数据或简单的布尔值。

Data Type	描述 (Description)	常见用例 (Common Use Cases)	最大尺寸 / 存储 (Maximum Size / Storage)
`JSON`	存储 JavaScript Object Notation (JSON) 格式的文本。	存储应用程序设置、日志或第三方 Web API 数据。适用于半结构化数据。	1-4 GB (取决于底层文本/二进制存储)
`XML`	存储 eXtensible Markup Language (XML) 格式的数据。	存储配置文件、遗留企业系统数据。	2 GB (SQL Server)
`CLOB`	Character Large Object (字符大型对象)。用于存储特大文本。	存储整个文本文件或书籍长度的文档。(Oracle 常用，MySQL 用 `TEXT`，SQL Server 用 `VARCHAR(MAX)`)。	通常 2 GB 或更多
`BOOLEAN`	存储逻辑真值：`TRUE` 或 `FALSE`。	存储标志位，如 `is_active`。 (MySQL 使用 `TINYINT(1)` 作为等效类型)。	1 byte
`UUID`	Universally Unique Identifier (通用唯一标识符)。128 位数字，用于跨系统唯一标识信息。	生成唯一主键，尤其是在分布式数据库中。	16 bytes (128 bits)

不同数据库中的 SQL 数据类型

每个主要的数据库系统都有其独特的“风格”，提供专有数据类型或标准类型的不同行为。

MySQL 数据类型 (MySQL Data Types)

ENUM：列的值必须从预定义列表中选择。高效存储为小整数，但后期添加项可能性能慢。
SET：与 ENUM 类似，但单个列可以包含多个预定义列表中的值。
UNSIGNED 属性：一个重要的数值类型属性，防止存储负值，有效将正数范围加倍。
YEAR：1 字节类型，用于存储年份，可为 YEAR(4) 或旧版 YEAR(2)（不推荐）。

PostgreSQL 数据类型 (PostgreSQL Data Types)

ARRAY：允许单个列存储数组值。
JSONB vs. JSON：JSON 存储精确的文本副本，JSONB 存储分解的二进制格式，查询更快且支持索引。
UUID：用于存储 128 位全球唯一标识符的原生类型，常用于分布式系统中的主键。
网络地址类型：如 INET 和 CIDR，用于存储和验证 IPv4 和 IPv6 地址。

SQL Server 数据类型 (SQL Server Data Types)

MONEY / SMALLMONEY：优化用于货币，固定四位小数精度。
DATETIME2：现代推荐的 DATETIME 替代品，提供更大的日期范围和用户定义的分秒精度。
UNIQUEIDENTIFIER：SQL Server 的原生 UUID 数据类型。
VARCHAR(MAX) / NVARCHAR(MAX)：取代旧版 TEXT 和 NTEXT，提供高达 2GB 的存储容量，并保留 VARCHAR 的功能优势。

Oracle SQL 数据类型 (Oracle SQL Data Types)

VARCHAR2(n)：Oracle 的主要可变长度字符串类型，VARCHAR 不推荐使用。
NUMBER：高度通用的数值类型，可作为定点小数、整数或浮点数。
DATE：不同于标准 SQL 的 DATE，Oracle 的 DATE 始终包含日期和时间组件。
CLOB/BLOB/NCLOB：Oracle 使用“大型对象”（LOB）类型来存储大量数据：BLOB 用于二进制，CLOB 用于字符，NCLOB 用于 Unicode 字符。

SQL 中的类型转换 (Type Conversion in SQL)

类型转换是将一个值从一种数据类型转换为另一种数据类型。

隐式转换 (Implicit Conversion)：数据库自动进行的转换。虽然方便，但不推荐依赖，可能导致意外结果或性能问题。
显式转换 (Explicit Conversion)：手动使用函数进行转换，推荐的做法，使代码意图明确，转换可预测。
- CAST()：ANSI SQL 标准函数，在几乎所有数据库中可用。语法：CAST(expression AS target_type)
- CONVERT()：主要用于 SQL Server，提供更多灵活性，尤其在格式化日期和时间时。语法：CONVERT(target_type, expression, [style_code])

数据类型选择的最佳实践 (Best Practices for Data Type Selection)

在数据库设计中，“越小通常越好”。数据类型的大小对查询速度、内存使用和整体应用程序性能有深远影响。

使用能安全容纳数据的最小类型（“金发姑娘原则”，The Goldilocks Principle）：不要默认使用 INT，如果 TINYINT 就足够了。例如，用户年龄使用 TINYINT UNSIGNED（0-255）比 INT 更节省空间。用户 ID 使用 INT（21 亿）通常已足够，避免过度使用 BIGINT。
理解你的数据 (Understand Your Data)：在选择类型之前，了解数据的领域。例如，美国邮政编码应该用 CHAR(5) 而不是 INT，因为邮政编码可能有前导零。
规划未来，但要实际 (Plan for the Future, But Be Practical)：为增长做合理规划，但不要过度设计。例如，选择 INT 作为主键通常是明智的，但如果应用程序仅限英语内容，自动使用 NVARCHAR 替代 VARCHAR 则是一种浪费。
理解 CHAR 与 VARCHAR 的权衡 (Understand the CHAR vs. VARCHAR Trade-offs)：
- CHAR(n)：仅当数据长度固定时使用（如两位国家代码）。
- VARCHAR(n)：适用于几乎所有其他文本，因为它只存储实际字符加上少量开销。

常见问题解答 (FAQs)

1. SQL 中数据类型的主要类别有哪些？

主要类别包括：数值类型（整数、小数）、字符/字符串类型（固定/可变长度文本）、日期和时间类型、二进制类型（原始文件数据）、以及杂项/专用类型（布尔、JSON、XML 等）。

2. SQL 中 `VARCHAR` 和 `TEXT` 有什么区别？

主要区别在于指定长度、存储方式和预期用途：

VARCHAR(n)：必须指定最大长度 n，数据通常“行内”存储，访问速度快。适用于已知合理最大长度的文本，如姓名、电子邮件地址。
TEXT：不指定最大长度，设计用于存储非常长的文本（可能达兆字节或千兆字节），数据通常单独存储在行外，检索可能稍有性能开销。适用于博客文章内容、长描述等。

3. MySQL 和 PostgreSQL 的数据类型有何不同？

两者都支持标准 SQL 类型，但各有特点：

PostgreSQL：以全面和严格遵守 SQL 标准著称。提供 ARRAY（列可存储列表）、UUID（原生 128 位唯一标识符）、JSONB（二进制、可索引 JSON）和真正的 BOOLEAN 类型。
MySQL：提供方便的用户友好数据类型。如 ENUM（预定义字符串列表，高效内部存储）、SET（可存储多个预定义值）、UNSIGNED（允许数值类型范围翻倍用于正数）。

4. SQL 中 `INT` 的默认大小是多少？

在几乎所有现代 SQL 数据库（包括 MySQL、PostgreSQL 和 SQL Server）中，INT 或 INTEGER 数据类型是**4 字节（32 位）**的整数。

它可以表示 2^32（约 42.9 亿）个不同的值。
对于标准的有符号 INT，范围从 -2,147,483,648 到 2,147,483,647。
在支持的系统中（如 MySQL），无符号 UNSIGNED INT 范围从 0 到 4,294,967,295。

结论

本文全面概述了 SQL 数据类型，从它们对性能、完整性和存储的基本影响开始。详细介绍了每个主要类别，包括数值和字符串类型，以及 JSON 和 UUID 等专用类型。还探讨了 MySQL、PostgreSQL、SQL Server 和 Oracle 等流行数据库系统之间的关键差异和独特功能。最后，深入研究了类型转换（使用 CAST() 和 CONVERT()）和存储优化最佳实践，为您提供了全面而实用的基础。

关于

关注我获取更多资讯

📢 公众号

💬 个人号