一直有读者追着问 PostgreSQL 的数组类型到底怎么用。问得最多的两个问题一个是“商品表里的标签字段用 text[] 好还是逗号分隔的字符串好”另一个是“给 text[] 字段建了 GIN 索引为什么查询还是慢”。这两个问题其实已经把 PostgreSQL 数组从选型到查询优化的大部分要点都串起来了。所以这篇就把“数组的创建与实战使用”这条线一次性理顺从建表、赋值、取值、更新、索引到常见坑全部拿实际能跑的 SQL 说话适合业务里已经在用 PostgreSQL、正在做字段设计优化、或者被关联表查询搞得有点头疼的同学。1. 选型之前先想清楚数组到底解决什么问题1.1 数组适合的典型业务场景先说结论数组适合存“值集合”不太适合存“关系实体”。什么叫值集合就是这些值本身没有额外属性你只关心它们存在不存在或者同时包含哪些。我经常遇到的好例子有这么几类用户标签一个用户身上挂了好几个标签比如“高净值”“已复购”“偏好线上渠道”标签之间没有顺序属性也没有附加时间文章关键词一篇文章存一串关键词最常做的查询是“包含关键词A或者关键词B的文章有哪些”固定顺序的数值快照比如商品近12个月的销量、某渠道近30天的日活这种天然就是一个有顺序的数值序列枚举白名单比如某次活动允许参与的渠道 ID 列表数量不大需要整体展示和判断成员。这些场景有一个共同点如果用字符串存查询时就得 LIKE %xx%慢且容易误匹配如果用关联表存展示的时候要 join 或者聚合查询路径绕了一大圈。数组类型把“一对多”压缩进一行配合 GIN 索引查询还是集合语义非常顺手。1.2 数组、关联表、JSONB 到底怎么选这个问题几乎每次讨论数组都会带出来我直接给一张对比表配合实际判断逻辑。对比维度text[] / int[] 数组关联表JSONB关系本身是否有属性不行只有值和顺序可以加列就行不太方便需要嵌套对象单个页面连带展示主表查询一次带回需要二次 join 或用子查询聚合一次带回但 JSON 解析有额外成本高频按单个元素更新要重写整个数组不适合单行更新锁粒度小jsonb_set 支持局部更新包含/重叠查询GIN 索引支持效率高EXISTS 普通索引GIN 表达式索引也支持数据规范性无法对元素做外键约束可以加外键、约束同样无法约束元素统计聚合unnest 后聚合表大了偏重直接 GROUP BY也可以但路径更绕我自己的选择逻辑是查询模式主要是“包含”“重叠”“按顺序取第几个”优先数组如果这个“多”的一侧本身还有属性比如“用户于某时间加入某个标签”“订单与商品的关系里有数量”老老实实用关联表如果结构不固定、字段经常增减JSONB 更自由。在某个活动运营后台里我给“用户标签”用了 text[]因为运营只想知道某个人有哪些标签、某个标签覆盖多少人。后来需求升级成“记录标签变更时间”我没有在数组上硬塞结构而是另建了一张标签变更流水表数组保留用于画像筛选两边各司其职。这是数组用得舒服的关键。1.3 哪些情况别硬用数组数组不是万能的下面几种情况我踩过或者见过别人踩过第一需要频繁更新其中某一个元素。比如一行里存了 31 天的日数据每天都要更新其中某一天的值。数组的更新单位是整行即使你只改 tags[5]底层也会触发整行重写更新频率高了以后锁竞争和 WAL 量都会变大。这种场景拆明细表更合理。第二元素数量可能很大。数组五六十个以内很舒服如果奔着几千上万个元素去每次 unnest 扫描的代价、GIN 索引的体积都会上升。这个时候你实际上已经把一个集合塞进一个单元格了该重新考虑归一化。第三需要对元素做完整性约束。数组不支持对元素加外键也无法保证“数组里的值必须来自字典表”。如果数据一致性要求高还是关联表可控。之前某后台用 int[] 存“每日新增用户数”一开始 31 个元素体验很好。后来业务要求按天回刷数据高频 UPDATE 同一个数组线上出现了不少锁等待最后改成每日明细表加物化视图反而更清爽。数组选型永远要跟着查询路径和更新频率走。2. 数组的创建与赋值四种写法各有讲究2.1 建表时定义数组字段数组类型在 PostgreSQL 里的语法很简单就是“类型名 []”。定义 text[]、int[]、numeric[]、timestamptz[] 都可以只要元素类型本身合法。CREATE TABLE products ( id bigserial PRIMARY KEY, name text NOT NULL, tags text[] DEFAULT {}, category_ids int[], prices numeric[], created_at timestamptz[] );注意几个小细节。字段如果不给默认值默认是 NULL不是空数组。业务上如果希望“没有标签”和“还没设置标签”是同一件事建议 DEFAULT {}::text[]。否则后面写查询条件的时候每个数组都得包一层 COALESCE很容易漏。数组也可以写成 tagname text ARRAY 这种语法但我从不在 DDL 里这么写一是辨识度低二是“[]”的位置更直观团队里谁看了都明白。2.2 手动赋值ARRAY 构造函数和数组字面量给数组字段插入数据最常用的是 ARRAY 构造函数INSERT INTO products (name, tags, category_ids) VALUES ( 苹果, ARRAY[水果, 生鲜, 今日特价], ARRAY[2, 7] );ARRAY 构造函数的好处是可以写表达式、变量、子查询不容易被转义规则坑。比如INSERT INTO products (name, tags, category_ids) VALUES ( 香蕉, ARRAY[热带水果, 进口], ARRAY(SELECT id FROM categories WHERE parent_id 1 ORDER BY sort_no) );ARRAY(SELECT ...) 这种子查询构造非常适合批量初始化把原本要写循环灌数据的活一句 SQL 搞定。另一种写法是数组字面量也就是带引号的字符串形式INSERT INTO products (name, tags, category_ids) VALUES (香蕉, {热带水果,进口}::text[], {3,8}::int[]);字面量的解析规则更严格元素里有逗号、反斜杠、双引号时都得做转义新手很容易在这里写错。日常手动造数据我优先用 ARRAY 构造函数字面量主要用于备份导出、配置脚本这类场景。2.3 用函数把字符串拼成数组从逗号分隔字符串生成数组实战里非常常见尤其是从 Excel、CSV、历史表迁移数据的时候SELECT string_to_array(a,b,c, ,); -- 结果{a,b,c} SELECT regexp_split_to_array(a1b2c3, [0-9]); -- 结果{a,b,c}string_to_array 是纯按分隔符切分输入是普通字符串不存在数组字面量的转义问题。这里有一个容易踩的版本差异对于空字符串和 NULL 参数不同 PostgreSQL 版本返回的结果不完全一致。项目里我一般应用层先把空串处理掉不让空字符串直接进函数避免查询结果出现“NULL 数组和空数组被当成两个状态”的困惑。还有一个中文场景分隔符是中文逗号的时候千万要确认输入里到底是不是同一个字符。之前有个同事用程序把标签拼成“水果,生鲜”入库后数组确实只有一个元素因为标签串里混入了中文逗号“”而 string_to_array 的分隔符写的是英文逗号排查了半天。2.4 多维数组的认知二维数组建表时写 int[][]插入时对应CREATE TABLE matrix_demo ( id int PRIMARY KEY, data int[][] ); INSERT INTO matrix_demo VALUES (1, ARRAY[[1, 2, 3], [4, 5, 6]]);多维数组有个硬性约束它必须是规则的矩形每一维的子数组长度必须一致不能存“第一行2个、第二行3个”这种高低不平的数据。这是很多新手用不惯它的原因。项目里如果有确实需要矩阵形状的数据比如渠道和日期的交叉指标可以用。读取时SELECT data[1][2] FROM matrix_demo WHERE id 1; -- 结果是 2日常业务里多维数组用得不多常见的是标签、ID 列表、字符串列表这类一维数组。先掌握一维比什么都重要。3. 数组的查询与更新常见操作一次讲透3.1 按下标读取元素从 1 开始越界不报错PostgreSQL 数组的下标从 1 开始这一点和 C、Java、Python 都不一样非常容易在写代码时下意识写错。SELECT tags[1], tags[2], tags[3], tags[4] FROM products WHERE id 1;如果 tags 只有 3 个元素tags[4] 不会报错返回 NULL。这种静默行为容易被忽略在应用代码里尤其要小心别把“越界返回 NULL”当成“元素值为 NULL”去处理。切片返回的是数组子集SELECT tags[2:3] FROM products; SELECT tags[:2] FROM products; -- 前两个元素 SELECT tags[2:] FROM products; -- 从第二个到末尾切片结果依然是数组可以继续传给 unnest、array_append 这类函数。3.2 条件过滤包含、重叠、任意相等数组查询最常用的操作符就四个建议直接记下来-- 包含全部tags 必须同时包含“水果”和“特价” SELECT * FROM products WHERE tags ARRAY[水果, 特价]; -- 被包含左边数组是所有右边数组的子集 SELECT * FROM products WHERE ARRAY[水果] tags; -- 重叠至少含有一个右边数组中的元素 SELECT * FROM products WHERE tags ARRAY[特价, 进口]; -- 任意元素等于某个值 SELECT * FROM products WHERE 特价 ANY(tags);这里最容易混的是 和 。 是“包含全部” 是“有交集”。业务上“同时拥有标签A且标签B”用 “至少命中其中一个标签”用 。 ANY(tags) 是等值判断语义上和“ 集合里是否存在这个值”等价。一个容易翻车的写法是 tags[1] 特价这种只判断第一个元素的下标过滤不仅语义不对而且索引完全用不上。3.3 追加、删除、替换元素数组操作函数我按使用频率排一下-- 末尾追加 UPDATE products SET tags array_append(tags, 新品) WHERE id 1; -- 开头插入 UPDATE products SET tags array_prepend(tags, 新品) WHERE id 1; -- 合并两个数组 UPDATE products SET tags array_cat(tags, ARRAY[热卖, 限时]) WHERE id 1; -- 按值删除只删除第一个匹配项实际是删除所有等于该值的元素 UPDATE products SET tags array_remove(tags, 过期标签) WHERE id 1; -- 按值替换 UPDATE products SET tags array_replace(tags, 旧词, 新词) WHERE id 1; -- 按下标替换 UPDATE products SET tags[1] 应季 WHERE id 1;这些函数都遵循同一个原则返回一个新数组不修改原数组内存需要配合 UPDATE 的 SET 使用。有一个坑非常隐蔽array_remove 删除 NULL 是无效的。假设一个数组是 ARRAY[a, NULL, b]你执行 array_remove(arr, NULL)结果仍然是原数组因为 NULL 不能参与等值匹配-- 想删掉数组中所有 NULL 元素不能用 array_remove SELECT ARRAY( SELECT v FROM unnest(ARRAY[a, NULL, b]::text[]) AS v WHERE v IS NOT NULL ); -- 结果{a,b}这个写法在数据清洗时很有用。另外直接按下标赋值如果下标越界PostgreSQL 会在中间用 NULL 填充。比如空数组 {}执行 arr[5] x会得到一个下标 1 到 5、中间多个 NULL 的数组。表面上能写入但语义已经变了这种隐式扩展在业务里很难被察觉。3.4 unnest 与 array_agg数组和行的双向转换这是数组出入 SQL 场景最核心的组合。行业表数据要按数组聚合就用 array_agg数组字段要展开成行统计就用 unnest。-- 行转数组按分组聚合 SELECT category_id, array_agg(product_id ORDER BY product_id) AS product_ids FROM product_category_rel GROUP BY category_id; -- 数组转行同时保留原顺序 SELECT id, tag, tag_index FROM products CROSS JOIN LATERAL unnest(tags) WITH ORDINALITY AS t(tag, tag_index) WHERE id 1;WITH ORDINALITY 是重点它会把元素在数组里的下标一起返回。之前我在某个排序功能上踩过坑直接 unnest(tags) 返回行的顺序其实通常是数组顺序但一旦放进子查询、加过滤条件、再做窗口函数顺序就可能不稳定。后来统一改成显式保留 tag_index再也没出过问题。array_agg 还支持 FILTER 子句可以只聚合满足条件的行SELECT category_id, array_agg(product_id ORDER BY product_id) FILTER (WHERE status active) AS active_ids FROM product_category_rel GROUP BY category_id;这个能力做条件聚合很方便避免先 filter 再 group 多写一层。4. 性能优化GIN 索引怎么建才有效4.1 GIN 索引支持的核心查询数组字段要想查询快必须建 GIN 索引CREATE INDEX idx_products_tags ON products USING GIN (tags);GIN 会把数组的每个元素作为索引词条天然适合“包含”“重叠”这类查询。上面第 3 小节的 、、、 ANY(tags) 都能命中 GIN 索引。之前有张 200 多万行的用户表tags 平均 20 个元素没有索引时按标签筛选要扫全表几百毫秒甚至秒级建了 GIN 之后筛选高净值人群的查询稳定在几十毫秒以内。差距非常明显数组字段只要进入查询条件第一件事就是补索引。4.2 哪些写法会让索引失效和普通索引一样GIN 也不是万能的。下面几种情况我见过太多人踩-- 对函数结果做匹配无法走 GIN SELECT * FROM products WHERE tags[1] 水果; -- 对标量函数做过滤无法走 GIN SELECT * FROM products WHERE array_length(tags, 1) 5; -- 取反逻辑一般也不会走 GIN SELECT * FROM products WHERE NOT (tags ARRAY[特价]);实际业务里“排除某个标签”的需求确实存在但 NOT 数组操作通常很难利用 GIN 索引数据量大的时候会退化成全表扫描。我的处理办法是先用包含条件把候选集缩小再在应用层或子查询里做排除或者直接接受扫描优先保证核心的“包含筛选”走索引。判断索引有没有生效直接看执行计划EXPLAIN ANALYZE SELECT * FROM products WHERE tags ARRAY[水果, 特价];输出里出现 Bitmap Index Scan on idx_products_tags 就说明走索引了。没看到的话检查字段类型和赋值类型是否一致最常见的坑是 text[] 字段和 unknown 类型的字符串数组比较类型不匹配导致索引丢失。4.3 存储和写入层面的提醒数组在底层如果超过一定大小会进入 TOAST 行外存储也就是说更新数组字段时触发的行重写代价会比想象中高。频繁更新单行里的数组写放大很明显。我的经验是标签、关键词这类低频更新场景数组非常适合高频单点更新的数据不要硬塞数组。还有一个实际工程问题很多 ORM 对数组字段类型映射支持得不好。比如同样是文本数组某些 Java 框架不识别 text[]插入时会被当成一个字符串整个数组变成“{标签1,标签2}”这样的文本查询条件写了 tags ARRAY[标签1] 永远匹配不上。遇到这种情况要么在实体映射里自定义 Type要么用驱动提供的数组工厂创建参数别直接拼字符串。元素量特别大的数组客户端驱动传输也有压力。我一般约定单行数组元素超过 200 个就要重新评估设计方案能拆就拆不能拆先确认查询路径确实需要整体读取。4.4 排序、去重和部分更新的补充数组按第一个元素排序需要表达式索引CREATE INDEX idx_products_tags_first ON products ((tags[1]));这种使用场景非常少。如果业务经常按数组第一个值做排序或过滤我更倾向于把第一个值单独抽成一列别为了省一列去建表达式索引维护成本不划算。数组去重没有内置函数简单做法是SELECT ARRAY(SELECT DISTINCT v FROM unnest(tags) AS v WHERE v IS NOT NULL);这个写法在数据清洗时很常用但注意它不会保留原始顺序。要保留顺序需要把 unnest 的下标一起带出来再分组去重复杂一些我通常建议在应用层处理。5. 实战场景拆解标签、关键词、快照明细5.1 用户标签与人群筛选这是数组用得最多的场景。表结构CREATE TABLE users ( id bigint PRIMARY KEY, name text, tags text[] DEFAULT {} ); CREATE INDEX idx_users_tags ON users USING GIN (tags);查询“同时是高净值且已复购的用户”SELECT * FROM users WHERE tags ARRAY[高净值, 已复购];查询“命中高净值、已复购、活跃中任意一个标签的用户”SELECT * FROM users WHERE tags ARRAY[高净值, 已复购, 活跃];做人群画像时最常跑的就是标签分布统计SELECT unnest(tags) AS tag, count(*) AS user_count FROM users GROUP BY tag ORDER BY user_count DESC;这套方案在运营后台跑得非常顺。需要注意UNNEST 统计是扫描型查询如果数据量到了千万级每次即时统计会偏重建议跑进报表或者物化视图而不是让运营每次现算。5.2 文章关键词的报表统计内容平台经常要统计全站词频文章表里有关键词字段 keywords varchar[]。统计 Top 50 关键词SELECT keyword, count(*) FROM (SELECT unnest(keywords) AS keyword FROM articles) t GROUP BY keyword ORDER BY count(*) DESC LIMIT 50;数据量大可以建立物化视图CREATE MATERIALIZED VIEW keyword_daily AS SELECT keyword, count(*) AS cnt FROM (SELECT unnest(keywords) AS keyword FROM articles) t GROUP BY keyword; -- 每日刷新 REFRESH MATERIALIZED VIEW CONCURRENTLY keyword_daily;这个模式比应用层循环统计要靠谱得多数组展开成行再聚合正是 SQL 擅长的部分。5.3 订单里保存商品快照某些订单场景需要保留下单时的商品组合商品信息后续会被修改但订单不能跟着变。把商品 ID 列表存进 orders.product_ids bigint[]打开订单直接读取不需要回查商品表SELECT order_id, product_ids, product_ids[3] AS third_product_id FROM orders WHERE order_id 12345;反向查询“哪些订单包含某商品”SELECT order_id FROM orders WHERE product_ids ARRAY[10086];这个设计牺牲了一定的规范化换取了读取的简单性和历史快照的稳定性。适合订单类低频写入、高频读取的业务不适合商品维度需要频繁修改订单内容的场景。5.4 简单矩阵型数据的存储有一定规模的数据矩阵比如每个商品在每个区域近 7 天的销量用二维数组存也可以。但我不推荐为了节省几列就把业务数据压成矩阵PostgreSQL 不是计算引擎二维数组定位某个点很直接做跨维度聚合时 unnest 的写法会非常绕。真实业务里二维数组更多的是临时接收外部系统推来的网格数据存下来再转成明细行处理。能用一维数组解决的事尽量不升到二维。6. 常见问题与排查技巧实录6.1 空数组、NULL 数组、含 NULL 元素的三态问题数组字段有三种“空”的状态很多线上问题都是从这里开始的状态写法示例array_length说明空数组{}::text[]0数组存在但没有任何元素NULL 数组NULLNULL字段本身没值含 NULL 元素ARRAY[a, NULL]2数组存在元素里混有 NULL区分它们非常重要。查询条件如果写 tags ARRAY[水果]NULL 数组不会命中空数组更不会命中。需要把空数组和 NULL 统一处理时用 COALESCESELECT * FROM users WHERE COALESCE(tags, {}::text[]) ARRAY[水果];注意这种写法即使建了 GIN 索引也可能因为函数包裹不走索引。更好的做法是在写入时统一用 DEFAULT {}从源头上消灭 NULL 数组。6.2 下标从 0 开始还是从 1 开始PostgreSQL 数组默认从 1 开始访问 0 下标不会报错返回 NULL。很多应用层代码写惯了 arr[0]映射到 SQL 里就变成 tags[0]结果永远是 NULL。排查这类问题时先检查是 SQL 下标写错还是数据本身为空。切片边界如果写成 0:2PostgreSQL 会自动从第一个可用元素开始取所以 arr[0:2] 实际上等价于 arr[1:2]。这种隐式规则容易让人迷惑建议代码里直接用 1 作为下界。6.3 操作符匹配报错与隐式转换最常见报错是ERROR: operator does not exist: text[] unknown原因是右边写成裸字符串数组类型没有被推导出来PostgreSQL 不知道用什么算子匹配。解决方法是显式转换WHERE tags ARRAY[水果, 特价]::text[]; -- 或者 WHERE tags {水果,特价}::text[];字段类型是 int[] 时右边也要匹配 int[]WHERE category_ids ARRAY[2, 7]::int[];很多“为什么不走索引”的排查最后都落在这里类型隐式转换导致系统绕开了索引或者直接报错。SQL 里只要涉及数组比较我建议右边全部显式写类型避免调试时浪费半小时。6.4 字符串转数组的转义规则数组字面量 {a,b} 和 string_to_array(a,b, ,) 在简单数据上等价但遇到包含逗号、双引号、反斜杠的数据时字面量需要特殊转义-- 元素本身包含逗号必须用双引号包裹 SELECT {水果, 红色,热带水果}::text[]; -- 结果{水果, 红色,热带水果}从外部文件或用户输入迁移数据时优先使用 string_to_array因为它按纯字符分割没有转义负担SELECT string_to_array(水果,红色,热带水果, ,);另一个高频隐形问题传进来的字符串里带了不可见字符比如换行、中文逗号、全角空格导致数据被切错成一个大元素。排查时先把字符串转成二进制看看 ASCII 码或直接在应用层做 trim。6.5 数组与 JSON 的互相转换前后端联调时经常要把数组转成 JSON 输出SELECT to_jsonb(tags) FROM products WHERE id 1; -- 结果[水果, 生鲜, 今日特价]JSON 数组转回 PostgreSQL 数组SELECT ARRAY(SELECT jsonb_array_elements_text([水果, 特价]::jsonb)); -- 结果{水果,特价}如果 JSON 里有数字用 jsonb_array_elements_text 再转 int或者用 jsonb_array_elements 之后取 text 再转换写法会啰嗦一点。前端图表需要数组时直接让后端返回 to_jsonb 的结果即可。注意驱动对 text[] 的映射有的框架默认会变成 Java 的 String需要手动转 list工程细节容易漏所以我在接口文档里一般直接约定“数组字段一律输出 JSON 数组格式”。最后说点个人体会。数组类型我用下来最大的感受是选型比使用更重要。它适合“值集合”不适合“关系实体”。如果同一套数据你既要做包含查询又要做明细级的更新那才真的应该考虑关联表。实际项目里每个数组字段都值得问一句它一共有多少元素怎么被查询多久更新一次想清楚再建表后续会省很多事。还有一个小技巧非常实用任何需要按数组原始顺序展开成行的查询都务必在 unnest 后面加 WITH ORDINALITY 并保留下标列不要依赖返回顺序。曾经我在一个带排序展示的接口里直接 unnest 数组结果子查询套了一层窗口函数之后顺序乱了排查了很久才发现问题出在“隐式顺序”上。改成显式序号之后逻辑就完全可控了。数组这个东西用好了是利器用不好就是隐藏的雷关键还是设计时多想一步。