数组运算 · 把「多对多」压进一列 · PostgreSQL 特性详解

PostgreSQL 把数组当一等公民：列可以直接是 text[] / int[]，用 @> / && / ANY 做集合判断，用 UNNEST / array_agg 在「数组」和「行」之间双向穿梭，还能用 GIN 索引加速。标签系统、权限位、多选枚举，常能因此省掉一张中间表。

数组运算 · 把「多对多」压进一列

关系建模的第一课就是「别把多个值塞进一列」——要多对多，就建中间表。这是对的，但不是唯一的对。

PostgreSQL 把数组做成了一等公民：一列可以直接是 text[]、int[]、甚至 jsonb[]。对于「一篇文章有几个标签」「一个用户有几个角色」这类读多写少、不需要对元素做外键约束的场景，数组能让你省掉那张中间映射表，查询还更直观。

集合运算符：`@>` 、`<@` 、`&&`

假设 articles(name text, tags text[])，数组的威力在这几个运算符：

-- @> 「包含」:tags 里含有 'eng' 的文章
SELECT name FROM articles WHERE tags @> ARRAY['eng'];
-- -> ['a', 'c', 'd']

-- && 「有交集」:tags 与 [sales, pm] 沾边的文章
SELECT name FROM articles WHERE tags && ARRAY['sales', 'pm'];
-- -> ['b', 'c', 'd']

@> 包含、<@ 被包含、&& 有交集，把「集合关系」直接写进 WHERE。
还有 ANY / ALL：'eng' = ANY(tags) 等价于「tags 里有 eng」，x = ALL(arr) 要求全等。

换成没有数组的库，这些查询要么把标签展开成中间表再 JOIN，要么在 JSON 数组上写函数——都更绕。

UNNEST 与 array_agg：数组 ↔ 行的双向桥

数组和「行集」之间可以自由互转，这是它最灵活的地方。

UNNEST 把数组摊成行，于是能用普通的 GROUP BY 统计：

-- 每个 tag 在所有文章里出现了几次
SELECT tag, count(*)
FROM articles, UNNEST(tags) AS tag
GROUP BY tag ORDER BY count(*) DESC;
-- eng: 3 | admin: 2 | pm: 2 | sales: 1

array_agg 反过来，把多行聚合回一个数组：

-- 每个 tag 对应哪些文章名
SELECT tag, array_agg(name) AS names
FROM articles, UNNEST(tags) AS tag
GROUP BY tag;
-- admin: ['a', 'd'] | eng: ['a', 'c', 'd'] | pm: ['c', 'd'] | sales: ['b']

UNNEST 出、array_agg 回，一出一回，你就能在「规范化的行」和「紧凑的数组」两种形态间随意切换——这是数组建模真正好用的地方。配套还有 array_to_string（拼成 CSV）、array_length、array_position 等一票函数。

GIN 索引：让 @> 走索引

数组查询能不能快，取决于能不能索引。PostgreSQL 用 GIN（倒排索引） 覆盖数组：

CREATE INDEX idx_tags ON articles USING gin (tags);

GIN 是 Generalized Inverted iNdex——倒排索引。它为数组里每一个元素建一条「元素 → 包含它的行」的映射(posting list)。所以 tags @> ARRAY['eng'] 不必全表扫描，直接查 eng 这个词条对应的行列表。这套倒排机制，和它给 JSONB、全文检索加速用的是同一个 GIN——全文检索一章会再见到它。

实测里有个细节值得一提：数据量很小的时候，planner 可能仍然选择顺序扫描而不走 GIN。 这不是 bug——几十行数据，建索引、查索引的开销比直接扫一遍还大，优化器算过账，顺序扫更划算。索引的价值要到数据量上来才显现。

什么时候别用数组

数组是把双刃剑。它的本质是反规范化：

❌ 元素需要外键约束时别用。数组里的 tag_id 没法 REFERENCES tags(id)——数据库不会替你保证这些 id 真实存在。要强一致，还是老老实实建中间表。
❌ 元素本身有属性时别用。如果「标签」还要记「谁在何时打的」，那它就不该是数组，而是一张关系表。
✅ 读多写少、元素是简单值、不需引用完整性——标签、权限位、枚举多选——数组最香。

横向对比

能力	PostgreSQL	MySQL	Oracle
数组列类型	原生 `T[]`，任意类型	无，靠 `JSON` 数组模拟	`VARRAY` / 嵌套表，笨重
集合运算	`@>` `<@` `&&` `ANY`	`JSON_CONTAINS` 等函数	需 `TABLE()` 展开
数组↔行	`UNNEST` / `array_agg`	`JSON_TABLE`(8.0+)	`TABLE(...)`
索引加速	GIN 倒排	有限（多值索引 8.0.17+）	受限

MySQL 8.0 后能用 JSON 数组 + 多值索引凑出类似效果，但语法和性能都不如 PG 的原生数组顺手。Oracle 的 VARRAY 存在已久，却因用法笨重很少有人真拿它当数组用。

应用场景

标签系统：文章 / 商品 / 用户打标签，tags text[] + GIN 索引，@> 查「含某标签」，&& 查「含任一标签」。
权限位 / 角色：roles text[]，判断 'admin' = ANY(roles)，免一张 user_roles 中间表（前提：不需要对角色做外键）。
多选枚举：问卷多选、商品多规格，一列存下，查询直接用集合运算。
临时聚合：array_agg 把一组值收进数组回传给应用，少一次往返。

小结与下一章

数组让你在「规范化」和「紧凑」之间多了一个选项：不是所有「多个值」都必须拆成中间表。用对场景（读多、简单值、不需外键），它能显著简化模型与查询；用错场景（需引用完整性），它会把一致性责任甩给应用层。

如果说数组是「把一列变成一组同类值」，那下一个特性就是「把一列变成一整个文档」——JSONB，PostgreSQL 从关系库跨向文档库的那一步。👉 JSONB：关系库里长出一个文档库。

参考文献

[1] PostgreSQL 官方文档.「8.15. Arrays.」https://www.postgresql.org/docs/current/arrays.html —— 数组类型、字面量与下标语法。
[2] PostgreSQL 官方文档.「9.19. Array Functions and Operators.」https://www.postgresql.org/docs/current/functions-array.html —— @> && array_agg unnest 等完整清单。
[3] PostgreSQL 官方文档.「64.4. GIN Indexes.」https://www.postgresql.org/docs/current/gin.html —— GIN 倒排索引的结构与适用类型。

数组运算 · 把「多对多」压进一列

本页目录