MySQL - Selecting data from multiple tables all wi

问题:

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.

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:

SELECT * from us_music, de_music where `genre` = 'punk'

MySQL spits out this error:

#1052 - Column 'genre' in where clause is ambiguous

Obviously, I am doing this wrong. Anyone care to shed some light on this for me?

回答1:

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:

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.

回答3:

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:

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.

回答5:

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.

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.

回答6:

The union statement cause a deal time in huge data. It is good to perform the select in 2 steps:

1. select the id
2. then select the main table with it