3 min read565 words

数据类型选择指南

选对数据类型，查询更快、存储更紧凑、应用代码更简洁。选错了，会为以后的重构埋下隐患。PostgreSQL 的类型系统远比 MySQL 丰富——理解它，才能充分发挥 PostgreSQL 的优势。

数值类型

类型	存储	范围	适用场景
`SMALLINT`	2 字节	-32,768 to 32,767	状态码、枚举类数字
`INTEGER`	4 字节	-2,147,483,648 to 2,147,483,647	一般用途 ID、计数
`BIGINT`	8 字节	-9.2×10¹⁸ to 9.2×10¹⁸	高并发自增 ID、大规模数据
`NUMERIC(p,s)`	可变	精确小数	金融金额（不能用 FLOAT）
`REAL`	4 字节	6 位有效数字	非精确浮点（科学计算）
`DOUBLE PRECISION`	8 字节	15 位有效数字	非精确浮点（机器学习特征值）

金融金额：永远用 NUMERIC

-- ❌ 错误：FLOAT 有精度损失
salary FLOAT,               -- 1234567.89 可能存为 1234567.890000001
-- ✅ 正确：NUMERIC(p, s) 精确存储
salary NUMERIC(12, 2),      -- 12 位有效数字，2 位小数，最大 9,999,999,999.99
price  NUMERIC(10, 4),      -- 电商价格
rate   NUMERIC(5, 4),       -- 汇率（0.0001 精度）

文本类型

类型	说明	MySQL 对比
`VARCHAR(n)`	可变长度，最长 n 个字符	等同
`TEXT`	无限长文本（实际上和 VARCHAR 性能相同）	LONGTEXT
`CHAR(n)`	固定长度，不足时用空格填充	等同

PostgreSQL 的 VARCHAR vs TEXT：

与 MySQL 不同，PostgreSQL 中 VARCHAR(n) 和 TEXT 的存储性能完全一样。
差别只在于 VARCHAR(n) 有长度约束检查，TEXT 没有。
结论：
- 需要限制长度时（如 username 最多 50 字符）→ VARCHAR(50)
- 不限制长度时（如评论、文章内容）→ TEXT（比 VARCHAR 更简洁）

日期和时间类型

类型	说明	推荐
`TIMESTAMP WITH TIME ZONE`（`TIMESTAMPTZ`）	带时区的时间戳，存储为 UTC	推荐，多时区应用
`TIMESTAMP WITHOUT TIME ZONE`	不带时区	避免（时区问题难以追踪）
`DATE`	只有日期，无时间	生日、节假日等
`TIME`	只有时间，无日期	营业时间等
`INTERVAL`	时间间隔	计算时间差

-- ✅ 推荐：所有时间戳都用 TIMESTAMPTZ
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ,                           -- 软删除
-- 时区感知查询
SELECT created_at AT TIME ZONE 'Asia/Kuala_Lumpur' FROM orders;
-- 常用时间操作
SELECT NOW();                                         -- 当前时间（含时区）
SELECT NOW() - INTERVAL '7 days';                     -- 7 天前
SELECT DATE_TRUNC('month', created_at) FROM orders;   -- 按月截断
SELECT EXTRACT(hour FROM created_at) FROM orders;     -- 提取小时

布尔类型

-- PostgreSQL 原生支持 BOOLEAN
is_active   BOOLEAN NOT NULL DEFAULT true,
is_deleted  BOOLEAN NOT NULL DEFAULT false,
-- 可接受的值：TRUE/FALSE, 't'/'f', 'yes'/'no', '1'/'0', 'on'/'off'
-- MySQL 迁移者注意：不要用 TINYINT(1) 模拟布尔，直接用 BOOLEAN

UUID 类型

-- PostgreSQL 原生支持 UUID 类型
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- PostgreSQL 13+ 内置函数
-- 或使用扩展（旧版本）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

UUID 生成策略	函数	特点
UUID v4	`gen_random_uuid()`	随机，无序，最常用
UUID v7（需扩展）	—	有序 UUID，适合主键
ULID（应用层生成）	—	字典序有序，26字符

数组类型

PostgreSQL 支持原生数组，MySQL 不支持：

-- 数组列定义
tags    TEXT[] NOT NULL DEFAULT '{}',
scores  INTEGER[],
phones  VARCHAR(20)[],
-- 插入数组数据
INSERT INTO users (name, tags) VALUES ('Alice', ARRAY['developer', 'admin']);
INSERT INTO users (name, tags) VALUES ('Bob', '{"designer","manager"}');
-- 数组查询
SELECT * FROM users WHERE 'admin' = ANY(tags);          -- 包含某元素
SELECT * FROM users WHERE tags @> ARRAY['admin'];        -- 包含数组（超集）
SELECT array_length(tags, 1) FROM users;                 -- 数组长度
SELECT unnest(tags) FROM users;                          -- 展开数组为行
-- 更新数组
UPDATE users SET tags = array_append(tags, 'superadmin') WHERE id = 1;
UPDATE users SET tags = array_remove(tags, 'admin') WHERE id = 1;

类型选择速查表

需求                           推荐类型
─────────────────────────────────────────
自增主键（小表 < 2 亿行）      SERIAL / INTEGER
自增主键（大表）               BIGSERIAL / BIGINT
随机唯一 ID                   UUID (gen_random_uuid())
金额、精确小数                 NUMERIC(p, s)
一般数字                      INTEGER
大整数                        BIGINT
浮点（非精确）                 DOUBLE PRECISION
短文本（有长度限制）           VARCHAR(n)
长文本（无限制）               TEXT
时间戳                        TIMESTAMPTZ
日期                          DATE
布尔值                        BOOLEAN
枚举（固定选项）               TEXT + CHECK 约束 / ENUM 类型
JSON 数据                     JSONB（见第 05 章）
多值标签/列表                  TEXT[] / INTEGER[] 数组
网络地址                      INET / CIDR
二进制数据                    BYTEA

本节记录清单

[ ] 项目中所有金额字段改用 NUMERIC(p, s)，不用 FLOAT
[ ] 所有时间戳使用 TIMESTAMPTZ，不用 TIMESTAMP
[ ] 主键选策略：小表用 SERIAL，大表用 BIGSERIAL，分布式用 UUID
[ ] 了解 PostgreSQL 数组类型，识别哪些字段可以从 JSON 改为原生数组

下一节：主键策略与约束设计——选好了列类型，接下来设计主键、外键和约束，让数据库帮你保证数据完整性。