MySQL与FOREIGN KEYS的多对多关系 [英] MySQL many-to-many relationship with FOREIGN KEYS

问题描述

我试图在我的MySQL数据库中创建一个多对多的关系。我有三个表：
电影，流派和 Films_Genres 。我使用下面的代码来设置它们：
$ b $ pre $ code CREATE TABLE电影
（
id INT NOT NULL AUTO_INCREMENT，
PRIMARY KEY（id），
Title VARCHAR（255）
），

CREATE TABLE流派
（
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）参考流派（id）ON UPDATE CASCADE
）

然而，当我尝试插入一些值到表中：

  INSERT INTO Films（标题）VALUES（'$ title'）
 INSERT INTO流派（名称）VALUES（'$ genre'）
  

我可以在电影表和新的流派在 Genres 表，但
Films_Genres 表不更新 - 没有新行通过phpMyAdmin）。

我做错了什么？

解决方案

你不会在 Films_Genres 表中看到任何东西，直到你明确地插入了一些东西。
通过PK和FK进行参照完整性不是为了填充你的表。

MySql代码用于在中插入新记录Films_Genres ，如果这是一个新的电影，对应于一个新的流派，可能看起来像

  INSERT INTO Films（Title）VALUES（'Title1'）; 
 SET @film_id = LAST_INSERT_ID（）; 
 
 INSERT INTO流派（名称）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 的

现在，如果您想创建一部新电影并将其分配给多个流派，您可以执行

  INSERT INTO Films（标题）VALUES（'Title2'）; 
 SET @film_id = LAST_INSERT_ID（）; 
  - 如果您从您的用户界面中获得流派的ID，只需使用它们
 INSERT INTO Films_Genres（film_id，genre_id）
 SELECT @film_id，id 
 FROM流派
 WHERE id IN（2,3,4）; 
 
 INSERT INTO电影（标题）VALUES（'Title3'）; 
 SET @film_id = LAST_INSERT_ID（）; 
  - 如果您也可以使用它们的类型名称
 INSERT INTO Films_Genres（film_id，genre_id）
 SELECT @film_id，id 
 FROM流派
 WHERE名称（'Genre2'，'Genre4'）; 
  

这是 SQLFiddle demo

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')

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).

What am I doing wrong?

解决方案

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.

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);

On php side to get a newly assigned id for an autoincremented field use $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');

Here is SQLFiddle demo

这篇关于MySQL与FOREIGN KEYS的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！