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.
大多数时候只需要一张表，而且一切都很好。但是，有时需要从两个或更多的表中获取数据，并且需要根据用户定义的列进行排序。这就是我的问题所在。
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?
显然，我做错了。有人愿意帮我解释一下吗?
6 个解决方案
#1
168
I think you're looking for the UNION clause, a la
我想你在找工会条款，洛杉矶。
(SELECT * from us_music where `genre` = 'punk')
UNION
(SELECT * from de_music where `genre` = 'punk')
#2
19
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万的记录。任何真正的数据库都可以处理这种大小的问题。添加正确的索引，您就可以将它们作为一个表来处理了。
#3
5
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'
#4
3
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.html or by using the Merge database engine http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html.
该列是不明确的，因为它出现在两个表中，您需要指定where(或排序)字段，如us_music。类型或de_music。类型，但是如果你想以某种方式将它们连接在一起，你通常会指定两个表。您处理的结构有时被称为分区表，尽管通常将数据集分离到不同的文件中，而不是只是随意地分割数据集。如果您负责数据库结构，并且没有很好的理由对数据进行分区，那么我将构建一个包含国家代码的额外“起源”字段的大表，但您这样做可能是出于合理的性能原因。可以使用union来连接您感兴趣的表:http://dev.mysql.com/doc/refman/5.0/en/union.html，或者使用合并数据库引擎http://dev.mysql.com/doc/refman/5.1/en/mergestorageengine.html。
#5
3
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.
您最初跨两个表的尝试创建了一个隐式连接。这是大多数经验丰富的SQL程序员不赞成的，因为它将表与如何合并的条件分开。
The UNION is 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.
联合对于表本身来说是一个很好的解决方案，但是应该没有理由不将它们放入索引良好的一个表中。我看到向一个大表添加正确的索引将查询速度提高了3个数量级。
#6
3
The union statement cause a deal time in huge data. It is good to perform the select in 2 steps:
工会的声明在巨大的数据中引发了交易时间。最好分两步执行select:
- select the id
- 选择id
- then select the main table with it
- 然后用它选择主表
#1
168
I think you're looking for the UNION clause, a la
我想你在找工会条款，洛杉矶。
(SELECT * from us_music where `genre` = 'punk')
UNION
(SELECT * from de_music where `genre` = 'punk')
#2
19
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万的记录。任何真正的数据库都可以处理这种大小的问题。添加正确的索引，您就可以将它们作为一个表来处理了。
#3
5
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'
#4
3
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.html or by using the Merge database engine http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html.
该列是不明确的，因为它出现在两个表中，您需要指定where(或排序)字段，如us_music。类型或de_music。类型，但是如果你想以某种方式将它们连接在一起，你通常会指定两个表。您处理的结构有时被称为分区表，尽管通常将数据集分离到不同的文件中，而不是只是随意地分割数据集。如果您负责数据库结构，并且没有很好的理由对数据进行分区，那么我将构建一个包含国家代码的额外“起源”字段的大表，但您这样做可能是出于合理的性能原因。可以使用union来连接您感兴趣的表:http://dev.mysql.com/doc/refman/5.0/en/union.html，或者使用合并数据库引擎http://dev.mysql.com/doc/refman/5.1/en/mergestorageengine.html。
#5
3
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.
您最初跨两个表的尝试创建了一个隐式连接。这是大多数经验丰富的SQL程序员不赞成的，因为它将表与如何合并的条件分开。
The UNION is 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.
联合对于表本身来说是一个很好的解决方案，但是应该没有理由不将它们放入索引良好的一个表中。我看到向一个大表添加正确的索引将查询速度提高了3个数量级。
#6
3
The union statement cause a deal time in huge data. It is good to perform the select in 2 steps:
工会的声明在巨大的数据中引发了交易时间。最好分两步执行select:
- select the id
- 选择id
- then select the main table with it
- 然后用它选择主表