SQL 数值数据类型

选择正确的数值类型可以确保数据完整性，优化存储空间，并提升查询性能。

本章将详细介绍 PostgreSQL 中的整数 (Integer)、定点数 (Decimal) 和浮点数 (Float) 数据类型，解释它们的特性、用例以及根据特定需求选择合适类型的注意事项。

1. 整数数据类型 (Integer Data Types)

整数是没有小数或分数部分的完整数字。PostgreSQL 提供了几种整数类型，它们在可存储的值范围和占用的存储空间上有所不同。

1.1 整数类型分类

PostgreSQL 提供以下整数类型：

• SMALLINT: 2 字节整数。
• INTEGER (或 INT): 4 字节整数。这是最常用的默认选择。
• BIGINT: 8 字节整数。

1.2 实际应用示例

• SMALLINT: 假设你要存储一年中的月份。因为需要的最大值只是 12，使用 SMALLINT 既足够又节省内存。

CREATE TABLE months (
    month_number SMALLINT
);
INSERT INTO months (month_number) VALUES (1);
INSERT INTO months (month_number) VALUES (12);

• INTEGER: 用于存储学校里的学生人数时，INTEGER 是一个很好的选择，因为学生人数极不可能超过 INTEGER 的最大值（约 21 亿）。

CREATE TABLE students (
    student_id INTEGER,
    student_name VARCHAR(255)
);
INSERT INTO students (student_id, student_name) VALUES (101, 'Alice');
INSERT INTO students (student_id, student_name) VALUES (2147483647, 'Bob');
-- INSERT INTO students (student_id, student_name) VALUES (2147483648, 'Charlie'); 
-- 上面这行会导致报错，因为超过了 INTEGER 的上限

• BIGINT: 当处理可能变得非常大的标识符、序列号或计数值（例如，网站总访问量、大型系统中的用户 ID）时，BIGINT 是合适的选择。

CREATE TABLE website_visits (
    visit_id BIGINT,
    visit_date TIMESTAMP
);
INSERT INTO website_visits (visit_id, visit_date) VALUES (1, NOW());
INSERT INTO website_visits (visit_id, visit_date) VALUES (9223372036854775807, NOW());

1.3 如何选择正确的整数类型

整数类型的选择取决于你期望存储的值范围。在合适的情况下使用较小的整数类型（如 SMALLINT）可以节省磁盘空间。但是，你必须确保所选类型能够容纳所有可能的值。如果存在超出最大值的风险，请务必选择更大的类型（如 INTEGER 或 BIGINT）。

假设场景： 想象一个社交媒体应用程序。用户 ID 最初可以作为 INTEGER 存储，但最终其规模可能会超过 INTEGER 的限制。为了防止未来进行昂贵且复杂的数据库迁移，即使初始用户群很小，从一开始就使用 BIGINT 作为用户 ID 也是一种明智的预防措施。

2. 定点数数据类型 (Decimal Data Types)

定点数数据类型（在 PostgreSQL 中具体为 NUMERIC 和 DECIMAL）用于存储具有固定精度 (Precision) 和标度 (Scale) 的数字。它们非常适合表示货币金额或任何对精确度要求极高的数值。

2.1 理解精度 (Precision) 和标度 (Scale)

• 精度 (Precision): 数字中的总有效位数。
• 标度 (Scale): 小数点右侧的位数。

例如，数字 123.45 的精度为 5，标度为 2。

2.2 NUMERIC 和 DECIMAL

在 PostgreSQL 中，NUMERIC 和 DECIMAL 本质上是完全相同的。你可以将列定义为 NUMERIC(precision, scale) 或 DECIMAL(precision, scale)。如果不指定精度和标度，该列可以存储达到系统实现上限的任何精度和标度的数字。

2.3 实际应用示例

• 存储价格： 对于存储产品价格，通常使用具有适当精度和标度的 DECIMAL 类型。

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255),
    price DECIMAL(10, 2) -- 精度为 10，标度为 2
);
INSERT INTO products (product_name, price) VALUES ('Laptop', 1200.50);
INSERT INTO products (product_name, price) VALUES ('Mouse', 25.99);

在这个例子中，DECIMAL(10, 2) 意味着你可以存储总共最多 10 位的数字，其中小数点后有 2 位。这适用于最高达 99999999.99 的货币值。

• 存储税率： 对于存储税率，NUMERIC 类型很合适。

CREATE TABLE tax_rates (
    tax_id SERIAL PRIMARY KEY,
    tax_name VARCHAR(255),
    rate NUMERIC(5, 4) -- 精度为 5，标度为 4
);
INSERT INTO tax_rates (tax_name, rate) VALUES ('Sales Tax', 0.0625);
INSERT INTO tax_rates (tax_name, rate) VALUES ('VAT', 0.2000);

这里，NUMERIC(5, 4) 允许存储像 0.0625 (6.25%) 这样的税率，总共 5 位数字，小数点后有 4 位。

2.4 注意事项

• 在定义 DECIMAL 或 NUMERIC 列时，始终要考虑最大可能的值和所需的精度。
• 使用比实际需要更大的精度会浪费存储空间。
• 如果只指定了精度而没有指定标度，则标度默认为 0（即变成整数）。

假设场景： 一个电子商务平台需要存储产品价格和折扣。使用 DECIMAL(12, 2) 存储价格允许高达 9999999999.99 的值，这对于大多数产品来说绰绰有余。对于折扣，可以使用 DECIMAL(5, 2) 来表示高达 999.99% 的百分比。

3. 浮点数数据类型 (Float Data Types)

浮点数数据类型用于存储近似的数值。PostgreSQL 提供两种主要的浮点类型：REAL（单精度）和 DOUBLE PRECISION（双精度）。这些类型适用于存储科学测量值或任何可以接受一定程度近似值的数据。

3.1 REAL 和 DOUBLE PRECISION

• REAL: 4 字节的浮点数。
• DOUBLE PRECISION: 8 字节的浮点数。

3.2 实际应用示例

• 存储科学测量值： 考虑存储温度读数。

CREATE TABLE temperature_readings (
    reading_id SERIAL PRIMARY KEY,
    temperature REAL,
    reading_time TIMESTAMP
);
INSERT INTO temperature_readings (temperature, reading_time) VALUES (25.5, NOW());
INSERT INTO temperature_readings (temperature, reading_time) VALUES (-4.2, NOW());

存储 GPS 坐标： 对于存储纬度和经度坐标，由于需要更高的精度，通常首选 DOUBLE PRECISION。

CREATE TABLE gps_coordinates (
    location_id SERIAL PRIMARY KEY,
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION
);
INSERT INTO gps_coordinates (latitude, longitude) VALUES (34.0522, -118.2437); -- 洛杉矶
INSERT INTO gps_coordinates (latitude, longitude) VALUES (40.7128, -74.0060);  -- 纽约

3.3 浮点数的局限性

浮点数在计算机底层是作为二进制分数存储的，这意味着它们不能总是精确地表示十进制分数。这会导致舍入误差。

例如，在许多编程语言和数据库中：

SELECT 0.1 + 0.2; 
-- 结果通常是：0.30000000000000004，而不是精确的 0.3

这是浮点算术的一个普遍问题，并不特定于 PostgreSQL。

3.4 何时使用浮点类型

在以下情况下使用浮点类型：

• 近似值是可以接受的（例如传感器数据）。
• 存储空间是一个关键考虑因素，且不需要 DECIMAL 提供的那种绝对精确度。
• 需要执行复杂的数学计算，且性能要求优先于绝对精度。

3.5 何时避免使用浮点类型

在以下情况下绝对避免使用浮点类型：

• 需要绝对精确度，例如处理货币、财务或计费数据。 在这些场景下，请务必使用 DECIMAL / NUMERIC。
• 由于底层二进制表示问题导致的微小舍入误差是不可接受的。

假设场景： 一个天气预报应用程序使用 DOUBLE PRECISION 来存储温度、湿度和风速。DOUBLE PRECISION 带来的极微小的不精确性是完全可以接受的，而且它在实时计算和数据处理方面的性能优势非常重要。