数据表设计与数据类型

数据表设计与数据类型

设计一张表，核心是两件事：决定有哪些列，以及为每列选择合适的数据类型。本节介绍 PostgreSQL 常用数据类型，并给出选型建议，让你的表既能准确表达业务，又能高效存储与查询。

设计数据表的基本步骤

设计一张表，通常遵循这样的思路：

1. 明确这张表代表什么实体：是用户、订单、文章，还是评论？
2. 列出该实体的关键属性：用户有邮箱、昵称、头像；订单有金额、状态、时间。
3. 为每个属性选择合适的数据类型：邮箱用文本、金额用数值、状态用枚举或文本。
4. 确定主键：用什么唯一标识一行（详见下一节约束）。
5. 考虑与其他表的关系：是否需要外键关联（详见第四节）。

常用数据类型

PostgreSQL 的类型非常丰富，日常开发最常用的有下面几类。

数值类型

• integer（整数）：适合计数、数量，如商品库存、订单项数。
• bigint（大整数）：当数值可能超过 21 亿时使用，如自增主键、时间戳。
• numeric / decimal（定点数）：适合金额等对精度敏感的场景，可指定小数位，避免浮点误差。
• real / double precision（浮点数）：适合科学计算等可容忍微小误差的场景，不推荐用于金额。

金额字段一律用 numeric，切勿用浮点数，否则累加会出现几分钱的偏差。

文本类型

• text（变长文本）：PostgreSQL 中最常用的文本类型，无长度限制，适合绝大多数场景。
• varchar(n)（限定长度的变长文本）：当你希望强制最大长度时使用，如短信验证码。
• char(n)（定长文本）：用得较少，适合固定长度编码。

在 PostgreSQL 中，text 与 varchar 的性能几乎没有差别，除非需要限制长度，否则默认用 text 即可。

布尔类型

• boolean：只有「真 / 假」两个值，适合开关状态，如「是否已读」「是否启用」。

时间类型

• timestamptz（带时区的时间戳）：强烈推荐用于所有时间字段，它会把时间统一存储为 UTC，读取时再按时区转换，避免跨时区混乱。
• timestamp（不带时区）：仅当你确定无需跨时区时使用。
• date（日期）、time（时间）：分别只存日期或只存时间。

经验法则：所有时间字段默认用 timestamptz，能省去大量时区相关的麻烦。

UUID 类型

• uuid：一种 128 位的全局唯一标识，适合作为主键。UUID 的好处是可以在客户端生成、全局唯一、不暴露顺序信息；代价是体积比自增整数大、无序写入对索引略有影响。

Supabase 默认推荐用 UUID 作为业务表主键，并在创建行时自动生成。认证系统的用户 id 也是 UUID 类型。

JSONB 类型

• jsonb：PostgreSQL 的「杀手锏」类型之一，存储 JSON 格式的结构灵活数据，并支持对其内部字段建索引与查询。

jsonb 的价值在于「在严格的关系模型中保留一块灵活地带」。例如用户配置、第三方接口返回的动态结构、可扩展的元数据，都适合用 jsonb 存放，既不必为每个字段都建列，又能用 SQL 查询内部内容。

数组类型

• integer[]、text[] 等：每种基础类型都有对应的数组形式，适合存储「同类型的一组值」，如文章的标签列表、角色的权限码列表。

数组适合元素数量不大、且通常整体读取的场景；如果需要频繁按元素查询或建立多对多关系，仍推荐用联结表（见第四节）。

枚举类型

• enum：自定义一组可选值，如订单状态 ('pending', 'paid', 'shipped', 'done')。

枚举的好处是约束严格、可读性好；缺点是修改可选值需要变更类型。如果可选值会频繁变动，也可以用 text 配合应用层校验或检查约束。

实战选型速查表

一个示例：设计文章表

综合运用上述类型，一张「文章」表可以这样设计（用通用概念描述，不绑定具体语法细节）：

• id：UUID，主键，自动生成
• title：text，标题
• content：text，正文
• author_id：uuid，外键，关联作者
• tags：text[]，标签数组
• metadata：jsonb，额外元信息（封面、来源等）
• published：boolean，是否发布
• created_at：timestamptz，创建时间，默认当前时间

这套选型既覆盖了结构化字段，又用 jsonb 和数组保留了灵活性。

小结

数据类型的选择决定了存储效率、查询性能与数据准确性。核心原则是：精确优先——金额用定点数、时间用带时区、布尔用 boolean，避免用「万能的文本」承接一切。下一节将介绍如何用约束与主外键，让数据库主动拒绝不合法的数据。