php - 有没有一种方法可以通过 MySQL 一次调用从大量一对多表中提取数据？

Question

使用 MySQL 5.7，我有一个包含许多单值字段的 movie 表:

+--------------+--------------+
| Field        | Type         |
+--------------+--------------+
| id           | int(11)      |
| title        | varchar(255) |
| release_year | int(11)      | 
| rating       | int(11)      |
+--------------+--------------+

当我对该表调用查询时，例如SELECT * FROM movie WHERE id = 5，它将按预期仅返回一行，其中包含每一列的数据。

我有许多与该电影表具有一对多关系的表。我有 movie_subtitle、movie_language、movie_actor 等。以下是一个示例:

+--------------+--------------+
| Field        | Type         |
+--------------+--------------+
| id           | int(11)      |
| movie_id     | int(11)      |
| subtitle     | varchar(255) | 
+--------------+--------------+

我的问题是，为了获取一部电影的所有字段数据，我似乎需要进行大约 10 次 SELECT 调用。一个用于电影 表，然后一个用于每个附加附加表。

如果用户有 1,000 部电影，如果我尝试对整个集合运行更大的查询，这很快就会变成 10,000 个 SELECT 调用。

有没有办法在一次调用中实现所有输出？或者这就是它应该的样子吗？我觉得有更好的方法来做到这一点，但我对数据库关系的了解还不够，无法理解我在这里缺少的东西。

我需要将从这些调用中收到的数据传递到我的 PHP 页面，以将其放入 JSON 中。

Answer 1

我建议在 php.ini 中进行多个查询并合并数据。这是电影和类型的简单示例:

示例表:

Table: MOVIES
    id, title, release_year, rating ...etc

Table: MOVIES_GENRES (many-to-many relation to movies and genres)
    movie_id, genre_id

Table: GENRES
    id, name, description

Table: USERS_MOVIES (many-to-many relation to users and movies)
    user_id, movie_id

然后你可以这样做:

//example user id
$user_id = 5;

//query for all movies
$sql = "SELECT * FROM `movies` WHERE `id` IN (SELECT `movie_id` FROM `users_movies` WHERE `user_id`={$user_id})";

$result = /*perform query*/;

//loop over the result
$movies = array();
while($row = $result->fetch(PDO::FETCH_ASSOC)){
    //add the row to the movies array with the movie id as the key
    $movies[$row['ID']] = $row;

    //also add an empty genre array in anticipation of genre data being coalesced
    $movies['genres'] = array();
}

//get a comma separated list of all the movie ids for use in queries below
$movieIDs = implode(',', array_keys($movies));

//now query out genre based on movies, make sure to get the movie_id for coalesce
$sql = "SELECT `movie_id`, `name`, `description` FROM `genres` JOIN `movies_genres` ON `genre_id`=`genres`.`id` WHERE `movie_id` IN ({$movieIDs})";

$result = /*perform query*/;

//loop over the result
while($row = $result->fetch(PDO::FETCH_ASSOC)){
    //add the genre to this movie
    $movies[$row['movie_id']]['genres'][] = $row;
}

在整个过程中，无论有多少部电影，我都只使用两个查询。我只是生成所有电影 ID 的列表，并在后续查询中使用它。然后在 php 中将数据重新合并在一起。稍后，我可以循环 $movies 数组并使用它来构建一个表或我需要的任何内容。在流派查询中，您还可以在 movie_id 上包含对 users_movies 的联接(或子查询)，这样就不需要很大的 IN 列表，但可以使用上面的 user_id。