SQL数据类型参考与最佳实践

SQL 数据类型是一种基本规范，它定义了列可以保存哪些值以及这些值在数据库表中需要多少存储空间。对于任何构建数据管道、编写查询或设计数据库模式的人来说，理解 SQL 数据类型至关重要，因为这些类型直接控制数据完整性、存储效率和查询性能。当你在数据库表中定义一列时，你不仅是在指定一个名称——你还在建立一份契约，规定该列中将存储何种信息以及数据库应如何处理它。

选择正确数据类型的重要性怎么强调都不为过。SQL 数据类型强制实施关于可以存储哪些值的逻辑规则，从一开始就阻止无效数据的输入。它们还极大地影响查询运行的速度和表占用的磁盘空间。选择不当的数据类型会拖慢查询速度、浪费存储空间，并在数据管道中引发微妙的问题。相反，选择合适的类型可以提高长期的可扩展性，并大幅提升数据库在分析工作负载、实时应用和机器学习特征管道中的性能。

SQL 数据类型大致分为四大类：用于数学计算的数值数据类型、用于文本的字符和字符串数据类型、用于记录事件发生时间的日期和时间数据类型，以及用于二进制数据和其他格式的特殊数据类型。不同的数据库系统——MySQL、PostgreSQL、SQL Server 和 Oracle——在命名、精度和存储要求上略有变化地实现了这些类别。本指南提供了一个实用参考，帮助您理解常见数据库系统中的 SQL 数据类型，以及为您的用例选择合适类型的最佳实践。

了解数据类型在强制什么

数据类型不仅仅是一个标签。当你声明某一列是 INTEGER 或 VARCHAR 类型时，你是在告诉数据库管理系统该列中究竟应包含什么类型的值，以及在查询和存储期间如何处理它们。数据库使用此信息在插入时验证数据，防止违反类型约束的数据项进入。诸如基于 ACID 事务 的现代数据库系统确保即使在并发访问模式下，这种验证也能可靠地发生。

考虑一个简单的例子：如果你将一列定义为 INTEGER，数据库将拒绝任何尝试插入像“hello”这样的文本或像 3.14 这样的非整数值。这种验证会自动发生，通过拒绝存储错误的数据格式来强制数据完整性。如果没有这种强制执行，下游的查询和分析将遇到损坏或不一致的数据，导致错误结果和浪费调试时间。

数据类型还向与你的模式打交道的其他开发人员和数据工程师传达意图。当有人看到一列被定义为 DECIMAL 而不是 FLOAT 时，他们立即明白该列存储的是不能容忍舍入误差的精确货币值。这种隐含的文档减少了误解，并使模式随着时间的推移更容易维护。

数据类型如何影响存储和查询性能

数据类型的选择直接影响到表占用的磁盘空间量和查询能够运行多快。存储效率影响云账单、备份时间以及处理时适合放入内存的行数。查询性能部分取决于数据类型大小——较小的类型可以处理得更快，因为更多的行适合 CPU 缓存，并且在存储和计算之间传输的数据更少。对于每天处理数百万行数据的ETL每天处理数百万行数据的管道中，这些优化会累积成可衡量的成本和延迟改进。

字符串数据类型的存储占用差异很大。CHAR 列始终保留其完整的声明长度，即使存储短值也会填充空格。相比之下，VARCHAR 列只使用实际存储值所需的空间。如果大多数客户名称少于 30 个字符，将它们存储为 VARCHAR(50) 相比 CHAR(50) 可以节省大量空间。这种空间节省在数百万行数据中累积，并可以减少查询延迟，因为更多数据可以放入可用内存中。

数值类型也影响性能。使用 BIGINT 而 INT 足够时浪费存储和计算。相反，对于需要存储超过 32,000 的值的列使用 SMALLINT 会导致溢出错误。了解数据的范围和精度要求，让你能够选择能安全容纳你的值的最小数据类型，使数据库保持快速和精简。

索引能大幅提升查询性能，在合适的数据类型上定义索引时速度更快。TINYINT 列上的索引比 TEXT 列上的索引更高效。通过选择适当大小的数值类型并避免在非常大的文本列上建立索引，你可以将索引的性能优势倍增于整个工作负载。分布式查询引擎如Apache Spark尤其受益于合适大小的数据类型，因为较小的类型减少了洗牌操作期间的网络传输。

选择正确的数据类型

数据类型选择的金科玉律是使用能安全容纳数据的最小类型。如果在模式设计期间始终坚持这一原则，将在存储效率、查询速度和系统可扩展性方面带来回报。在选择类型之前，问问自己：此列可能包含的最大值是多少？我需要多少精度？此值是否可能为 NULL？

对于数值数据，检查实际的数据分布。如果某列包含0到100之间的值，TINYINT最为合适。如果存储的客户ID可能超过20亿，INT即可；只有确实需要存储超过20亿的值时，才使用BIGINT。在模式中的几十列中做出这种区分，可以将总表大小减少20-30%，直接提升查询性能。

处理字符串时，考虑存储与灵活性之间的权衡。CHAR强制选择最大长度并始终占用该空间。VARCHAR允许高效存储可变长度数据，但需要选择不会导致截断的最大值。VARCHAR(50)用于姓名达到了平衡——它大到足以容纳几乎所有姓名，但防止意外存储可能是数据质量问题的超长值。对于文章正文或日志消息等非常大的文本块，使用不需要预先指定长度的TEXT或CLOB类型。

在部署到生产环境之前，用样本数据验证选择。将真实数据插入使用提议模式创建的测试表，并观察实际存储占用。运行预期查询并测量性能。这种实证方法可以确认选择是否支持实际工作负载。数据库平台通常提供工具来分析查询执行计划，并识别因数据类型不佳而导致的慢操作。

数值数据类型

数值数据类型存储数字，主要有两类：整数类型用于整数，小数或浮点类型用于带小数部分的数字。

整数类型表示不带小数的整数。INTEGER数据类型，简称INT，是整数值最常见的类型，占用4字节，可表示大约-20亿到+20亿的值。当需要更小范围——例如存储不超过127的年龄——TINYINT仅占用1字节，非常合适。SMALLINT占用2字节，可处理约32000的值，适用于数量或计数等相对较小的列。BIGINT是8字节整数，可容纳极大数，对于存储来自分布式系统生成的ID或毫秒级时间戳是必需的。

DECIMAL数据类型在SQL标准文档中有时称为NUMERIC，存储固定精度的数值，适合金融计算和其他不允许舍入误差的场合。DECIMAL存储精确值，没有浮点算术固有的近似性。定义DECIMAL(10,2)意味着"想要存储最多10位总数，其中恰好2位在小数点右边"。这种精度使DECIMAL(10,2)能安全存储像99999999.99这样的值，但会拒绝任何超过两位小数的内容。银行和会计系统依赖DECIMAL，因为金融法规要求精确、可审计的计算，不带舍入误差。

NUMERIC是SQL标准中用于固定精度十进制数据的名称，在大多数数据库系统中行为与DECIMAL相同。一些数据库可互换使用NUMERIC和DECIMAL，而其他数据库由于历史原因分开文档化。检查数据库文档以确认确切行为，但实践中可将它们视为功能等效。

创建带数值列的表可以说明这些类型的使用场景。一个典型的销售表可能如下：

这里，employee_id使用INT，因为员工ID通常在百万级别。age使用TINYINT，因为人类年龄不会超过127。salary和bonus_percentage使用DECIMAL，确保在工资处理期间计算精确，即使在组织中微小的舍入误差也会累积。像Delta Lake这样的现代数据平台严格强制这些类型，保证错误类型的数据不能插入生产表。

理解浮点数

浮点类型以指定精度存储近似数值。FLOAT和DOUBLE使用IEEE 754二进制表示，牺牲精确性换取速度和范围。FLOAT通常占用4字节，存储近似值，而DOUBLE占用8字节，提供更高精度。

浮点表示会引入舍入伪影，因为许多十进制值不能精确表示为二进制。例如，0.1不能精确表示为二进制浮点数，因此任何涉及0.1的计算都可能略有偏差。这些微小误差在长串计算中累积，最终产生明显错误的结果。因此，绝不能将FLOAT或DOUBLE用于货币数据或其他需要精确性的值。

DECIMAL和FLOAT之间的选择取决于用例。对所有财务数据、需要可审计精确性的精确科学测量或计算使用DECIMAL。对允许小误差的近似值、科学计算或对微小不精确不影响模型质量的机器学习特征使用FLOAT。使用适当大小的数据类型可提高查询性能，且FLOAT操作比DECIMAL快，因为浮点算术在所有现代处理器上都经过硬件加速。

比较这两种用于存储产品价格的方法：

第二种方法确保像19.99这样的价格精确存储，在计算或显示期间永远不会出现舍入误差。第一种方法可能在内部将19.99表示为19.989999...，导致总和计算和客户显示价格出现细微差异。

日期和时间数据类型

日期和时间类型存储时间信息——事件发生的时刻或数据应被视为相关的时间。这些类型对于时间序列分析、事件日志记录以及跟踪事件发生的业务流程至关重要。

DATE 类型仅存储日期部分——年、月、日，格式为 YYYY-MM-DD，不包含任何时间部分。当您需要仅记录某件事发生的日期，比如客户的出生日期或交易日期，而不关心具体的小时或分钟时，使用 DATE 类型。DATE 占用最少的存储空间（通常为 3 字节），并简化了按日历日分组事件的查询。

TIME 类型仅存储时间部分——小时、分钟和秒，不包含日期。TIME 不如 DATE 或 TIMESTAMP 常用，但出现在记录周期性时间的模式中，比如营业时间或一天内的预约时间。

TIMESTAMP 类型（在某些系统如 MySQL 和 SQL Server 中称为 DATETIME）以 YYYY-MM-DD HH:MM:SS 格式存储日期和时间信息。TIMESTAMP 捕获事件发生的完整时刻，精确到秒（或更细，取决于数据库）。大多数事件驱动系统使用 TIMESTAMP 来记录日志条目创建、订单下达或传感器读数到达的确切时间。许多构建于星型模式设计的分析系统使用 TIMESTAMP 键进行高效的时态分析和历史事实跟踪。

根据查询模式选择 DATE 与 TIMESTAMP。如果业务逻辑按日历日分组事件，且永远不需要日内精度，DATE 更简洁高效。如果需要计算事件之间经过的时间、检测小时内趋势或维护精确的时间顺序，则必须使用 TIMESTAMP。

日期和时间列定义的示例：

在这里，birthdate 使用 DATE，因为只关心人的出生日期，而不关心出生的时间。account_creation_date 使用 TIMESTAMP，因为需要知道账户创建的确切时间，可能用于检测欺诈模式或计算账户年龄（以天为单位）。preferred_contact_time 使用 TIME，因为正在存储一个定期时间，如“下午2点给我打电话”，没有具体的日期。

日期和时间数据的时区考虑

时间数据中一个微妙但关键的问题是时区处理。当您记录事件发生在“2024-03-15 14:30:00”时，那是指纽约、东京还是 UTC 的下午2:30？答案很重要，因为相同的墙上时钟时间在不同时区代表不同的含义。

最佳实践是将所有时间戳存储在 UTC（协调世界时），这是一个与时区无关的时间参考。当您的应用程序接收到来自任何时区用户的事件时，在存储到数据库之前将其转换为 UTC。这种方法确保所有时间戳都是可比较的，并且您可以明确地回答诸如“哪些事件先发生？”或“这些事件之间经过了多少时间？”等问题。

一些数据库如 PostgreSQL 支持 TIMESTAMPTZ（带时区的时间戳），它存储时间戳以及相关的时区信息。当您检索数据时，如果需要，数据库会将 UTC 时间戳转换回原始时区。这种方法保留了原始时区上下文，同时确保内部一致性。

SQL Server 的 DATETIME 和 MySQL 的 DATETIME 不包含时区信息，因此在存储前将时间转换为 UTC，并在显示给用户时转换回来。会话设置可能影响某些数据库中时间戳的解释方式，因此请明确记录您的假设。