MySQL查询将多个表中的媒体与另一个表中的元信息相关联 [英] MySQL query to associate media in multiple tables with meta info in another table

问题描述

我将 MySQL 关系数据库用于网站，人们可以通过上传各种媒体来创建在线故事:音频剪辑、照片、视频等.该网站将登录个人空间，人们可以在其中创建、查看并编辑故事.对于这个问题，假设数据库有 4 个表:memorybox(关于故事的元信息，即:标题、日期和描述)、音频、照片和视频(后三张表将所有媒体连接到 memorybox 中的一个故事table -- 单个媒体文件通过带有 memorybox ID 的列链接到一个故事.

I'm using a relational MySQL database for a website where people can create online stories by uploading various media: audio clips, photos, videos, etc. The site will feature a login to a personal space where people can create, view and edit the stories. For this question, let's say the database has 4 tables: memorybox (with meta info about the story, i.e.: title, date and description), audio, photos and video (the latter three have all the media connected to one story in the memorybox table -- individual media files are linked to a story by a column with the memorybox ID).

我正在使用 PHP 选择与故事相关的所有媒体和故事的元数据，然后导出到 XML 文件.从 XML 中，我将使用 jquery 首先显示故事(某种图标，带有故事标题)，然后，当单击故事图标时，故事视图中的所有媒体.

I'm using PHP to select all the media associated with a story, and the story's metadata, and exporting to an XML file. From the XML I'll use jquery to display, first, the stories (some sort of icon, with the story title) and then, when the story icon is clicked, all of the media in a story view.

我想要的是将所有媒体文件仅与一个故事(及其元数据)相关联.我的问题是我不能完全得到我需要的 XML 结果，并且不确定问题是与查询还是与数据库结构有关.我试图选择所有 IDS 匹配的地方，但这为我提供了每个媒体文件的单独条目的长列表.我还尝试了 LEFT JOIN 将所有内容与 memorybox ID 相关联，但我仍然为一个 ID 获得多个条目.我试过 GROUP BY 但这只给了我一行媒体数据.

What I want is to associate all media files with only one story (and its metadata). My problem is that I can't quite get the XML results I need, and am uncertain if the issue is with the query or with the database structure. I have tried to select all where the IDS match, but that gives me a long list of separate entries for each of the media files. I've also tried a LEFT JOIN to associate everything with a memorybox ID, but I still get multiple entries for one ID. I tried GROUP BY but that gave me only one row of media data.

是否有查询可以做到这一点，或者我是否需要重新考虑我的数据库结构?

Is there a query that can do this or do I need to rethink my database structure?

谢谢，谢丽尔

推荐答案

从您的问题来看，您还没有完全了解关系数据库.您是否意识到过程代码和面向对象编程之间的区别?走向 RealtionalDB 是一个类似的步骤.

judging from your question you haven't wrapped your head around relational databases yet. Do you realize the difference between procedural code and object oriented programming? Going RealtionalDB is a step similar in magnitude.

始终考虑数据集，即使该集合仅包含一条记录.一个表是一个数据集，一个查询结果是另一个，一个视图是一个，一个连接是两个结合的.

至于你的问题:我了解一个故事由 memorybox 表中的一条记录和任何媒体表中的一条或多条记录组成.

As for your question: I understand a story is composed of one record in the memorybox table and one or more records in any of the media tables.

为此，您的表结构非常好.我想table memoryboxes(id int, metadata varchar),table fotos(memorybox_id int, thefoto clob),table audios(memorybox_id int, theaudio club),表格视频(memorybox_id int, thevideo clob).

For this, your table structure is pretty much ok. I imagine table memoryboxes(id int, metadata varchar), table fotos(memorybox_id int, thefoto clob), table audios(memorybox_id int, theaudio club), table videos(memorybox_id int, thevideo clob).

要获得完整故事"，您最好根据 memorybox_id 从所有这些表中进行选择.这意味着 4 个查询(在一个事务中)一个接一个.从这些结果集构建 xml.

To get 'the full story' you best select from all these tables based on the memorybox_id. That means 4 queries (in one transaction for neatness) after each other. Build the xml from those resultsets.

关于连接:(左)联接的行为应与您描述的完全相同.它结合了基于前提的数据集(在您的情况下 ids 相等).左连接允许生成左侧"数据集中的记录，即使右侧"数据集中没有匹配的记录.想一想；这将为您提供与您的谓词匹配的数据集的所有排列.正是您所描述的.

As for joins: A (left) join should behave exactly as you describe. It combines datasets based on a premise (ids being equal in your case). A left join allows records in the dataset 'on the left' to be produced even if there is no matching record in the dataset on 'the right'. Think about it; this will give you all permutations of your datasets that match your predicate. Exactly what you describe.

至于分组依据:一个 group by(我猜是通过 id)将滚动"一个数据集，你会注意到你需要在 group-by 子句或所谓的聚合函数中拥有该 group-by 查询中的列(count, min, max, ...)，这意味着它们要么参与了结果分组(想想表中的复合键)，要么它们的最终值将由基础数据集组中的所有数据组成.

As for group-bys: A group by (by id I guess) will 'roll' a dataset up and as you will have noticed you were required to have the columns in that group-by query either in the group-by clause or in a so called aggregate function (count, min, max, ...), meaning either they participated in the resulting grouping (think composite keys in tables) or their final value would be composed of all the data in the underlying dataset group.

长话短说:您的数据库结构很好，使用 4 个单独的查询(在一个事务中)来获取数据.

Long story short: your db structure is fine, use 4 separate queries (in a transaction) to get the data out.

后续步骤:稍后，当您想编写一个列出故事中相关媒体数量的查询时，请尝试阅读更多有关连接和分组的内容，例如:

Next steps: Try to read a bit more about joins and group-by later, when you want to write a query that lists the amount of associated media in a story for instance:

select 
    m.metadata, 
    count(f.id) as fotocount, 
    count(a.id) as audiocount, 
    count(v.id) as videocount 
from 
    memoryboxes m 
    left join fotos f on f.memorybox_id=m.id
    left join audios a on a.memorybox_id=m.id
    left join videos v on v.memorybox_id=m.id
group by m.metadata

这篇关于MySQL查询将多个表中的媒体与另一个表中的元信息相关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！