MySQL 与 FOREIGN KEYS 的多对多关系

2022-01-20 00:00:00 many-to-many sql foreign-keys php mysql

我正在尝试在我的 MySQL 数据库中创建多对多关系.我有三张桌子:Films、Genres 和 Films_Genres.我正在使用以下代码来设置它们:

I am trying to create a many-to-many relationship in my MySQL database. I have three tables: Films, Genres and Films_Genres. I am using the following code to set them up: 

CREATE TABLE Films
(  
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),   
    Title VARCHAR(255)
),  

CREATE TABLE Genres
(  
    id INT NOT NULL AUTO_INCREMENT,   
    PRIMARY KEY(id),  
    Name VARCHAR(255)
),

CREATE TABLE Films_Genres
(
    film_id INT NOT NULL,  
    genre_id INT NOT NULL,  
    PRIMARY KEY (film_id, genre_id),  
    FOREIGN KEY (film_id) REFERENCES Films(id) ON UPDATE CASCADE,  
    FOREIGN KEY (genre_id) REFERENCES Genres(id) ON UPDATE CASCADE
)

但是，当我尝试将一些值插入到表中时:

However, when I try to insert some values into the tables with:

INSERT INTO Films (Title) VALUES ('$title')
INSERT INTO Genres (Name) VALUES ('$genre')

我可以在 Films 表中看到新电影，在 Genres 表中看到新类型，但是Films_Genres 表没有更新 - 没有新行(我正在通过 phpMyAdmin 检查).

I can see the new Film in Films table and the new Genre in Genres table but the Films_Genres table doesn't update - there are no new rows (I'm checking through phpMyAdmin).

我做错了什么?

推荐答案

你不会在 Films_Genres 表中看到任何东西，除非你明确地在其中插入一些东西.通过 PK 和 FK 实现的参照完整性不适用于为您填充表.

You won't see anything in Films_Genres table until you explicitly insert something in it. Referential integrity through PK and FK is not for populating your tables for you.

在Films_Genres中插入新记录的MySql代码，如果是对应新类型的新电影，可能看起来像

Your MySql code for inserting a new record in Films_Genres, if it's a new film which correspond to a new genre, might look like

INSERT INTO Films (Title) VALUES ('Title1');
SET @film_id = LAST_INSERT_ID();

INSERT INTO Genres (Name) VALUES ('Genre1');
SET @genre_id = LAST_INSERT_ID();

INSERT INTO Films_Genres (film_id, genre_id) VALUES(@film_id, @genre_id);

在 php 方面，要为自动增量字段获取新分配的 id，请使用 $mysqli->insert_id.

现在，如果您想制作一部新电影并同时将其分配给多种类型，您可以这样做

Now if you want to create a new film and assign it to multiple genres at once you can do

INSERT INTO Films (Title) VALUES ('Title2');
SET @film_id = LAST_INSERT_ID();
-- if you get ids of genre from your UI just use them
INSERT INTO Films_Genres (film_id, genre_id) 
SELECT @film_id, id
  FROM Genres
 WHERE id IN (2, 3, 4);

INSERT INTO Films (Title) VALUES ('Title3');
SET @film_id = LAST_INSERT_ID();
-- if you names of genres you can use them too
INSERT INTO Films_Genres (film_id, genre_id) 
SELECT @film_id, id
  FROM Genres
 WHERE Name IN ('Genre2', 'Genre4');

这里是 SQLFiddle 演示

Here is SQLFiddle demo

相关文章