PHP小码农

Appearance

数值类型选择指南 ​

本节把前面五节讲过的所有数值类型汇总成一张可执行的决策表。你将学会按"精度要求 → 取值范围 → 存储成本 → 兼容性"四个维度快速选定类型，并避开最常见的选型错误。

前置知识

阅读本节前，建议先了解：整数类型（SMALLINT / INTEGER / BIGINT）

基础概念 ​

PostgreSQL 的数值类型分为精确类型与近似类型两大类，这是选型的第一刀。

大类类型存储空间精度典型用途
精确SMALLINT2 字节整数状态码、年龄、楼层
精确INTEGER4 字节整数主键、数量、计数值（默认选择）
精确BIGINT8 字节整数大表主键、毫秒时间戳、累计计数
精确NUMERIC(p, s) / DECIMAL变长精确小数金额、税率、坐标、科学记数
近似REAL / FLOAT44 字节约 6 位有效数字传感器原始值、近似统计
近似DOUBLE PRECISION / FLOAT88 字节约 15 位有效数字科学计算、几何距离
近似MONEY8 字节固定 2 位小数单币种内部批处理（多数场景不推荐）
特殊SERIAL / BIGSERIAL / IDENTITY同底层整型整数自增主键（推荐 IDENTITY）

关键术语：

  • 精确类型（Exact Type）：smallint / integer / bigint / numeric，运算结果精确，比较可靠。
  • 近似类型（Approximate Type）：real / double precision / money，为了范围或速度牺牲精度。
  • 存储开销（Storage Cost）：列值占用的字节数，影响 IO 与缓存命中率。
  • 类型提升（Type Promotion）：混合运算时 PostgreSQL 按类型优先级自动转换，可能悄悄降低精度。

一个极易被忽略的事实是：SQL 里写 1.5 这个字面量的类型是 numeric，不是浮点。

sql
SELECT pg_typeof(1.5) AS bare_decimal, pg_typeof(1.5::FLOAT8) AS explicit_float;
text
 bare_decimal | explicit_float
--------------+----------------
 numeric      | double precision
(1 row)

这意味着 SELECT 0.1 + 0.2; 得到的是 0.3（精确），而 SELECT 0.1::FLOAT8 + 0.2::FLOAT8; 得到 0.30000000000000004。选型时想清楚你到底要哪一种。

语法与用法 ​

四类最常用的建表写法：

sql
-- 最简可运行示例：同一张表里各类型各就其位
CREATE TABLE type_showcase (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  age           SMALLINT          NOT NULL CHECK (age BETWEEN 0 AND 200),
  stock         INTEGER           NOT NULL DEFAULT 0,
  views         BIGINT            NOT NULL DEFAULT 0,
  price         NUMERIC(18, 4)    NOT NULL CHECK (price >= 0),
  currency      CHAR(3)           NOT NULL DEFAULT 'CNY',
  latitude      NUMERIC(9, 6),
  temperature   REAL,
  distance_km   DOUBLE PRECISION
);

INSERT INTO type_showcase (age, stock, views, price, latitude, temperature, distance_km)
VALUES (30, 120, 9876543210, 1299.9000, 39.904200, 23.5, 1234.5678);

SELECT id, age, stock, views, price, latitude, temperature, distance_km FROM type_showcase;
text
 id | age | stock |   views    |  price   |  latitude  | temperature | distance_km
----+-----+-------+------------+----------+------------+-------------+-------------
  1 |  30 |   120 | 9876543210 | 1299.9000| 39.904200  |        23.5 |   1234.5678
(1 row)

这张表体现了选型的基本思路：age 用最小够用的 SMALLINT，stock 用 INTEGER，可能超 21 亿的计数用 BIGINT，金额用 NUMERIC(18,4) 配 currency 列，坐标用 NUMERIC(9,6) 因为经纬度需要精确到米。

详细说明 ​

决策流程一：整数怎么选 ​

text
值域会不会超过 2,147,483,647？
├─ 不会 → INTEGER（4 字节，默认选择）
└─ 会   → BIGINT（8 字节）
        └─ 值域小于 32,767 且列数极多、存储极敏感？
              └─ SMALLINT（2 字节）

关键考虑：

考虑点说明
主键永远用 BIGINT。32 位主键耗尽后扩容代价极高
枚举/状态SMALLINT，或直接用 TEXT + CHECK 更易读
数量/库存INTEGER 通常够，超出预期时直接上 BIGINT
时间戳（毫秒）必须 BIGINT（毫秒时间戳已超 32 位）
溢出风险无溢出检查时插入报错，比静默截断好，但要提前规划
sql
-- 32 位整数上限的实际影响：毫秒时间戳
SELECT 1735689600000::BIGINT  AS ms_ok,
       1735689600000::INTEGER AS ms_broken;
text
ERROR:  integer out of range

决策流程二：小数怎么选 ​

text
这个值需要"精确相等"或"财务对账"吗？
├─ 需要 → NUMERIC(p, s)
│        └─ 需要多长有效数字？金额常用 NUMERIC(18,2)~NUMERIC(20,8)
└─ 不需要 → 需要超过 15 位有效数字吗？
          ├─ 不需要 → REAL（4 字节，省一半空间）
          └─ 需要   → DOUBLE PRECISION（8 字节）

判断"是否需要精确"的三个问题：

  1. 两个值相加后再相减，结果必须回到原值吗？→ 是则用 NUMERIC
  2. 不同客户端／不同环境算出的结果必须逐位一致吗？→ 是则用 NUMERIC
  3. 值本身是测量值，只有物理意义而无精确性吗？→ 是则可用浮点
sql
-- 精确 vs 近似：同一个累加操作
SELECT
  sum(v)::NUMERIC AS exact_sum
FROM (SELECT 0.1::NUMERIC AS v FROM generate_series(1, 10)) t;
text
 exact_sum
-----------
       1.0
(1 row)
sql
SELECT
  sum(v)::NUMERIC AS approx_sum
FROM (SELECT 0.1::FLOAT8 AS v FROM generate_series(1, 10)) t;
text
    approx_sum
------------------
 0.9999999999999999
(1 row)

决策流程三：自增主键怎么选 ​

方案写法推荐度理由
GENERATED ALWAYS AS IDENTITYid BIGINT GENERATED ALWAYS AS IDENTITY★★★★★SQL 标准，权限/所有权语义清晰
GENERATED BY DEFAULT AS IDENTITYid BIGINT GENERATED BY DEFAULT AS IDENTITY★★★★需要导入指定 ID 时用
BIGSERIALid BIGSERIAL★★★向后兼容，但权限边界模糊
SERIALid SERIAL★★仅 32 位，新项目不应再用
sql
-- 推荐写法
CREATE TABLE new_style (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  v  TEXT
);

在既需要 BIGINT 宽度、又需要 ALWAYS 严格语义的场景，IDENTITY 是唯一正确选择。

决策流程四：聚合与计算的类型提升 ​

了解类型提升规则，才知道"算出来的列"是什么类型：

操作输入类型输出类型
sum()smallint / integerBIGINT
sum()bigintNUMERIC
sum()numericNUMERIC
sum()realREAL
sum()double precisionDOUBLE PRECISION
avg()任意整数NUMERIC
avg()numericNUMERIC
avg()real / float8DOUBLE PRECISION
count()任意BIGINT
int + int两者都是 integerINTEGER（可能溢出）

验证一下：

sql
CREATE TABLE promo (
  qty_int    INTEGER,
  qty_big    BIGINT,
  qty_num    NUMERIC(18,2),
  ratio_real REAL
);

INSERT INTO promo VALUES (100, 100, 100.00, 0.5);

SELECT
  pg_typeof(sum(qty_int))    AS sum_int,
  pg_typeof(sum(qty_big))    AS sum_big,
  pg_typeof(sum(qty_num))    AS sum_num,
  pg_typeof(avg(qty_int))    AS avg_int,
  pg_typeof(count(*))        AS count_type
FROM promo;
text
 sum_int | sum_big | sum_num | avg_int | count_type
---------+---------+---------+---------+------------
 bigint  | numeric | numeric | numeric | bigint
(1 row)

sum(integer) 返回 BIGINT 是重要保护：即使表里全是 INTEGER，求和也不会溢出 32 位。但 sum(bigint) 返回 NUMERIC 意味着聚合结果会比列本身大不少，大面积 sum 要留意内存。

决策流程五：存储成本对比 ​

假设有 1 亿行数据，各类型一列占用的空间：

类型单值字节1 亿行占用相对 NUMERIC(18,2)
SMALLINT2≈ 200 MB—
INTEGER4≈ 400 MB—
BIGINT8≈ 800 MB—
REAL4≈ 400 MB—
DOUBLE PRECISION8≈ 800 MB—
MONEY8≈ 800 MB略小
NUMERIC(18,2)8~10≈ 800 MB~1 GB基准
NUMERIC（无约束，大数）12~16≈ 1.2~1.6 GB更大

实测几种典型值：

sql
SELECT
  pg_column_size(1::SMALLINT)          AS smallint_bytes,
  pg_column_size(1::INTEGER)           AS integer_bytes,
  pg_column_size(1::BIGINT)            AS bigint_bytes,
  pg_column_size(1::REAL)              AS real_bytes,
  pg_column_size(1::DOUBLE PRECISION)  AS float8_bytes,
  pg_column_size(1.00::NUMERIC(18,2))  AS numeric_18_2_bytes,
  pg_column_size(123456789012345678.90::NUMERIC) AS numeric_wide_bytes;
text
 smallint_bytes | integer_bytes | bigint_bytes | real_bytes | float8_bytes | numeric_18_2_bytes | numeric_wide_bytes
----------------+---------------+--------------+------------+--------------+--------------------+--------------------
              2 |             4 |            8 |          4 |            8 |                  8 |                 16
(1 row)

结论很清楚：NUMERIC(18,2) 与 BIGINT 同样占 8 字节，没有额外代价。所以"为了省空间用浮点存金额"是完全站不住的。

决策流程六：按业务场景速查 ​

业务场景推荐类型理由
主键 / 外键BIGINT值域充足，避免后期迁移
订单金额NUMERIC(18,4) + CHAR(3) 币种精确、多币种
单价、税率NUMERIC(10,6)乘法中间结果需要更多小数位
库存数量INTEGER32 位够用
浏览量 / 累计计数BIGINT会破 21 亿
年龄 / 评分（1~5）SMALLINT2 字节足够
经纬度NUMERIC(9,6)精确到约 0.11 米，且可精确比较
传感器读数REAL只有 3~4 位有效数字，省一半空间
科学计算中间量DOUBLE PRECISION需要 15 位有效数字
毫秒时间戳（整数）BIGINT超过 32 位
百分比NUMERIC(5,2)精确，如 99.99
折扣率NUMERIC(5,4)支持 0.0001 粒度

常见错误选型对照 ​

错误写法问题正确写法
price FLOAT8金额出现累加误差price NUMERIC(18,4)
price MONEYlocale 依赖、无法多币种NUMERIC(18,4) + currency
id SERIAL32 位可能耗尽id BIGINT GENERATED ALWAYS AS IDENTITY
id INTEGER（大表）同上BIGINT
qty SMALLINT库存可能超 32767INTEGER
score NUMERIC（无约束）无范围保护、变长开销大NUMERIC(5,2)
ratio FLOAT8 DEFAULT 0 + CHECK (ratio = 0)浮点等值比较不可靠改 NUMERIC 或用容差
code VARCHAR(10) 存状态改状态时要改结构SMALLINT 或 TEXT + CHECK

实战示例 ​

下面用一个"电商订单与商品"的完整建模流程，演示从需求到类型决策的全过程。

第 1 步：明确需求与数据特征 ​

需求要点：

  • 商品价格：人民币，保留 2 位小数，参与金额汇总，必须精确
  • 商品库存：可能超 10 万，不会超 21 亿
  • 销量：长期累计，可能超 21 亿
  • 评分：0.00 ~ 5.00，保留 2 位
  • 重量：克为单位，3 位小数，用于运费估算，允许近似
  • 经纬度：门店坐标，需要精确比较

第 2 步：按决策流程逐列选型 ​

列决策依据选定类型
id主键，值域充足BIGINT GENERATED ALWAYS AS IDENTITY
price精确金额NUMERIC(18,4) + currency CHAR(3)
stock不超 21 亿INTEGER
sold_count长期累计BIGINT
rating0~5 精确两位NUMERIC(3,2)
weight_g近似即可REAL
latitude / longitude需精确比较NUMERIC(9,6)

第 3 步：建表 ​

sql
DROP TABLE IF EXISTS products_master;

CREATE TABLE products_master (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         TEXT          NOT NULL UNIQUE,
  name        TEXT          NOT NULL,
  currency    CHAR(3)       NOT NULL DEFAULT 'CNY',
  price       NUMERIC(18,4) NOT NULL CHECK (price >= 0),
  stock       INTEGER       NOT NULL DEFAULT 0 CHECK (stock >= 0),
  sold_count  BIGINT        NOT NULL DEFAULT 0 CHECK (sold_count >= 0),
  rating      NUMERIC(3,2)  CHECK (rating BETWEEN 0 AND 5),
  weight_g    REAL          CHECK (weight_g > 0),
  latitude    NUMERIC(9,6)  CHECK (latitude  BETWEEN -90  AND 90),
  longitude   NUMERIC(9,6)  CHECK (longitude BETWEEN -180 AND 180),
  created_at  TIMESTAMPTZ   NOT NULL DEFAULT now()
);

第 4 步：插入数据 ​

sql
INSERT INTO products_master
  (sku, name, price, stock, sold_count, rating, weight_g, latitude, longitude)
VALUES
  ('SKU-001', '机械键盘',   399.0000,   120,  1234567, 4.75, 780.5,  39.904200, 116.407400),
  ('SKU-002', '显示器',    1299.9000,    35,   456789, 4.50, 4200.0, 39.904200, 116.407400),
  ('SKU-003', '人体工学椅', 999.0000,  1000, 2000000000, 4.90, 18500.0, 31.230400, 121.473700);

SELECT id, sku, name, price, stock, sold_count, rating, weight_g FROM products_master ORDER BY id;
text
 id |   sku   |    name    |   price   | stock | sold_count | rating | weight_g
----+---------+------------+-----------+-------+------------+--------+----------
  1 | SKU-001 | 机械键盘   |  399.0000 |   120 |    1234567 |   4.75 |    780.5
  2 | SKU-002 | 显示器     | 1299.9000 |    35 |     456789 |   4.50 |   4200.0
  3 | SKU-003 | 人体工学椅 |  999.0000 |  1000 | 2000000000 |   4.90 |  18500.0
(3 rows)

注意 sold_count 已经到 20 亿——如果用 INTEGER，下一行就会报错；用 BIGINT 则毫无压力。

第 5 步：验证金额精确性 ​

sql
-- 金额汇总必须精确
SELECT
  sum(price)                        AS total_price,
  sum(price * stock)                AS total_inventory_value,
  pg_typeof(sum(price))             AS sum_type
FROM products_master;
text
 total_price | total_inventory_value | sum_type
-------------+-----------------------+----------
  2697.8000  |          1424310.0000 | numeric
(1 row)

结果正好是 2697.8000（精确），如果 price 是 FLOAT8 可能得到 2697.8000000000002。

第 6 步：验证库存类型的选择 ​

sql
-- 错误示范：sold_count 用 INTEGER，插入超过 21 亿的值
CREATE TEMP TABLE bad_counter (sold INTEGER);
INSERT INTO bad_counter VALUES (3000000000);
text
ERROR:  integer out of range
sql
-- 正确：BIGINT 毫无压力
SELECT 3000000000::BIGINT AS ok;
text
     ok
-------------
 3000000000
(1 row)

第 7 步：按存储成本估算表规模 ​

sql
SELECT
  pg_column_size(id)         AS id_b,
  pg_column_size(price)      AS price_b,
  pg_column_size(stock)      AS stock_b,
  pg_column_size(sold_count) AS sold_b,
  pg_column_size(rating)     AS rating_b,
  pg_column_size(weight_g)   AS weight_b,
  pg_column_size(latitude)   AS lat_b
FROM products_master WHERE id = 1;
text
 id_b | price_b | stock_b | sold_b | rating_b | weight_b | lat_b
------+---------+---------+--------+----------+----------+-------
    8 |       8 |       4 |      8 |        6 |        4 |     8

单行这些列合计约 46 字节。按 5000 万商品估算约 2.3 GB——这个量级下，用 SMALLINT 替 INTEGER 省下的 200 MB 完全不值得牺牲可维护性。选型优先考虑正确性，存储优化只在大表实测有瓶颈时才做。

第 8 步：清理环境 ​

sql
DROP TABLE IF EXISTS products_master, promo, type_showcase;

注意事项 ​

  • 不要"为了省空间"给主键选 INTEGER 或 SERIAL。SERIAL 在 21 亿行后耗尽，届时改类型需要长时间锁表重写。
sql
-- 不推荐
-- id SERIAL PRIMARY KEY

-- 推荐
-- id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
  • 金额绝不使用 REAL / DOUBLE PRECISION / MONEY。这是选型里唯一没有例外的规则。
sql
-- 错误！金额用浮点
CREATE TEMP TABLE bad_money (price DOUBLE PRECISION);
INSERT INTO bad_money VALUES (0.1), (0.2);
SELECT sum(price) AS bad_total FROM bad_money;
text
       bad_total
-----------------------
 0.30000000000000004
(1 row)
  • NUMERIC 不带精度参数时既无范围保护也是变长，一定要写 (p, s)：
sql
-- 不推荐：没有上界约束，存储 12~16 字节起步
-- amount NUMERIC

-- 推荐
-- amount NUMERIC(18, 4) CHECK (amount >= 0)
  • avg() 一定返回 NUMERIC 或 DOUBLE PRECISION，不会是整数，不要指望 avg(integer) 得到整数：
sql
SELECT avg(v) AS avg_val, pg_typeof(avg(v)) AS t FROM (VALUES (1), (2)) AS x(v);
text
 avg_val |    t
---------+---------
     1.5 | numeric
(1 row)
  • 整数除法会截断，这是最容易被忽略的精度事故：
sql
SELECT 7 / 2 AS int_div, 7::NUMERIC / 2 AS num_div, 7.0 / 2 AS literal_div;
text
 int_div | num_div | literal_div
---------+---------+-------------
       3 |   3.5000000000000000 |         3.5
(1 row)

7 / 2 = 3。需要小数结果时必须显式转型或用小数标度。

  • smallint 溢出风险常被低估。库存、计数器、年份差值都可能超 32767。
  • 混用类型会让索引失效。列是 BIGINT 而查询条件传字符串或 NUMERIC，可能触发隐式转换：
sql
-- 假设 big_col 是 BIGINT 且有索引
-- WHERE big_col = '123'      -- 可以走索引（常量会被推导）
-- WHERE big_col = 123.0::NUMERIC  -- 可能不走索引
  • 不要用 CHAR(3) 之外的方式存币种代码（如用整数 1=CNY、2=USD），除非确实需要极致紧凑——可读性与可维护性损失更大。
  • REAL 存坐标会带来约 1 米级误差（在赤道附近），需要精确位置时用 NUMERIC。
sql
SELECT 39.904200::REAL::NUMERIC AS real_lat, 39.904200::NUMERIC AS exact_lat;
text
   real_lat   | exact_lat
--------------+-----------
 39.90420150756836 | 39.904200
(1 row)
  • 同一列在原型期用 INTEGER、上线后改 BIGINT 是有代价的：ALTER TABLE ... ALTER COLUMN TYPE 会重写整表。宁可一开始就宽。

最佳实践 ​

  • 默认用 INTEGER，主键和外键一律 BIGINT。这两条能覆盖 90% 的整数场景。
  • BIGINT GENERATED ALWAYS AS IDENTITY 是自增主键的标准答案，SERIAL 只用于维护老代码。
  • 所有小数先问"要不要精确"：要精确就 NUMERIC(p, s)，不要精确再考虑 REAL/DOUBLE PRECISION。
  • 金额列永远配三件套：NUMERIC(18, 4) + CHECK (amount >= 0) + currency CHAR(3)。
  • NUMERIC 的标度按"中间计算需要"而非"展示需要"来定。税率乘法会产生额外小数位，展示时再用 round(..., 2) 收敛。
  • 每个数值列都写 CHECK 约束。类型只管格式，范围要靠约束：
sql
CREATE TABLE checks_demo (
  age    SMALLINT     CHECK (age BETWEEN 0 AND 200),
  rating NUMERIC(3,2) CHECK (rating BETWEEN 0 AND 5),
  pct    NUMERIC(5,2) CHECK (pct BETWEEN 0 AND 100)
);
  • 不确定时选更宽的类型。多占几字节远比线上溢出后改表便宜。
  • 大表（亿级行以上）才做存储优化，且必须用实测数据（pg_column_size、pg_total_relation_size）支撑决策。
  • REAL 是存储敏感型时序数据的默认选择，前提是物理量精度需求不超过 6 位有效数字。
  • 能不用 MONEY 就不用；已有系统迁移时固定 lc_monetary 保证可复现。
  • 把选型决策写进 COMMENT ON COLUMN，说明为什么选这个类型：
sql
COMMENT ON COLUMN products_master.sold_count IS 'BIGINT：累计销量会超 21 亿';
COMMENT ON COLUMN products_master.price      IS 'NUMERIC(18,4)：精确金额，4 位小数兼容外汇';
  • numeric 与 float8 不要混在同一表达式的关键路径上，隐式转换会让精度问题变得难以定位。
  • 从别的数据库迁移时重点核对三处：SERIAL → IDENTITY、MONEY → NUMERIC、FLOAT → 明确选 REAL 还是 DOUBLE PRECISION。
  • 在代码层面统一类型映射：ORM 配置里把 NUMERIC 映射为高精度十进制类型（BigDecimal / Decimal / shopspring/decimal），绝不要映射成 float/double，否则前面所有努力在应用层前功尽弃。

下一节

继续学习：CHAR / VARCHAR / TEXT

参考链接 ​