文章目录一、数组基础1.1 创建含数组的表1.2 插入数组数据1.3 性能对比与选择建议1.4 使用建议二、数组查询SELECT2.1 基本访问2.2 条件查询判断元素是否存在2.3 数组长度与空值判断2.4 展开数组为行UNNEST三、数组更新UPDATE3.1 替换整个数组3.2 追加元素3.3 删除指定元素3.4 更新特定位置元素四、数组索引优化4.1 GIN 索引通用倒排索引4.2 GiST 索引空间/近似匹配4.3 表达式索引针对特定模式4.4 部分索引过滤空数组五、数组常见应用场景场景1商品标签系统场景2用户角色权限场景3多值过滤前端筛选六、避坑指南与限制6.1 数组 vs 关联表何时使用6.2 空数组 vs NULL6.3 下标从1开始6.4 写入性能6.5 JSONB 替代方案七、高级技巧7.1 数组合并去重7.2 数组转字符串用于展示7.3 初始化空数组在 PostgreSQL 中数组Array是一种强大而灵活的内置数据类型允许单个字段存储多个同类型元素。它广泛应用于标签系统、权限列表、多值属性等场景。然而不当使用可能导致性能下降或查询复杂化。一、数组基础1.1 创建含数组的表CREATETABLEproducts(idSERIALPRIMARYKEY,nameTEXT,tagsTEXT[],-- 文本数组scoresINTEGER[],-- 整数数组dimensionsFLOAT[3]-- 固定长度为3的浮点数组可选);注意TEXT[]表示一维文本数组TEXT[][]为二维数组。固定长度如[3]仅为文档提示PostgreSQL 不强制限制实际长度。1.2 插入数组数据-- 方式1使用花括号字面量推荐INSERTINTOproducts(name,tags,scores)VALUES(Laptop,{electronics, portable, high-end},{95, 88, 92});-- 方式2使用 ARRAY 构造器INSERTINTOproducts(name,tags,scores)VALUES(Phone,ARRAY[mobile,android],ARRAY[85,90]);字符串元素若含逗号、空格或特殊字符需用双引号包裹{ wireless, bluetooth, water-resistant }1.3 性能对比与选择建议查询模式推荐操作符是否走 GIN 索引元素存在单值val ANY(col)是包含子集col ARRAY[...]是交集非空col ARRAY[...]是精确相等col ARRAY[...]否需 B-tree 索引长度判断array_length(col,1) N否需表达式索引注意col ARRAY[...]无法使用 GIN 索引若需频繁精确匹配可考虑CREATEINDEXidx_tags_exactONproducts(tags);-- B-tree 索引1.4 使用建议优先使用 GIN 索引加速ANY,,查询。单元素存在性检查用 ANY(col)多元素用。追加用||删除用array_remove()。明确区分空数组{}和 NULL。避免在高写入场景滥用数组评估关联表方案。简单同类型列表用数组复杂结构用 JSONB。下标从1开始切片语法[start:end]。用unnest()展开分析用array_agg()聚合生成。通过合理设计与索引优化PostgreSQL 数组能显著简化数据模型并提升查询效率。但在关系型数据库中规范化仍是默认准则——仅在明确收益时才引入数组。二、数组查询SELECT2.1 基本访问-- 获取整个数组SELECTtagsFROMproducts;-- 访问第1个元素PostgreSQL 数组下标从1开始SELECTtags[1]FROMproducts;-- 访问切片第2到第3个元素SELECTscores[2:3]FROMproducts;2.2 条件查询判断元素是否存在-- 方法1使用 ANY()SELECT*FROMproductsWHEREelectronicsANY(tags);-- 方法2使用 操作符包含SELECT*FROMproductsWHEREtags ARRAY[electronics];-- 方法3使用 操作符重叠至少一个公共元素SELECT*FROMproductsWHEREtagsARRAY[electronics,gaming];推荐单元素存在性检查 → ANY(array_col)多元素子集检查 →array_col ARRAY[...]2.3 数组长度与空值判断-- 数组长度SELECTarray_length(tags,1)AStag_countFROMproducts;-- 判断是否为空数组SELECT*FROMproductsWHEREtags{};-- 判断是否为 NULL注意空数组 ≠ NULLSELECT*FROMproductsWHEREtagsISNULL;2.4 展开数组为行UNNEST-- 将每个标签展开为独立行SELECTid,name,unnest(tags)AStagFROMproducts;-- 保留空数组使用 LEFT JOIN LATERALSELECTp.id,p.name,t.tagFROMproducts pLEFTJOINLATERAL unnest(p.tags)ASt(tag)ONtrue;三、数组更新UPDATE3.1 替换整个数组UPDATEproductsSETtags{updated, tag}WHEREid1;3.2 追加元素-- 追加到末尾使用 || 操作符UPDATEproductsSETtagstags||new_tagWHEREid1;-- 追加数组UPDATEproductsSETtagstags||ARRAY[tag1,tag2]WHEREid1;注意||对数组是连接操作对字符串是拼接上下文自动区分。3.3 删除指定元素PostgreSQL无直接删除函数需通过数组构造实现-- 删除所有值为 old_tag 的元素UPDATEproductsSETtagsarray_remove(tags,old_tag)WHEREid1;-- 删除第一个匹配的元素PostgreSQL 13UPDATEproductsSETtagsarray_replace(tags,old_tag,NULL)WHEREid1;-- 然后移除 NULL需额外步骤不推荐推荐使用array_remove()移除所有匹配项。3.4 更新特定位置元素-- 将第2个标签改为 replacedUPDATEproductsSETtags[2]replacedWHEREid1ANDarray_length(tags,1)2;注意若下标超出范围会插入 NULL 填充需谨慎。四、数组索引优化数组查询若无索引将导致全表扫描。以下是关键优化策略。4.1 GIN 索引通用倒排索引适用于ANY(),,等操作。-- 创建 GIN 索引CREATEINDEXidx_products_tags_ginONproductsUSINGGIN(tags);-- 查询可走索引SELECT*FROMproductsWHEREtags ARRAY[electronics];SELECT*FROMproductsWHEREgamingANY(tags);优势支持任意元素查询、子集、交集。劣势写入开销较大索引体积大。4.2 GiST 索引空间/近似匹配较少用于普通数组多用于几何、全文搜索。4.3 表达式索引针对特定模式若常查询数组长度CREATEINDEXidx_products_tag_countONproducts((array_length(tags,1)));4.4 部分索引过滤空数组CREATEINDEXidx_products_nonempty_tagsONproductsUSINGGIN(tags)WHEREtags!{}ANDtagsISNOTNULL;五、数组常见应用场景场景1商品标签系统-- 快速查找带“sale”和“electronics”标签的商品SELECT*FROMproductsWHEREtags ARRAY[sale,electronics];-- GIN 索引高效支持场景2用户角色权限-- 用户拥有 admin 或 moderator 角色SELECT*FROMusersWHERErolesARRAY[admin,moderator];场景3多值过滤前端筛选-- 用户选择颜色 [red, blue]查找包含任一颜色的商品SELECT*FROMitemsWHEREcolorsARRAY[red,blue];六、避坑指南与限制6.1 数组 vs 关联表何时使用使用数组当数据简单、无独立属性查询模式固定如仅存在性检查写少读多使用关联表当需要独立管理元素如标签有创建时间、状态元素需被多个主记录共享需要外键约束或复杂查询反例若标签需统计使用次数、支持层级应建tags表 product_tags关联表。6.2 空数组 vs NULL{}是空数组有效值NULL表示未知/未设置查询时需明确区分WHEREtagsISNOTNULLANDtags!{}6.3 下标从1开始这是 SQL 标准但易与编程语言0起始混淆tags[1]-- 第一个元素6.4 写入性能GIN 索引在高并发写入场景下可能成为瓶颈。若写多读少评估是否值得用数组。6.5 JSONB 替代方案对于结构更复杂或多类型的多值字段可考虑JSONBALTERTABLEproductsADDCOLUMNmetadata JSONB;-- 支持更灵活查询且有专用 GIN 索引但若仅为同类型列表数组更轻量、高效。七、高级技巧7.1 数组合并去重-- 合并两个数组并去重SELECTARRAY(SELECTDISTINCTUNNEST(tags||ARRAY[new,tag])ORDERBY1);7.2 数组转字符串用于展示SELECTarray_to_string(tags,, )AStag_listFROMproducts;7.3 初始化空数组-- 避免 NULL初始化为空数组INSERTINTOproducts(name,tags)VALUES(New Product,{});