在 MYSQL 中有效地连接 3 个表 [英] Joining 3 tables efficiently in MYSQL

问题描述

我有 3 张这样的桌子:

I have 3 tables like this:

图像(约 10,000 行)

images (~10,000 rows)

id | place_id | filename 
---|----------|----------
 1 |     4    |  abc.jpg
 2 |     3    |  def.jpg
 3 |     4    |  ghi.jpg
 4 |     7    |  jkl.jpg

标签(约 100 行)

tags (~100 rows)

id |   name   |
---|----------|
 1 |   tagA   |
 2 |   tagB   |
 3 |   tagC   |
 4 |   tagD   |

tagsToImages(约 30,000 行)

tagsToImages (~30,000 rows)

id |  tag_id  | image_id
---|----------|----------
 1 |     1    |    4
 2 |     3    |    2
 3 |     2    |    4
 4 |     1    |    1

作为一个例子，最后一个表格显示了 id = 1 的标签与 id = 4 的图像相关联.

As an example, the last table shows that the tag with id = 1 is linked with the image with id = 4.

我有两个问题，一个很简单(我认为！)，一个更难.

I've got two questions, one pretty simple (I think!), and one more difficult.

简单的问题

给定一个 place_id，如何列出所有在该 place_id 中有图像的标签?一个例子是:

Given a place_id, how can I list all the tags that have image in that place_id? An example would be:

给定 place_id = 7，返回 tagA，tagB.

Given place_id = 7, return tagA, tagB.

难题

我想要做的是选择具有特定 place_id 的所有图像，并将每个图像与标签列表相关联，如下所示:

What I'd like to do is to select all the images with a certain place_id, and associate each image with a list of tags like so:

选择所有 place_id = 4 的，同时加入标签信息.

Select all with place_id = 4, whilst joining to tags info.

 filename |    tags    
-----------------------
  abc.jpg | tagA       
  ghi.jpg | tagA, tagB 

在 PHP 中执行多个查询会更好，还是我可以使用 MYSQL 来执行此操作?

Would this be better to do in PHP by performing multiple queries, or can I do this using MYSQL?

推荐答案

1. SELECT i.place_id, t.name as tag_name
来自图像 i
INNER JOIN tagsToImages tti ON (tti.image_id = i.id)
INNER JOIN 标签 t ON (t.id = tti.tag_id)
哪里 i.place_id = 7

SELECT i.filename, GROUP_CONCAT(t.name SEPARATOR ',') AS 标签
来自图像 i
INNER JOIN tagsToImages tti ON (tti.image_id = i.id)
INNER JOIN 标签 t ON (t.id = tti.tag_id)
哪里 i.place_id = 4GROUP BY i.filename

如果可能，最好避免多次查询.
*注意GROUP_CONCAT 的最大长度由group_concat_max_len 变量控制.如果您希望获得一长串串联字符串，则可能需要更改默认值.

It's always better to avoid multiple queries if possible.
*Be aware that the maximum length of GROUP_CONCAT is controlled by group_concat_max_len variable. If you are expecting to get a long list of concatenated strings, you might need to change the default value.

更新
如果您希望查询显示没有关联标签的图像，请将 INNER JOINs 更改为 LEFT JOINs

这篇关于在 MYSQL 中有效地连接 3 个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！