产品指南
表设计
数组

数组

数组是一种将多个相同类型值存储在单个数据库字段中的数据类型，非常适合存储标签、分类、多值属性等场景。

支持的数组类型:

  • 基本类型数组：INT[], TEXT[], VARCHAR[], FLOAT[], BOOLEAN[] 等
  • 多维数组

数组的基本操作

创建包含数组的表

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[],  -- 文本数组
    prices FLOAT[],  -- 数值数组
    dimensions FLOAT[][],  -- 二维数组
    created_at TIMESTAMP
) USING HYBRID;

插入数组数据

-- 使用 ARRAY 构造函数
INSERT INTO products (name, tags, prices)
VALUES ('Laptop', ARRAY['electronics', 'computers'], ARRAY[999.99, 899.99]);
 
-- 使用数组字面量语法
INSERT INTO products (name, tags, prices)
VALUES ('Phone', '{"mobile", "electronics"}', '{599.99, 499.99}');
 
-- 插入多维数组
INSERT INTO products (name, dimensions)
VALUES ('Box', '{{10.5, 20.3}, {15.2, 25.7}}');

查询数组数据

-- 查询整个数组
SELECT name, tags FROM products;
 
-- 访问数组元素（索引从1开始）
SELECT name, tags[1] AS primary_tag FROM products;
 
-- 使用 unnest 展开数组
SELECT name, unnest(tags) AS tag FROM products;
 
-- 查询数组长度
SELECT name, array_length(tags, 1) AS tag_count FROM products;
 
-- 数组去重
SELECT name, ARRAY_AGG(DISTINCT tag)
FROM (SELECT name, UNNEST(tags) AS tag FROM products) a
GROUP BY name;

数组的更新

基本更新

-- 更新整个数组
UPDATE prodcts
SET tags = ARRAY['new', 'tags']
WHERE id = 1;
 
 
-- 连接数组
UPDATE products
SET tags = tags || ARRAY['additional', 'tags']
WHERE id = 1;
 
 
-- 条件更新数组元素
UPDATE products
SET tags = (
    SELECT array_agg(
        CASE WHEN elem = 'electronics' THEN 'digital' ELSE elem END
    )
    FROM unnest(tags) AS elem
)
WHERE id = 1;

数组函数操作

-- 添加元素
UPDATE products
SET tags = array_append(tags, 'new_tag')
WHERE id = 1;
 
-- 前置元素
UPDATE products
SET tags = array_prepend('first_tag', tags)
WHERE id = 1;
 
-- 移除元素
UPDATE products
SET tags = array_remove(tags, 'old_tag')
WHERE id = 1;
 
-- 替换元素
UPDATE products
SET tags = array_replace(tags, 'old', 'new')
WHERE id = 1;

数组的查询

数组操作符

-- 包含（所有元素）
SELECT * FROM products WHERE tags @> ARRAY['electronics'];
 
-- 包含（任意元素）
SELECT * FROM products WHERE tags && ARRAY['electronics', 'mobile'];
 
-- 相等
SELECT * FROM products WHERE tags = ARRAY['electronics', 'computers'];
 
-- 重叠（有共同元素）
SELECT * FROM products WHERE tags && ARRAY['mobile', 'tablet'];

数组函数查询

-- 查找元素位置
SELECT name, array_position(tags, 'electronics') AS pos
FROM products;
 
-- 获取数组维度
SELECT name, array_dims(dimensions)
FROM products;

数组实践

数组 GIN 索引

如果是列存表，可以对经常查询的数组列创建 GIN 索引

CREATE INDEX idx_products_tags ON products USING SPLIT_GIN(tags);

默认值设置

ALTER TABLE products ALTER COLUMN tags SET DEFAULT ARRAY[]::TEXT[];

NULL 值处理

-- 将NULL转为空数组
SELECT name, COALESCE(tags, ARRAY[]::TEXT[]) FROM products;

JSON 互操作

-- 数组转JSON
SELECT name, to_json(tags) AS tags_json FROM products;
 
-- JSON转数组
UPDATE products SET tags = ARRAY(SELECT json_array_elements_text('[1,2,3]'));

常用数组函数速查

函数描述示例
array_append添加元素到末尾array_append(ARRAY[1,2], 3) → {1,2,3}
array_prepend添加元素到开头array_prepend(1, ARRAY[2,3]) → {1,2,3}
array_cat连接两个数组array_cat(ARRAY[1,2], ARRAY[3,4]) → {1,2,3,4}
array_remove移除所有匹配元素array_remove(ARRAY[1,2,1], 1) → {2}
array_replace替换元素array_replace(ARRAY[1,2,3], 2, 5) → {1,5,3}
array_length获取数组长度array_length(ARRAY[1,2,3], 1) → 3
array_position查找元素位置array_position(ARRAY['a','b'], 'b') → 2
unnest展开数组为行unnest(ARRAY[1,2,3]) → 三行(1,2,3)
数据类型约束