MySQL中的查找表

Question

我正在进行我的第一个PHP/MySQL工作，需要帮助。我有一张主表：

CREATE TABLE `m4l_movies` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Title` varchar(250) NOT NULL,
    `Rating` int(11) NOT NULL,
    `Genre` varchar(250) NOT NULL,
    `Actors` varchar(250) NOT NULL,
    `UserID` int(11) NOT NULL DEFAULT '1',
    PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8;

该表单接收输入，其中包含来自这些查找表的值：

CREATE TABLE `m4l_actors` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Actor` varchar(255) NOT NULL,
    PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=232 DEFAULT CHARSET=utf8;

CREATE TABLE `m4l_genre` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Genre` varchar(255) NOT NULL,
    PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8;

CREATE TABLE `m4l_movierating` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Movie_Rating` varchar(250) NOT NULL,
    PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

我已经创建了一个视图：

&

SELECT m4l_movies.ID AS ID,
  m4l_movies.Title AS Title,
  m4l_movierating.Movie_Rating AS Rating,
  m4l_movies.Actors AS Actors,
  m4l_movies.Genre AS Genre
FROM m4l_movies
  JOIN m4l_movierating ON m4l_movierating.ID = m4l_movies.Rating
  INNER JOIN m4l_genre ON m4l_movies.Genre = m4l_genre.ID
  INNER JOIN m4l_actors ON m4l_movies.Actors = m4l_actors.ID
ORDER BY m4l_movies.Title

这是我得到的输出：

----------------------------------------------------------------|
  ID      Title       Rating    Actor         Genre             |
  10      Summer      G         (10,15,25)    (45,115,123)      |
   1      About You   G-1       (63,163,405)  (3,16,51)         |
   5      Dog Years   P         (45,65,95)    (98,163,357)      |
----------------------------------------------------------------|

首先，这个视图应该返回超过200条记录。其次，我需要知道如何创建一个查找或其他方法来将名称和类型转换回它们对应的文本值。某种程度上，评分值做得很好，但我无法正确获取名称或类型。我相信这与我连接表的方式有关，但我无法弄清楚哪里出了问题。请有人帮帮我。

好的，试图按照Phil的建议跟进，我已经从电影表中移除了演员和类型，并创建了电影类型和电影演员表。

DROP TABLE IF EXISTS `m4l_movies`;
CREATE TABLE `m4l_movies` (
  `ID` int(11) NOT NULL auto_increment,
  `Title` varchar(250) NOT NULL,
  `Year` float NOT NULL,
  `Review` varchar(250) NOT NULL,
  `Rating` int(11) NOT NULL,
  `Image` varchar(250) NOT NULL,
  `Storyline` longtext NOT NULL,
  `Director` varchar(250) NOT NULL,
  `UserID` int(11) NOT NULL default '1',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


 CREATE TABLE `m4l_movie_actor` (
    movie_id INT(11),
    actor_id INT(11),
    PRIMARY KEY (movie_id, actor_id),
    FOREIGN KEY (movie_id) REFERENCES m4l_movies (ID),
    FOREIGN KEY (actor_id) REFERENCES m4l_actors (ID)
 );


CREATE TABLE `m4l_movie_genre` (
    movie_id INT(11),
    genre_id INT(11),
    PRIMARY KEY (movie_id, genre_id),
    FOREIGN KEY (movie_id) REFERENCES m4l_movies (ID),
    FOREIGN KEY (genre_id) REFERENCES m4l_genre (ID)
);


DROP TABLE IF EXISTS m4l_genre;
CREATE TABLE m4l_genre (
  ID int(11) NOT NULL auto_increment,
  Genre varchar(250) NOT NULL,
  PRIMARY KEY  (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS m4l_actors;
CREATE TABLE m4l_actors (
  ID int(11) NOT NULL auto_increment,
  Actor varchar(255) NOT NULL,
  PRIMARY KEY  (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

但是，当我尝试创建电影演员或电影流派时，我遇到了以下问题：

09:29:45
CREATE TABLE `m4l_movie_actor` (movie_id INT(11), actor_id INT(11),
PRIMARY KEY (movie_id, actor_id),
FOREIGN KEY (movie_id) REFERENCES m4l_movies (ID),
FOREIGN KEY (actor_id) REFERENCES m4l_actors (ID) )
Error Code: 1215. Cannot add foreign key constraint
0.000 sec

09:40:56 CREATE TABLE m4l_movie_genre (movie_id INT(11),genre_id INT(11),
PRIMARY KEY (movie_id, genre_id),
FOREIGN KEY (movie_id) REFERENCES m4l_movies (ID),
FOREIGN KEY (genre_id) REFERENCES m4l_genre (ID) )
Error Code: 1215. Cannot add foreign key constraint
0.016 sec

据我所知，这只会发生在数据类型不匹配时，但我认为我所有的数据类型都是INT，那么为什么会出现这个错误？

- Les

---

永远（我是说永远）不要将逗号分隔的外键存储在单个字段中。请改用连接表。 - Phil

Phil，如果你能解释一下怎么做，我完全赞成...我只是想让这个东西工作。 - Les

是的，我看了一下，现在和以前一样。创建一个“连接表”听起来不错，但我正在使用一个叫做PHPMaker的程序来创建实际的输入/编辑/查看数据库页面，那么我该如何将表单数据传递到这个表中呢？ - Les

http://forums.hkvstore.com/viewtopic.php?f=4&t=27547 - Phil

@Phil，非常感谢您昨晚（今天早上）的建议，它非常有价值...对我帮助很大。所以再次感谢您！！！ - Les

Answer 1

您似乎犯了将关系型数据以不可关联的方式存储的经典错误。

您应该使用连接表，而不是逗号分隔的值。例如...

CREATE TABLE `m4l_movies` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Title` varchar(250) NOT NULL,
    `Rating` int(11) NOT NULL,
    -- removed Actors and Genre
    `UserID` int(11) NOT NULL DEFAULT '1',
    PRIMARY KEY (`ID`)
);

CREATE TABLE `m4l_movie_actor` (
    movie_id INT(11) NOT NULL,
    actor_id INT(11) NOT NULL,
    PRIMARY KEY (movie_id, actor_id),
    FOREIGN KEY (movie_id) REFERENCES m4l_movies (ID),
    FOREIGN KEY (actor_id) REFERENCES m4l_actors (ID)
);

-- repeat for genres 

您可以在连接表上加入Actors / Genres 表，例如：

SELECT ... m4l_actors.Actor ...
FROM m4l_movies
INNER JOIN m4l_movie_actor ON m4l_movies.ID = m4l_movie_actor.movie_id
INNER JOIN m4l_actors ON m4l_movie_actor.actor_id = m4l_actors.ID

如果您想要以逗号分隔的列表形式获取结果，请查看GROUP_CONCAT()函数。

- Phil