数据类型与存储设计

PG 类型系统比 MySQL 更丰富：JSONB、数组、范围类型、UUID 等。DBA 需参与选型与迁移策略。与 /roadmaps/postgresql-dba 类型节点对齐。

学习目标

• 区分 text / varchar / char 与数值、时间类型
• 理解 JSONB 存储与 GIN 索引场景
• 规划从 MySQL 迁移时的类型映射

核心概念

常用类型对照表

用途	PostgreSQL	备注
主键	bigserial / uuid	UUID 需 pgcrypto 或应用生成
布尔	boolean	MySQL 用 TINYINT(1)
时间	timestamptz	推荐带时区
半结构化	jsonb	二进制 JSON，可索引
全文	tsvector	配合 GIN

CREATE TABLE app.cf_track (
  id          bigserial PRIMARY KEY,
  slug        text NOT NULL UNIQUE,
  title       text NOT NULL,
  metadata    jsonb NOT NULL DEFAULT '{}',
  is_member   boolean NOT NULL DEFAULT false,
  created_at  timestamptz NOT NULL DEFAULT now()
);

JSONB 查询

INSERT INTO app.cf_track (slug, title, metadata)
VALUES ('demo', 'Demo', '{"tags": ["dba", "pg"]}');

SELECT slug FROM app.cf_track
WHERE metadata @> '{"tags": ["dba"]}';

CREATE INDEX idx_track_metadata ON app.cf_track USING gin (metadata);

类型变更注意

ALTER TABLE app.cf_track
  ALTER COLUMN title TYPE varchar(200);  -- 可能锁表

大表改类型用 新列 + 回填 + 切换 降低锁时间；与 MySQL ALGORITHM=INPLACE 思路类似。

与 MySQL/Spring Boot 对照

MySQL	PostgreSQL	Spring/MyBatis
JSON	jsonb	String + TypeHandler 或 Map
DATETIME	timestamp / timestamptz	LocalDateTime / OffsetDateTime
AUTO_INCREMENT	serial / IDENTITY	@Id 自增策略一致
ENUM	原生 ENUM 或 CHECK	推荐 Java enum + varchar

本站 schema.sql 为 MySQL 方言；PG 版练习需去掉 ENGINE=InnoDB、改写 JSON→jsonb。

练习

• 为 cf_track 加 metadata jsonb 与 GIN 索引，验证 @> 走 Index Scan
• 写 MySQL→PG 类型映射表（至少 8 种常用类型）
• 测试 timestamptz 在不同时区客户端下的显示

小结

timestamptz + jsonb 是 PG OLTP 常见组合；迁移先定类型映射表。 下一课索引策略与维护。