如何在MySQL中创建具有N:M关系的表？

Question

假设我有这两个表： Store和 Product。我想让我的商店拥有一个产品列表。我该怎么做？

create table store(
id int unsigned not null auto_increment,
store_name varchar(30) not null,
product_list_FK int unsigned not null,
primary key(id)
);

create table product(
id int unsigned not null auto_increment,
product_name varchar(30) not null,
price float not null,
primary key(id)
);

我开始做类似的事情，但不知道如何结束，你们能帮我吗？

- flpn

---

创建第三张表，用于按店铺ID存储产品ID。 - artm

产品是否应该是商店的子级，还是同一产品可以出现在多个商店中？如果同一产品可以出现在多个商店中，则需要寻找多对多关系，而不是一对多关系。 - Bryan Newman

Answer 1

多对一 (产品只能属于一个商店)

create table store(
    id int unsigned not null auto_increment,
    store_name varchar(30) not null,
    primary key(id)
);

Query OK, 0 rows affected (0.02 sec)

create table product(
    id int unsigned not null auto_increment,
    store_id int unsigned not null,
    product_name varchar(30) not null,
    price float not null,
    primary key(id),
    constraint product_store foreign key (store_id) references store(id)
);

Query OK, 0 rows affected (0.02 sec)

多对多（产品可以存在于多个商店）

create table store(
    id int unsigned not null auto_increment,
    store_name varchar(30) not null,
    primary key(id)
);

Query OK, 0 rows affected (0.04 sec)

create table product(
    id int unsigned not null auto_increment,
    store_id int unsigned not null,
    product_name varchar(30) not null,
    price float not null,
    primary key(id)
);

Query OK, 0 rows affected (0.01 sec)

create table product_store (
    product_id int unsigned not null,
    store_id int unsigned not null,
    CONSTRAINT product_store_store foreign key (store_id) references store(id),
    CONSTRAINT product_store_product foreign key (product_id) references product(id),
    CONSTRAINT product_store_unique UNIQUE (product_id, store_id)
)

Query OK, 0 rows affected (0.02 sec)

- Bryan Newman

---

为什么表格“products”需要一个“store_Id”列？如果我们已经通过“products_store”将产品与商店连接起来了，那还有什么必要呢？ - Rarowcun

说得好，我不确定5年前写这个答案时我在想什么 :D - Bryan Newman

Answer 2

这是一种n-m的关系。一个商店可能有多个产品，而一个产品也可能在多个商店销售。

您可以通过一个单独的表来实现这一点：

create table StoreProducts (
    StoreProductId int auto_increment primary key,
    StoreId int,
    ProductId int,
    constraint fk_storeproducts_store foreign key (StoreId) references Stores(StoreId),
    constraint fk_storeproducts_product foreign key (ProductId) references Products(ProductId)
);

这被称为联接表。您可以在表中维护其他信息，例如“不再存货”标志或“第一批次日期”。

- Gordon Linoff