夜雨聆风
AI 时代重新学 PostgreSQL(三):数据类型,PostgreSQL 比 MySQL 多了什么
上篇聊了架构层面的差异，这篇来看 PostgreSQL 最直观的优势——数据类型。
我第一次发现 PostgreSQL 的类型系统有多强大，是在处理一个用户标签功能的时候。MySQL 里我要么建一张 user_tags 关联表，要么用逗号分隔的字符串存（别笑，赶工期的时候真干过）。同事告诉我 PostgreSQL 里直接用数组字段就行，还能建索引查询。当时的感觉是：这也行？
后来越用越多，发现这种”这也行？”的时刻经常出现。
MySQL 5.7 开始支持 JSON 类型，PostgreSQL 从 9.4 就有了 JSONB。两者表面上都是”存 JSON”，但能力差距很大。
基本用法
-- 创建包含 JSONB 字段的表CREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, attrs JSONB DEFAULT '{}');-- 插入数据INSERT INTO products (name, attrs) VALUES('iPhone 14', '{"color": "black", "storage": 128, "5g": true}'),('MacBook Pro', '{"color": "silver", "cpu": "M2", "ram": 16, "ports": ["usb-c", "hdmi", "magsafe"]}'),('AirPods Pro', '{"color": "white", "anc": true, "battery_hours": 6}');查询 JSONB 内部的值
-- 提取某个键的值（返回 JSONB 类型）SELECT name, attrs->'color' FROM products;-- 提取某个键的值（返回文本类型）SELECT name, attrs->>'color' FROM products;-- 嵌套提取SELECT name, attrs->'ports'->0 FROM products;-- 按 JSONB 内部的值过滤SELECT * FROM products WHERE attrs->>'color' = 'black';-- 数值比较（需要类型转换）SELECT * FROM products WHERE (attrs->>'storage')::int >= 128;-> 返回 JSONB 类型，->> 返回文本类型。这个区别很重要，搞混了会在类型比较时踩坑。
JSONB 的包含查询
这是 MySQL JSON 做不到的：
-- 查找 attrs 包含 {"color": "black"} 的记录SELECT * FROM products WHERE attrs @> '{"color": "black"}';-- 查找 attrs 中包含 "anc" 这个键的记录SELECT * FROM products WHERE attrs ? 'anc';-- 查找 attrs 中包含 "color" 或 "cpu" 任一键的记录SELECT * FROM products WHERE attrs ?| array['color', 'cpu'];@> 是”包含”操作符，? 是”存在键”操作符。这些操作符可以利用 GIN 索引，性能非常好。
给 JSONB 建索引
-- GIN 索引，支持 @>、?、?|、?& 操作符CREATE INDEX idx_products_attrs ON products USING GIN (attrs);-- 如果只查询某个特定路径，可以用表达式索引更精准CREATE INDEX idx_products_color ON products USING BTREE ((attrs->>'color'));GIN 索引的细节我们在索引那篇会详细展开。这里你只需要知道：JSONB + GIN 索引 = 在关系型数据库里获得了接近文档数据库的灵活性，同时保留了 SQL 的全部能力。
JSONB 的修改操作
-- 添加或更新一个键UPDATE products SET attrs = attrs || '{"weight": "172g"}' WHERE name = 'iPhone 14';-- 删除一个键UPDATE products SET attrs = attrs - 'weight' WHERE name = 'iPhone 14';-- 修改嵌套的值UPDATE products SET attrs = jsonb_set(attrs, '{storage}', '256') WHERE name = 'iPhone 14';|| 是合并操作符，- 是删除键操作符，jsonb_set 用于修改嵌套路径的值。
什么时候该用 JSONB，什么时候不该
适合的场景：
-
• 属性不固定的数据（不同商品有不同的属性字段） -
• 第三方 API 返回的原始数据（先存下来再说） -
• 配置信息、元数据
不适合的场景：
-
• 需要频繁按某个字段做 JOIN 的数据——还是拆成独立的列或表 -
• 需要强约束的核心业务字段——JSONB 里的字段没有 NOT NULL、外键这些约束 -
• 所有记录结构都一样的数据——直接用普通列，性能更好、语义更清晰
数组类型
PostgreSQL 的数组类型是原生支持的，不是什么 hack。任何数据类型都可以变成数组。
CREATE TABLE articles ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, tags TEXT[] DEFAULT '{}', scores INTEGER[]);-- 插入数组数据INSERT INTO articles (title, tags, scores) VALUES('学习 PostgreSQL', ARRAY['postgresql', 'database', '学习笔记'], ARRAY[95, 88, 92]),('Go 并发编程', ARRAY['go', 'concurrency'], ARRAY[90, 85]),('Redis 实战', ARRAY['redis', 'cache', 'database'], ARRAY[88]);数组查询
-- 包含某个元素SELECT * FROM articles WHERE 'database' = ANY(tags);-- 包含所有指定元素SELECT * FROM articles WHERE tags @> ARRAY['database', 'cache'];-- 被指定数组包含SELECT * FROM articles WHERE tags <@ ARRAY['postgresql', 'database', '学习笔记', 'go'];-- 两个数组有交集SELECT * FROM articles WHERE tags && ARRAY['go', 'redis'];-- 访问数组元素（PostgreSQL 数组下标从 1 开始！）SELECT title, tags[1] AS first_tag FROM articles;-- 数组长度SELECT title, array_length(tags, 1) AS tag_count FROM articles;注意 PostgreSQL 的数组下标从 1 开始，不是 0。这是从学术传统继承来的，第一次用会不适应。
数组索引
-- GIN 索引支持 @>、<@、&& 等数组操作符CREATE INDEX idx_articles_tags ON articles USING GIN (tags);-- 有了这个索引，下面的查询会很快SELECT * FROM articles WHERE tags @> ARRAY['database'];数组 vs 关联表
你可能会问：用数组存标签和用关联表存标签有什么区别？
|
|
|
|
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
简单的标签、分类这类场景，数组完全够用。如果标签本身还有其他属性（比如标签的描述、创建时间），那还是关联表更合适。
范围类型
这是 MySQL 完全没有的概念。范围类型让你直接表示”从 A 到 B”的区间。
CREATE TABLE events ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, during TSRANGE NOT NULL -- 时间戳范围);INSERT INTO events (name, during) VALUES('技术分享会', '[2023-07-20 14:00, 2023-07-20 16:00)'),('团建活动', '[2023-07-22 09:00, 2023-07-22 18:00)'),('代码评审', '[2023-07-20 15:00, 2023-07-20 16:30)');方括号 [ 表示包含端点，圆括号 ) 表示不包含端点。
范围查询
-- 查找包含某个时间点的事件SELECT * FROM eventsWHERE during @> '2023-07-20 15:30'::timestamp;-- 查找两个事件时间是否重叠SELECT a.name, b.nameFROM events a, events bWHERE a.id < b.id AND a.during && b.during;-- 结果：技术分享会 和 代码评审 时间有重叠范围类型还支持排除约束，可以在数据库层面防止时间冲突：
-- 需要先启用 btree_gist 扩展CREATE EXTENSION IF NOT EXISTS btree_gist;-- 添加排除约束：同一时间段不能有重叠的事件ALTER TABLE eventsADD CONSTRAINT no_overlap EXCLUDE USING GIST (during WITH &&);-- 现在插入时间重叠的事件会报错INSERT INTO events (name, during)VALUES ('冲突的会议', '[2023-07-20 15:00, 2023-07-20 17:00)');-- ERROR: conflicting key value violates exclusion constraint在 MySQL 里实现同样的”时间不重叠”约束，你得写触发器或者在应用层做检查。PostgreSQL 在数据库层面就能保证。
其他实用类型
UUID
-- 启用 uuid 生成扩展CREATE EXTENSION IF NOT EXISTS "uuid-ossp";CREATE TABLE orders ( id UUID DEFAULT uuid_generate_v4() PRIMARY KEY, amount DECIMAL(10, 2));INSERT INTO orders (amount) VALUES (99.99);SELECT * FROM orders;-- id: 550e8400-e29b-41d4-a716-446655440000-- amount: 99.99MySQL 里你得用 CHAR(36) 或 BINARY(16) 存 UUID，然后自己生成。PostgreSQL 有原生类型，存储更紧凑（16 字节），比较和索引效率也更高。
网络地址类型
CREATE TABLE access_logs ( id SERIAL PRIMARY KEY, client_ip INET NOT NULL, subnet CIDR);INSERT INTO access_logs (client_ip, subnet) VALUES('192.168.1.100', '192.168.1.0/24'),('10.0.0.50', '10.0.0.0/8');-- 查找某个子网内的所有记录SELECT * FROM access_logs WHERE client_ip << '192.168.0.0/16';-- << 操作符表示"被包含在"处理 IP 地址和网段，用 INET 和 CIDR 类型比用字符串存方便太多了。你不用自己解析 IP、计算子网掩码，数据库帮你做。
小结
PostgreSQL 的类型系统是它最直接的竞争优势之一。总结一下核心要点：
-
• JSONB 让你在关系型数据库里获得文档数据库的灵活性，配合 GIN 索引查询性能很好 -
• 数组 适合存储简单的列表数据，省去了建关联表的麻烦 -
• 范围类型 是处理区间数据的利器，排除约束能在数据库层面保证数据一致性 -
• UUID、INET 等专用类型比用字符串模拟更安全、更高效
但别忘了：类型再好用也不是万能的。核心业务数据还是该用普通列就用普通列，JSONB 不应该变成”什么都往里面塞”的垃圾桶。选择数据类型的原则是：用最精确的类型表达你的数据语义。
下一篇我们聊索引系统——PostgreSQL 有六七种索引类型，什么时候该用哪种？