php MySQL 与 FOREIGN KEYS 的多对多关系
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17459762/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL many-to-many relationship with FOREIGN KEYS
提问by Sharkz
I am trying to create a many-to-many relationship in my MySQL database. I have three tables:
Films, Genresand Films_Genres. I am using the following code to set them up:
我正在尝试在我的 MySQL 数据库中创建多对多关系。我有三个表：
Films, Genres和Films_Genres。我正在使用以下代码来设置它们：
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 Filmstable and the new Genre in Genrestable but
the Films_Genrestable doesn't update - there are no new rows (I'm checking through phpMyAdmin).
我可以看到Films表中的新电影和表中的新流派，Genres但Films_Genres表没有更新 - 没有新行（我正在通过 phpMyAdmin 检查）。
What am I doing wrong?
我究竟做错了什么？
回答by peterm
You won't see anything in Films_Genrestable until you explicitly insert something in it.
Referential integrity through PK and FK is not for populating your tables for you.
在Films_Genres您明确插入某些内容之前，您不会在表格中看到任何内容。通过 PK 和 FK 实现的参照完整性不是为了为您填充表。
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
您用于在 中插入新记录的 MySql 代码Films_Genres，如果它是与新类型相对应的新电影，可能看起来像
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.
在 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');
Here is SQLFiddledemo
这是SQLFiddle演示
回答by diy
Basically, you should insert a row into the link table as well to 'link' a film and a genre you've just inserted.Mysql engine doesn't do that for you automatically(it simply doesn't know that those 2 records should be somehow related) - this is to be done on application side or manually
基本上，您还应该在链接表中插入一行以“链接”您刚刚插入的电影和类型。Mysql 引擎不会自动为您执行此操作（它只是不知道这 2 条记录应该以某种方式相关） - 这将在应用程序端或手动完成
INSERT INTO Films_Genres (film_id,genre_id) VALUES (1,1)
回答by silkfire
You have to fill the many-to-many table yourself mate, that's how mysql works, there's no automatic guess work performed here unfortunately.
您必须自己填写多对多表，这就是 mysql 的工作方式，不幸的是，这里没有执行自动猜测工作。
