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

我已经创建了一个 View :

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

首先，此 View 应返回超过 200 条记录。其次，我需要知道如何创建一个lookup 或一些其他方法来将 NAME 和 GENRE 转换回它们相应的文本值。某些 RATING 值如何正确执行，但我无法让 NAME 或 GENRE 正确执行。我确定这与我加入表格的方式有关，但我无法弄清楚我哪里出错了。请有人帮助我。

好的，按照 Phil 的建议，我已经从电影故事中删除了 Actor 、流派并创建了 movies_genre 和 movies_actors

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;

但是当我尝试创建 movie_actor 或 movie_genre 时，我得到了

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 数据类型，所以为什么我会收到此错误？

Answer 1

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

你应该使用 junction tables而不是逗号分隔值。例如……

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 

然后您可以加入联结表和 Actor/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()