Kod błędu 1117 Zbyt wiele kolumn; Limit kolumn MySQL w tabeli

Mam tabelę z 1699 kolumnami i gdy próbuję wstawić więcej kolumn, otrzymuję

Kod błędu: 1117. Zbyt wiele kolumn

W tej tabeli mam tylko 1000 wierszy. Dla mnie najważniejsza jest liczba kolumn. Czy są jakieś ograniczenia na stole? Chcę utworzyć 2000 kolumn. Czy to jest możliwe?

Dlaczego miałbyś chcieć stworzyć tabelę z nawet 20 kolumnami, a co dopiero 2000?

Przyznane, zdormalizowane dane mogą zapobiec konieczności wykonywania JOIN w celu pobrania wielu kolumn danych. Jeśli jednak masz ponad 10 kolumn, powinieneś zatrzymać się i pomyśleć o tym, co stanie się pod maską podczas pobierania danych.

Jeśli tabela 2000 kolumn zostanie poddana WYBIERZ * Z ... GDZIE, podczas przetwarzania generowałbyś duże tabele tymczasowe, pobierając niepotrzebne kolumny i tworząc wiele scenariuszy, w których pakiety komunikacyjne ( max_allowed_packet ) byłyby wypychane na skraj każdego zapytania.

Wcześniej, jako programista, pracowałem w firmie w 1995 roku, gdzie DB2 był głównym RDBMS. Firma miała jedną tabelę zawierającą 270 kolumn, dziesiątki indeksów i problemy z wydajnością podczas pobierania danych. Skontaktowali się z IBM i konsultanci sprawdzili architekturę swojego systemu, w tym ten jeden monolityczny stół. Firmie powiedziano „Jeśli nie znormalizujesz tej tabeli w ciągu najbliższych 2 lat, DB2 nie powiedzie się w przypadku zapytań wykonujących przetwarzanie Stage2 (wszelkie zapytania wymagające sortowania według nieindeksowanych kolumn)”. Powiedziano to firmie o wartości wielu bilionów dolarów, aby znormalizować 270-kolumnowy stół. O ileż bardziej tabela 2000 kolumn.

Jeśli chodzi o mysql, musiałbyś zrekompensować taki zły projekt, ustawiając opcje porównywalne z przetwarzaniem DB2 Stage2. W takim przypadku byłyby to opcje

• max_allowed_packet
• tmp_table_size
• max_tmp_tables
• max_heap_table_size
• max_length_for_sort_data
• max_sort_length
• sort_buffer_size
• myisam_max_sort_file_size
• myisam_sort_buffer_size

Dostosowywanie tych ustawień w celu uzupełnienia obecności kilkudziesięciu, nie mówiąc już o setkach kolumn, działa dobrze, jeśli masz TB pamięci RAM.

Ten problem mnoży się geometrycznie, jeśli użyjesz InnoDB, ponieważ będziesz musiał poradzić sobie z MVCC (Multiversion Concurrency Control) próbującym chronić mnóstwo kolumn przy każdym WYBORZE, AKTUALIZACJI i USUNIĘCIU poprzez izolację transakcji.

WNIOSEK

Nie ma substytutu ani opaski, która mogłaby zrekompensować zły projekt. Proszę, dla własnego zdrowia psychicznego w przyszłości, znormalizuj ten stół już dziś !!!

Mam problem z wyobrażeniem sobie czegoś, w którym model danych mógłby zgodnie z prawem zawierać 2000 kolumn w odpowiednio znormalizowanej tabeli.

Domyślam się, że prawdopodobnie wykonujesz jakiś zdenormalizowany schemat „wypełnij puste pola”, w którym faktycznie przechowujesz różne rodzaje danych w jednej tabeli, zamiast rozdzielać dane na osobne tabele i tworzyć relacje , masz różne pola, które rejestrują, jaki „typ” danych jest przechowywany w danym wierszu, a 90% z nich ma wartość NULL. Ale nawet wtedy, aby dostać się do 2000 kolumn ... tak.

Rozwiązaniem problemu jest ponowne przemyślenie modelu danych. Jeśli przechowujesz wielki stos danych klucz / wartość powiązanych z danym rekordem, dlaczego nie modelować go w ten sposób? Coś jak:

CREATE TABLE master (
    id INT PRIMARY KEY AUTO_INCREMENT,
    <fields that really do relate to the
    master records on a 1-to-1 basis>
);

CREATE TABLE sensor_readings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    master_id INT NOT NULL,   -- The id of the record in the
                              -- master table this field belongs to
    sensor_id INT NOT NULL,
    value VARCHAR(255)
);

CREATE TABLE sensors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    <fields relating to sensors>
);

Następnie, aby uzyskać wszystkie wpisy czujników związane z danym rekordem „wzorcowym”, wystarczy SELECT sensor_id,value FROM sensor_readings WHERE master_id=<some master ID>. Jeśli chcesz uzyskać dane dla rekordu w mastertabeli wraz ze wszystkimi danymi czujnika dla tego rekordu, możesz użyć sprzężenia:

SELECT master.*,sensor_readings.sensor_id,sensor_readings.value
FROM master INNER JOIN sensor_readings on master.id=sensor_readings.master_id
WHERE master.id=<some ID>

A następnie dołącza, jeśli potrzebujesz szczegółów na temat każdego czujnika.

To system pomiarowy z 2000 czujnikami

Zignoruj ​​wszystkie komentarze krzyczące na temat normalizacji - to, o co prosisz, może być rozsądnym projektem bazy danych (w idealnym świecie) i doskonale dobrze znormalizowanym, jest to po prostu bardzo niezwykłe, a jak wskazano w innym miejscu, RDBMS zwykle nie są zaprojektowane dla tak wielu kolumn .

Chociaż nie osiągasz twardego limitu MySQL , jeden z innych czynników wymienionych w linku prawdopodobnie uniemożliwia ci przejście wyżej

Jak sugerują inni, możesz obejść to ograniczenie, mając tabelę podrzędną z id, sensor_id, sensor_value, lub prościej, możesz utworzyć drugą tabelę zawierającą tylko kolumny, które nie zmieściłyby się w pierwszej (i użyć tej samej PK)

MySQL 5.0 Limity liczby kolumn (wyróżnienie dodane):

Istnieje sztywny limit 4096 kolumn na tabelę , ale efektywne maksimum może być mniejsze dla danej tabeli. Dokładny limit zależy od kilku współdziałających czynników.

• Każda tabela (niezależnie od silnika pamięci) ma maksymalny rozmiar wiersza 65 535 bajtów. Silniki pamięci masowej mogą nakładać dodatkowe ograniczenia na ten limit, zmniejszając efektywny maksymalny rozmiar wiersza.

  Maksymalny rozmiar wiersza ogranicza liczbę (i ewentualnie rozmiar) kolumn, ponieważ całkowita długość wszystkich kolumn nie może przekroczyć tego rozmiaru.

...

Poszczególne silniki pamięci masowej mogą nakładać dodatkowe ograniczenia, które ograniczają liczbę kolumn w tabeli. Przykłady:

• InnoDB pozwala na maksymalnie 1000 kolumn.

Najpierw trochę więcej ognia, potem prawdziwe rozwiązanie ...

W większości zgadzam się z płomieniami, które zostały już na ciebie rzucone.

Nie zgadzam się z normalizacją klucz-wartość. Zapytania stają się okropne; wydajność jeszcze gorsza.

Jednym „prostym” sposobem uniknięcia bezpośredniego problemu (ograniczenie liczby kolumn) jest „pionowe” podzielenie danych. Załóżmy, powiedzmy, 5 tabel z 400 kolumnami każda. Wszystkie miałyby ten sam klucz podstawowy, z tym wyjątkiem, że może to być AUTO_INCREMENT.

Być może lepiej byłoby zdecydować o kilku najważniejszych polach i umieścić je w tabeli „głównej”. Następnie zgrupuj czujniki w logiczny sposób i umieść je w kilku równoległych tabelach. Przy odpowiednim grupowaniu może nie być konieczne dołączanie do wszystkich tabel przez cały czas.

Czy indeksujesz którąś z wartości? Czy musisz na nich szukać? Prawdopodobnie szukasz daty / godziny?

Jeśli musisz zaindeksować wiele kolumn - wykop.

Jeśli chcesz zindeksować kilka, umieść je w głównej tabeli.

Oto prawdziwe rozwiązanie (jeśli dotyczy) ...

Jeśli nie potrzebujesz indeksowanej szerokiej gamy czujników, nie twórz kolumn! Tak, słyszałeś mnie Zamiast tego zbierz je do JSON, skompresuj JSON, zapisz w polu BLOB. Zaoszczędzisz mnóstwo miejsca; będziesz mieć tylko jedną tabelę, bez problemów z limitami kolumn; itp. Twoja aplikacja rozpakuje się, a następnie użyje JSON jako struktury. Zgadnij co? Możesz mieć strukturę - możesz pogrupować czujniki w tablice, rzeczy wielopoziomowe itp., Tak jak chciałaby twoja aplikacja. Kolejna „cecha” - jest otwarta. Jeśli dodasz więcej czujników, nie musisz ZMIENIAĆ tabeli. JSON, jeśli w ten sposób elastyczny.

(Kompresja jest opcjonalna; jeśli Twój zestaw danych jest ogromny, pomoże to w wykorzystaniu miejsca na dysku, a tym samym ogólnej wydajności.)

Widzę to jako możliwy scenariusz w świecie dużych zbiorów danych, w którym możesz nie wykonywać tradycyjnych zapytań typu select *. Zajmujemy się tym w świecie modelowania predykcyjnego na poziomie klienta, w którym modelujemy klienta w tysiącach wymiarów (wszystkie o wartości 0 lub 1). Ten sposób przechowywania ułatwia wykonywanie dalszych działań związanych z budowaniem modelu itp., Gdy czynniki ryzyka znajdują się w tym samym rzędzie, a flaga wyniku również w tym samym rzędzie. Można to znormalizować z punktu widzenia magazynu z nadrzędną strukturą potomną, ale model predykcyjny niższego szczebla będzie musiał przekształcić go z powrotem w płaski schemat. Używamy redshift, który zajmuje pamięć kolumnową, więc twoje ponad 1000 kolumn, gdy ładujesz dane, faktycznie są przechowywane w formacie kolumnowym ...

Na ten projekt jest czas i miejsce. Absolutnie. Normalizacja nie jest rozwiązaniem dla każdego problemu.