sql - 引用不同表行的列中的 ID 数组

Question

假设我有一个包含不同类别的表..然后是另一个表，其中一列必须引用第一个中多个类别的数组。

这叫什么，最好的方法是什么？

Postgres 方言 :)

Category Id | Category Name
1           | Category 1
2           | Category 2

Product Id | Product Name | Categories
1          | Product 1    | 1,2

Answer 1

它被称为整型数组，int[] (或 int8[] 如果您需要 8 字节整数)。

create table category (
  id serial primary key,
  name text not null
);

create table product (
  id bigserial primary key,
  name test not null,
  category_ids int[] not null
);

您可以使用这种方法，甚至可以在 product.category_ids 上添加 GIN 索引加快查找速度 select * from product where '{2}'::int[] <@ category_ids; -- 但这里明显的缺点是 Postgres 将无法确保引用完整性 -- 您不能为 int[] 定义外键.

或者，您可以使用 jsonb数据类型而不是 int[]并在该列上添加 GIN 索引以加快搜索速度。

如果你想拥有 FK(这是非常好的和正确的愿望)，只需遵循传统的方法，EAV:

create table category (
  id serial primary key,
  name text not null
);

create table product (
  id bigserial primary key,
  name test not null
);

create table product2category (
  product_id int8 not null references product(id),
  category_id int not null references category(id),
  primary key (product_id, category_id)
);

有许多文章发现了 EAV 与 int[] 的优缺点。 (又名“intarray”)或jsonb方法，这是其中之一:http://coussej.github.io/2016/01/14/Replacing-EAV-with-JSONB-in-PostgreSQL/ -- 我建议用谷歌搜索该主题并进行学习，这将有助于决定什么更适合您的情况。