从MySQL的一个结果集中的多个表中获取数据

Question

在我正在开发的一个小型图书馆/研究数据库应用程序中，我有一个页面，用户可以在其中查看他们已提交的所有资源。

我有三个表用于不同的资源 - 书籍、期刊和会议。这些表看起来像这样：

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)

我想做的事情非常简单，但我似乎找不到一个简单的 SQL 命令。在堆栈溢出和谷歌上搜索“从多个表中选择数据”和其他类似的搜索返回了很多极其复杂的连接、联合等结果，数据库和操作比我在这里尝试做的要复杂得多。

是不是没有类似的东西：

Select * FROM book, journal, conference WHERE mid = 4'

这会返回一个错误：“字段中值不明确”。

谢谢！

**编辑 - 正如 martijn 指出的那样，我的很多问题都来自数据库的设计。

两种解决方案似乎是重新设计数据库并将 book、journal 和 conference 合并到一个表 resources 中

，或者，作为一种非常黑客的解决方法

执行三个单独的查询：

SELECT * FROM book where mid = 4

SELECT * FROM journal WHERE mid = 4

SELECT * FROM conference WHERE mid =4

然后使用 PHP 组合这些结果集。

Answer 1

可能，您遇到麻烦的原因是数据库设计。这三个表的定义（据我所知）是相同的。也许您应该将其设置为一个带有 ENUM 字段的表格，指定条目是书籍、期刊还是会议。
编辑：我刚刚发现这不是一个简单的选择：您仍然需要单独的表格来存储特定于书籍、期刊和会议的信息。

如果您不想这样做，则需要进一步说明您的问题。您想要三个表中 mid 等于 4 的行吗？在这种情况下，您必须使用UNION。如果您想要一行通过 mid = 4 为您提供书籍、期刊和会议，请使用 JOIN，正如 thomasrutter 已经指出的那样。

编辑：既然您准确地指出了您希望查询返回的内容，那么最好使用“mu 太短”的解决方案。添加字段是一个优雅的解决方案（为此+1）。

不过，我认为您应该考虑使用 ENUM 选项。我不知道哪些功能会被破坏，但如果您最初是从 journals 中进行选择，那么修复查询将归结为添加 WHERE kind = 'journal' 。

Answer 2

我认为您正在寻找一个 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

当然假设您尝试同时搜索所有三个表，并且它们确实具有相同的列结构。当然，在实际的应用程序中，您会显式列出列，而不是使用 * 来确保列以正确的顺序出现。另外，我还冒昧地添加了一个 item_type ，以便您可以找出表中每个条目的来源。

Answer 3

您可以通过加入来实现这一点。

MySQL 中最简单的连接类型与您已经尝试过的非常相似。你所拥有的问题是它不知道你指的是哪个“mid”。实际上，您希望将所有三个都等于一个常数。

SELECT * FROM book, journal, conference
WHERE book.mid = 4 AND journal.mid = 4 AND conference.mid = 4

这应该很容易理解。

还有其他方法可以表达相同的连接 - 例如：

SELECT * FROM
  book
  INNER JOIN journal ON journal.mid = book.mid
  INNER JOIN conference ON conference.mid = book.mid
WHERE
  book.mid = 4

... 表达完全相同的东西，但是一旦你得到比这更复杂的查询，将连接条件与其他 WHERE 子句分开可能会更好地阅读 - 一旦你'熟悉语法。

应该注意的是，当您想要合并表中的行时，需要使用联接。如果有多个会议或多本书等，并且都具有所需的“mid”值，则将为这些的每个组合返回一行。因此，如果有 4 本书、2 个会议和 2 个期刊具有相同的“mid”值，您将返回 4 x 2 x 2 = 16 行。这是因为在这种情况下，您告诉 MySQL“告诉我您可以使用这些值找到的所有书籍、会议和期刊的组合”。

我不知道这个应用程序是什么，所以我不知道这是否是你想要的。但是，如果您不希望这样做，并且您只想分别从每个表中检索行而不是组合它们，那么最好将其表示为三个单独的查询。