SQL Academy · 课时

数组与规范化表对比

Q: 「数组与规范化表对比」这节课中我会学到什么？

了解何时应选择数组 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

了解何时应选择数组

数组与规范化表对比 是 CoddyKit 上的免费 SQL Academy 课时。这是第 4 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

存储多个值的两种方式

当一行需要存储多个相关值时，PostgreSQL 提供了两种主要方式：在同一行中将它们存储为数组列，或者创建一个单独的子表，让每个值各占一行。

了解何时使用哪种方式，是设计高效且易于维护的数据库的一项重要技能。

规范化方法

在完全规范化的 schema 中，每一项数据都存储在自己的一行中。如果一个用户可以有多个电话号码，您可以创建一个 user_phones 表，并通过外键关联回 users。

这是经典的关系模型，也是大多数情况下的默认选择。

CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE user_phones (
  id      SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  phone   TEXT NOT NULL
);

INSERT INTO users (name) VALUES ('Alice'), ('Bob');

INSERT INTO user_phones (user_id, phone) VALUES
  (1, '+1-555-0101'),
  (1, '+1-555-0102'),
  (2, '+1-555-0200');

数组方法

PostgreSQL 的 TEXT[]（或任何类型后跟 []）允许您直接在单个列中存储多个值。不需要额外的表。

相同的电话号码数据可以按每位用户一行的方式紧凑地存储。

CREATE TABLE users_with_phones (
  id     SERIAL PRIMARY KEY,
  name   TEXT    NOT NULL,
  phones TEXT[]
);

INSERT INTO users_with_phones (name, phones) VALUES
  ('Alice', ARRAY['+1-555-0101', '+1-555-0102']),
  ('Bob',   ARRAY['+1-555-0200']);

查询数组很简单

使用 ANY 运算符或 @>（包含）运算符，可以轻松搜索数组列中的内容。您只需使用简单的 WHERE 子句，就能找到拥有特定电话号码的所有用户。

-- Find users who have a specific phone number
SELECT name
FROM users_with_phones
WHERE '+1-555-0101' = ANY(phones);

-- Or using the array-contains operator
SELECT name
FROM users_with_phones
WHERE phones @> ARRAY['+1-555-0101'];

数组更适合的场景：简单查找

在以下情况下，数组是很好的选择：

值列表总是作为一个整体一起读取（标签、标记、类别）
您从不需要针对单个元素进行连接
列表有自然上限，并且很少需要局部更新

一个经典示例是在博客文章中存储标签。您总是一次性获取所有标签，也很少需要通过复杂连接按单个标签查询文章。

CREATE TABLE posts (
  id         SERIAL PRIMARY KEY,
  title      TEXT    NOT NULL,
  tags       TEXT[]
);

INSERT INTO posts (title, tags) VALUES
  ('Intro to SQL',      ARRAY['sql', 'beginner', 'database']),
  ('Advanced Indexes',  ARRAY['sql', 'performance', 'indexes']),
  ('NoSQL Overview',    ARRAY['nosql', 'beginner']);

-- Get all posts tagged 'beginner'
SELECT title FROM posts
WHERE 'beginner' = ANY(tags);

规范化表更适合的场景：关系

在以下情况下，规范化表是更好的选择：

单个值需要拥有各自的属性（例如，电话号码有类型：家庭或工作）
您需要针对单个值进行连接
值会独立且频繁地变化
您需要通过外键实现引用完整性

-- Phone numbers need a 'type' attribute — array can't do this cleanly
CREATE TABLE user_phones (
  id      SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  phone   TEXT NOT NULL,
  type    TEXT CHECK (type IN ('home', 'work', 'mobile'))
);

INSERT INTO user_phones (user_id, phone, type) VALUES
  (1, '+1-555-0101', 'home'),
  (1, '+1-555-0102', 'work');

索引方面的差异

使用规范化表时，您可以在外键列或值列上添加标准 B 树索引。使用数组时，则需要 GIN 索引（通用倒排索引）来实现数组内的快速搜索。

GIN 索引的效果很好，但比 B 树索引更大，更新速度也更慢。

-- Index for fast array element lookups
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- Now this query uses the index efficiently
EXPLAIN SELECT title FROM posts
WHERE tags @> ARRAY['sql'];

跨行聚合：规范化表更有优势

当您需要针对单个值进行计数、分组或聚合时，规范化表要自然得多。在数组内部进行聚合需要使用 unnest()，它会先将数组展开为行——本质上是在查询时重新创建规范化结构。

-- Count posts per tag (array approach — needs unnest)
SELECT tag, COUNT(*) AS post_count
FROM posts, unnest(tags) AS tag
GROUP BY tag
ORDER BY post_count DESC;

-- With a normalized post_tags table this would be simpler:
-- SELECT tag, COUNT(*) FROM post_tags GROUP BY tag;

修改数组元素

更新或删除数组中的单个元素需要使用不太直观的语法——您必须替换整个数组，或使用 array_remove()。在规范化表中，您只需对特定行执行 DELETE 或 UPDATE。

-- Remove a single tag from an array column
UPDATE posts
SET tags = array_remove(tags, 'beginner')
WHERE id = 1;

-- Append a new tag
UPDATE posts
SET tags = array_append(tags, 'tutorial')
WHERE id = 1;

SELECT title, tags FROM posts WHERE id = 1;

强制使用有效值

在规范化表中，您可以使用外键来确保每个值都来自已知集合。数组无法引用其他表——它们不支持外键。

如果您需要确保每个元素都具备引用完整性，子表是唯一的选择。

-- Normalized: only valid category IDs allowed (FK enforced)
CREATE TABLE categories (
  id   SERIAL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_categories (
  post_id     INT REFERENCES posts(id),
  category_id INT REFERENCES categories(id),
  PRIMARY KEY (post_id, category_id)
);

-- Array: no constraint possible — any text value is accepted
-- UPDATE posts SET tags = ARRAY['totally_invalid_tag'] WHERE id = 1;

实用决策指南

当数据是简单的扁平列表、总是作为一个整体读取、每个元素没有额外属性，并且不要求引用完整性时，请使用数组（例如标签、标记、搜索关键字）。

当每个元素都有自己的属性、您需要连接或聚合单个值、需要外键，或者需要频繁更新或删除单个元素时，请使用规范化子表。

-- Summary example: tags as array (good fit)
SELECT title, tags
FROM posts
WHERE tags @> ARRAY['sql']
ORDER BY title;

-- Unnest when you need row-level processing
SELECT title, unnest(tags) AS tag
FROM posts
ORDER BY title, tag;

快速检查

以下哪种场景最适合将数据存储为 PostgreSQL 数组，而不是规范化子表？

课程回顾

在本课中，您学习了 PostgreSQL 中数组与规范化表之间的主要权衡。

数组对于标签这类扁平且作为整体读取的列表来说紧凑而方便，但它们不支持外键，使单个元素的更新变得不便，并且需要 GIN 索引才能快速搜索。
规范化表支持每个元素拥有属性、外键完整性、高效聚合以及简单的行级更新，但需要额外的连接。
正确的选择取决于您如何查询、更新和关联数据，而不仅仅取决于您如何存储数据。

常见问题解答

「数组与规范化表对比」课时是免费的吗？

是的 — 「数组与规范化表对比」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「数组与规范化表对比」这节课中我会学到什么？

了解何时应选择数组你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 4 节课，共 4 节。

「数组与规范化表对比」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy