MySQL - 从具有相同结构但不同数据的多个表中选择数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/409705/
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
MySQL - Selecting data from multiple tables all with same structure but different data
提问by Jayrox
Ok, here is my dilemma I have a database set up with about 5 tables all with the exact same data structure. The data is separated in this manner for localization purposes and to split up a total of about 4.5 million records.
好的，这是我的困境我有一个数据库，其中设置了大约 5 个表，所有表都具有完全相同的数据结构。以这种方式分离数据是为了本地化目的，并拆分总共约 450 万条记录。
A majority of the time only one table is needed and all is well. However, sometimes data is needed from 2 or more of the tables and it needs to be sorted by a user defined column. This is where I am having problems.
大多数时候只需要一张桌子，一切都很好。但是，有时需要来自 2 个或更多表的数据，并且需要按用户定义的列对其进行排序。这是我遇到问题的地方。
data columns:
数据列：
id, band_name, song_name, album_name, genre
MySQL statment:
MySQL语句：
SELECT * from us_music, de_music where `genre` = 'punk'
MySQL spits out this error:
MySQL 吐出这个错误：
#1052 - Column 'genre' in where clause is ambiguous
Obviously, I am doing this wrong. Anyone care to shed some light on this for me?
显然，我这样做是错误的。有人愿意为我解释一下吗？
回答by Mihai Limb??an
回答by Ned Batchelder
It sounds like you'd be happer with a single table. The five having the same schema, and sometimes needing to be presented as if they came from one table point to putting it all in one table.
听起来你会更高兴有一张桌子。这五个具有相同的模式，有时需要呈现为好像它们来自一张桌子指向将它们全部放在一张桌子上。
Add a new column which can be used to distinguish among the five languages (I'm assuming it's language that is different among the tables since you said it was for localization). Don't worry about having 4.5 million records. Any real database can handle that size no problem. Add the correct indexes, and you'll have no trouble dealing with them as a single table.
添加一个可用于区分五种语言的新列（我假设表中的语言不同，因为您说它用于本地化）。不要担心有 450 万条记录。任何真正的数据库都可以处理这种大小的问题。添加正确的索引，您就可以轻松地将它们作为单个表处理。
回答by Moo
Any of the above answers are valid, or an alternative way is to expand the table name to include the database name as well - eg:
上述任何答案都是有效的，或者另一种方法是扩展表名以包括数据库名称 - 例如：
SELECT * from us_music, de_music where `us_music.genre` = 'punk' AND `de_music.genre` = 'punk'
回答by Moo
The column is ambiguous because it appears in both tables you would need to specify the where (or sort) field fully such as us_music.genre or de_music.genre but you'd usually specify two tables if you were then going to join them together in some fashion. The structure your dealing with is occasionally referred to as a partitioned table although it's usually done to separate the dataset into distinct files as well rather than to just split the dataset arbitrarily. If you're in charge of the database structure and there's no good reason to partition the data then I'd build one big table with an extra "origin" field that contains a country code but you're probably doing it for legitimate performance reason. Either use a union to join the tables you're interested in http://dev.mysql.com/doc/refman/5.0/en/union.htmlor by using the Merge database engine http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html.
该列不明确，因为它出现在两个表中，您需要完全指定 where（或排序）字段，例如 us_music.genre 或 de_music.genre，但如果您打算将它们连接在一起，通常会指定两个表一些时尚。您处理的结构有时被称为分区表，尽管通常这样做是为了将数据集分成不同的文件，而不是随意拆分数据集。如果您负责数据库结构并且没有充分的理由对数据进行分区，那么我会构建一个包含国家代码的额外“来源”字段的大表，但您这样做可能是出于正当的性能原因. 要么使用联合来加入您对http://dev.mysql感兴趣的表。或使用合并数据库引擎http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html。
回答by staticsan
Your original attempt to span both tables creates an implicit JOIN. This is frowned upon by most experienced SQL programmers because it separates the tables to be combined with the condition of how.
您最初尝试跨越两个表会创建一个隐式 JOIN。这是大多数有经验的 SQL 程序员不赞成的，因为它将表与如何组合的条件分开。
The UNIONis a good solution for the tables as they are, but there should be no reason they can't be put into the one table with decent indexing. I've seen adding the correct index to a large table increase query speed by three orders of magnitude.
UNION对于这些表来说，这是一个很好的解决方案，但是没有理由不能将它们放入一个具有良好索引的表中。我已经看到向大表添加正确的索引可以将查询速度提高三个数量级。
回答by mehdi mohamadi
The unionstatement cause a deal time in huge data. It is good to perform the select in 2 steps:
该union语句导致大量数据的交易时间。最好分两步执行选择：
- select the id
- then select the main table with it
- 选择身
- 然后用它选择主表
