MySQL - wybieranie danych z wielu tabel, z których wszystkie mają taką samą strukturę, ale różne dane

79

OK, oto mój dylemat. Mam bazę danych utworzoną z około 5 tabelami z dokładnie taką samą strukturą danych. Dane są rozdzielane w ten sposób w celu lokalizacji i podzielenia łącznie około 4,5 miliona rekordów.

W większości przypadków potrzebny jest tylko jeden stół i wszystko jest w porządku. Jednak czasami potrzebne są dane z 2 lub więcej tabel i muszą być sortowane według kolumny zdefiniowanej przez użytkownika. Tutaj mam problemy.

kolumny danych:

id, band_name, song_name, album_name, genre

Statystyka MySQL:

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

MySQL wypluwa ten błąd:

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

Oczywiście robię to źle. Czy ktoś chciałby rzucić na to trochę światła?

— Jayrox
źródło

177

Myślę, że szukasz klauzuli UNION , a la

(SELECT * from us_music where `genre` = 'punk')
UNION
(SELECT * from de_music where `genre` = 'punk')

— Mihai Limbășan
źródło

@ mihai-limban - przepraszam, że przeszkadzam, ale czy istnieje sposób na rozpoznanie na podstawie zestawu wyników tego, „który wynik pochodzi z której tabeli”. Ponieważ musimy zaktualizować / usunąć rekord z tego zestawu wyników, nie ma sposobu, aby to wiedzieć.

— web-nomad

7

@Pushpesh dodaj unikalny identyfikator ciągu do każdego SELECT, np .:(SELECT 'us_music' AS from_table, * FROM us_music WHERE genre = 'punk') UNION ...

— jkrcma

Jaka jest wartość gatunku nie jest znana, ale identyfikatory powinny być zgodne w dwóch tabelach? Czy możesz coś takiego zrobić? (SELECT 1) AS select1 UNION (SELECT 2) AS select2 WHERE select1.id=select2.id

— ZurabWeb

Idealnie, właśnie dlatego uwielbiam Stack! Google, znajdź pytanie dotyczące stosu i odpowiedź już tutaj! Dzięki!

— Rocco The Taco

Jaka jest składnia do grupowania według UNION zestawu wyników, a następnie wykonywania według kolejności? Powiedzmy, że jest viewCounti movieTitlegdzie jest jedna DB na każdy miesiąc. Zsumujesz wszystkie 12 tabel, co jest w porządku, ale w wyniku otrzymasz 12 indywidualnych zestawów wyników. A co by było, gdybyś chciał tylko jednego zestawu wyników, w którym wszystkie wyniki zostałyby pogrupowane według, movieTitlea viewCountwartości zostały zsumowane dla każdego movieTitlewiersza?

— anon58192932

19

Wygląda na to, że jesteś szczęśliwy z jednym stołem. Pięć mających ten sam schemat i czasami trzeba je przedstawiać tak, jakby pochodziły z jednej tabeli, wskazują na umieszczenie wszystkiego w jednej tabeli.

Dodaj nową kolumnę, której można użyć do rozróżnienia między pięcioma językami (zakładam, że jest to język inny w tabelach, ponieważ powiedziałeś, że służy do lokalizacji). Nie martw się o 4,5 miliona rekordów. Każda prawdziwa baza danych może obsłużyć ten rozmiar bez problemu. Dodaj prawidłowe indeksy, a nie będziesz mieć problemu z traktowaniem ich jako pojedynczej tabeli.

— Ned Batchelder
źródło

Początkowo wszystkie moje dane znajdowały się w jednej tabeli, ale po około 3,5 miliona rekordów zaczęły one indeksować przez 5–10 sekund. Przekonałem się, że najlepiej dla mnie działa rozdzielenie, ponieważ było znacznie szybsze. Mam teraz nowego webhosta, więc może być lepiej, ale wydaje mi się, że połączenie go wydaje się zbyt

— trudne

26

Wygląda na to, że musisz dodać indeksy do tabel.

— Ned Batchelder

1

Tak, w zasadzie leczyłeś symptomy problemu bez rozwiązania podstawowego problemu (niewłaściwe / niewystarczające indeksowanie). Co się stanie, jeśli jedna z Twoich 5 tabel osiągnie 4,5 mln wierszy i ponownie zacznie indeksować?

— Lo-Tan

5

Dowolna z powyższych odpowiedzi jest poprawna lub alternatywnym sposobem jest rozszerzenie nazwy tabeli, tak aby zawierała również nazwę bazy danych - np .:

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

— Muczeć
źródło

co daje bardzo źle zdefiniowany zestaw wyników: wszystkie możliwe pary nas_ i de_ punk.

— David Schmitt,

4

Kolumna jest niejednoznaczna, ponieważ pojawia się w obu tabelach, musisz określić pole gdzie (lub sort) w pełni, takie jak us_music.genre lub de_music.genre, ale zwykle określasz dwie tabele, jeśli chcesz połączyć je razem w trochę mody. Struktura, z którą masz do czynienia, jest czasami nazywana tabelą podzieloną na partycje, chociaż zwykle jest to robione w celu rozdzielenia zestawu danych na osobne pliki, a nie tylko do arbitralnego podzielenia zestawu danych. Jeśli zarządzasz strukturą bazy danych i nie ma dobrego powodu, aby podzielić dane, zbudowałbym jedną dużą tabelę z dodatkowym polem „pochodzenia”, które zawiera kod kraju, ale prawdopodobnie robisz to z uzasadnionego powodu wydajności . Użyj unii, aby dołączyć do interesujących Cię tabel http: //dev.mysql.http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html .

3

Twoja pierwotna próba objęcia obu tabel tworzy niejawne JOIN. Jest to mile widziane przez najbardziej doświadczonych programistów SQL, ponieważ oddziela tabele, które mają być połączone, z warunkiem, jak.

UNIONJest dobrym rozwiązaniem dla tych tabel, ponieważ są one, ale nie powinno być powodem, że nie można umieścić w jednej tabeli z godnej indeksowania. Widziałem dodanie odpowiedniego indeksu do dużej tabeli, co zwiększyło szybkość zapytań o trzy rzędy wielkości.

— staticsan
źródło

3

unionOświadczenie przyczyną czasie do czynienia w ogromnych danych. Dobrze jest dokonać selekcji w 2 krokach:

wybierz identyfikator
następnie wybierz nim główną tabelę

— mehdi mohamadi
źródło