从 MySQL 中的一个结果集中的多个表中获取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7803746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get data from multiple tables in one result set in MySQL
提问by sanukcm
In a small library/research database application I am working on, I have a page where a user can view all of the resources they have submitted.
在我正在开发的一个小型图书馆/研究数据库应用程序中，我有一个页面，用户可以在其中查看他们提交的所有资源。
I have three tables for different resources - Books, Journals and Conferences. The tables look like this:
我有三个不同资源的表格 - 书籍、期刊和会议。表格如下所示：
mysql> desc book;
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| author | varchar(255) | YES | | NULL | |
| publishedyear | char(4) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| edition | int(3) unsigned | YES | | NULL | |
| publisher | varchar(100) | YES | | NULL | |
| place | varchar(50) | YES | | NULL | |
| image | varchar(100) | YES | | NULL | |
| isbn | varchar(20) | YES | | NULL | |
| callnumber | varchar(30) | YES | | NULL | |
| status | int(1) unsigned | YES | | NULL | |
| abstract | text | YES | | NULL | |
| toc | text | YES | | NULL | |
| problems | text | YES | | NULL | |
| futurework | text | YES | | NULL | |
| registered | datetime | YES | | NULL | |
| mid | int(10) unsigned | NO | MUL | NULL | |
| iid | int(10) unsigned | NO | MUL | NULL | |
+---------------+------------------+------+-----+---------+----------------+
18 rows in set (0.00 sec)
mysql> desc journal;
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| author | varchar(255) | YES | | NULL | |
| publishedyear | char(4) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| journaltitle | varchar(255) | YES | | NULL | |
| volume | int(3) unsigned | YES | | NULL | |
| issue | int(5) unsigned | YES | | NULL | |
| pagenumbers | varchar(15) | YES | | NULL | |
| image | varchar(100) | YES | | NULL | |
| isbn | varchar(20) | YES | | NULL | |
| callnumber | varchar(30) | YES | | NULL | |
| status | int(1) unsigned | YES | | NULL | |
| abstract | text | YES | | NULL | |
| toc | text | YES | | NULL | |
| problems | text | YES | | NULL | |
| futurework | text | YES | | NULL | |
| registered | datetime | YES | | NULL | |
| mid | int(10) unsigned | NO | MUL | NULL | |
| iid | int(10) unsigned | NO | MUL | NULL | |
+---------------+------------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)
mysql> desc conference;
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| author | varchar(255) | YES | | NULL | |
| publishedyear | char(4) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| conferencename | varchar(255) | YES | | NULL | |
| location | varchar(100) | YES | | NULL | |
| conferencedate | varchar(15) | YES | | NULL | |
| pagenumbers | varchar(15) | YES | | NULL | |
| image | varchar(100) | YES | | NULL | |
| isbn | varchar(20) | YES | | NULL | |
| callnumber | varchar(30) | YES | | NULL | |
| status | int(1) unsigned | YES | | NULL | |
| abstract | text | YES | | NULL | |
| toc | text | YES | | NULL | |
| problems | text | YES | | NULL | |
| futurework | text | YES | | NULL | |
| registered | datetime | YES | | NULL | |
| mid | int(10) unsigned | NO | MUL | NULL | |
| iid | int(10) unsigned | NO | MUL | NULL | |
+----------------+------------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)
What I am trying to do is very simple, but I can't seem to find a simple SQL command for it. Searching on stack overflow and google for "selecting data from multiple tables" and other similar searches returned a lot of results for ridiculously complex joins, unions, etc for databases and operations that are much more complicated than what I am trying to do here.
我想要做的很简单，但我似乎找不到一个简单的 SQL 命令。在堆栈溢出和谷歌上搜索“从多个表中选择数据”和其他类似的搜索返回了很多结果，这些结果是非常复杂的联接、联合等，这些数据库和操作比我在这里尝试做的要复杂得多。
Is there not something like:
有没有类似的东西：
Select * FROM book, journal, conference WHERE mid = 4'
This returns an error: "field mid is ambiguous".
这将返回一个错误：“field mid 不明确”。
Thanks!
谢谢！
**EDIT - As martijn pointed out, a lot of my problem is coming from the design of my database.
**编辑 - 正如 martijn 指出的，我的很多问题都来自我的数据库设计。
Two solutions seem to be to either redesign the database and combine book, journaland conferenceinto one table resources
两种解决方案似乎是要么重新设计数据库，并结合book，journal并conference为一个表resources
or, as a very hackish workaround
或者，作为一个非常黑客的解决方法
To do three separate queries:
要执行三个单独的查询：
SELECT * FROM book where mid = 4
SELECT * FROM journal WHERE mid = 4
SELECT * FROM conference WHERE mid =4
and then combine those result sets using PHP.
然后使用 PHP 组合这些结果集。
回答by mu is too short
I think you're looking for a UNION:
我认为您正在寻找 UNION：
select 'book' as item_type, * from book where mid = 4
union all
select 'journal' as item_type, * from journal where mid = 4
union all
select 'conference' as item_type, * from conference where mid = 4
Assuming of course that you're trying to search all three tables at once and they really do have the same column structure. In a real application you would, of course, explicitly list the columns rather than using *to ensure that the columns came out in the right order. Also, I've taken the liberty of adding an item_typeso that you can figure out where table each entry came from.
当然，假设您尝试一次搜索所有三个表，并且它们确实具有相同的列结构。在实际应用程序中，您当然会明确列出列，而不是*用来确保列以正确的顺序出现。另外，我冒昧地添加了一个，item_type以便您可以找出每个条目来自何处的表。
回答by Martijn
Probably, the reason you're having trouble because of the database design. The definitions of the three tables are (as far as I can see) identical. Maybe you should make it one table with an ENUMfield specifying if the entry is a book, journal or conference.
EDIT: I've just seen this is not an easy option: you'll still need separate tables for the info that is specific to books, journals and conferences.
可能是因为数据库设计而导致您遇到问题。这三个表的定义（据我所知）是相同的。也许您应该将其制作成一张表格，其中有一个ENUM字段指定条目是书籍、期刊还是会议。编辑：我刚刚看到这不是一个简单的选择：您仍然需要单独的表格来获取特定于书籍、期刊和会议的信息。
If you do not want to do this, you need to specify you question a little more. Do you want to have the rows from the three tables which have midequal to 4? In this case, you have to use UNION. If you want one line giving you the book, journal, and conference with mid = 4, use a JOIN, as thomasrutter pointed out already.
如果您不想这样做，则需要再详细说明您的问题。你想要三个表中的行mid等于 4 吗？在这种情况下，您必须使用UNION. 如果你想要一行给你的书、期刊和会议mid = 4，请使用JOIN，正如 thomasrutter 已经指出的那样。
EDIT: now that you point out exactly what you want the query to return, it is probably best to use 'mu is too short''s solution. Adding the field is an elegant solution (+1 for that).
编辑：现在您准确指出了您希望查询返回的内容，最好使用“mu 太短”的解决方案。添加该字段是一个优雅的解决方案（+1）。
Still, I think you should consider using the ENUMoption. I do not know what functionality would break, but fixing queries would boil down to adding a WHERE kind = 'journal'if you're selecting from jounalsoriginally.
不过，我认为您应该考虑使用该ENUM选项。我不知道什么功能会中断，但WHERE kind = 'journal'如果您jounals最初选择，修复查询将归结为添加一个。
回答by thomasrutter
You can achieve this with a join.
您可以通过join实现这一点。
The simplest type of join in MySQL is very similar to what you tried already. The problem with what you had is that it didn't know which "mid" you were referring to. In reality you want to join on all three of them being equal to a constant.
MySQL 中最简单的连接类型与您已经尝试过的非常相似。你所拥有的问题是它不知道你指的是哪个“中”。实际上，您希望加入所有三个等于一个常数。
SELECT * FROM book, journal, conference
WHERE book.mid = 4 AND journal.mid = 4 AND conference.mid = 4
Which should be pretty easy to understand.
这应该很容易理解。
There are other ways to express the same join - for example:
还有其他方法可以表达相同的连接 - 例如：
SELECT * FROM
book
INNER JOIN journal ON journal.mid = book.mid
INNER JOIN conference ON conference.mid = book.mid
WHERE
book.mid = 4
... which expresses exactly the same thing, but once you get more complex queries than this, separating the join conditions from other WHERE clauses may make for better reading - once you're familiar with the syntax.
... 表达完全相同的事情，但是一旦您获得比这更复杂的查询，将连接条件与其他 WHERE 子句分开可能会更好地阅读 - 一旦您熟悉语法。
It should be noted that joins are for when you want to combinerows from the tables. If there are multiple conferences, or multiple books, etc, all with the desired "mid" value, then a row will be returned for every combinationof these. So if there's 4 books, 2 conferences and 2 journals with the same "mid" value, you'll get 4 x 2 x 2 = 16 rows returned. This is because in this instance you're telling MySQL "tell me every combination of book, conference and journal you can find with these values".
应该注意的是，当您想要组合表中的行时，联接是适用的。如果有多个会议或多本书等，都具有所需的“mid”值，则将为这些的每个组合返回一行。因此，如果有 4 本书、2 个会议和 2 个期刊具有相同的“mid”值，您将得到 4 x 2 x 2 = 16 行返回。这是因为在这种情况下，您要告诉 MySQL“告诉我您可以使用这些值找到的书籍、会议和期刊的每种组合”。
I don't know what the application is, so I don't know if this is what you want. If you don't want this, however, and you're just interested in retrieving the rows from each table separately and not combining them, then it's probably better expressed as three separate queries.
我不知道应用程序是什么，所以我不知道这是不是你想要的。但是，如果您不想要这样，并且您只想分别从每个表中检索行而不是组合它们，那么最好将其表示为三个单独的查询。
