Błąd MySQL: specyfikacja klucza bez długości klucza

Mam tabelę z kluczem podstawowym, który jest varchar (255). Pojawiły się przypadki, w których 255 znaków to za mało. Próbowałem zmienić pole na tekst, ale pojawia się następujący błąd:

BLOB/TEXT column 'message_id' used in key specification without a key length

jak mogę to naprawić?

edycja: Powinienem również wskazać, że ta tabela ma złożony klucz podstawowy z wieloma kolumnami.

Błąd występuje, ponieważ MySQL może indeksować tylko pierwsze N ​​znaków BLOB lub TEXTkolumny. Błąd głównie tak się dzieje, gdy nie jest typ pola / kolumny TEXTlub BLOB lub te należą do TEXTlub BLOBtypy, takie jak TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, i LONGTEXTże starają się klucz podstawowy lub indeks. Z pełną BLOBlub TEXTbez wartości długości MySQL nie jest w stanie zagwarantować unikatowości kolumny, ponieważ ma ona zmienny i dynamiczny rozmiar. Tak więc, gdy używasz BLOBlub TEXTpiszemy jako indeks, należy podać wartość N, aby MySQL mógł określić długość klucza. Jednak MySQL nie obsługuje ograniczenia długości klucza dla TEXTlub BLOB. TEXT(88)po prostu nie zadziała.

Błąd pojawi się również, gdy spróbujesz przekonwertować kolumnę tabeli z non-TEXTi non-BLOBwpisać takie jak VARCHARi ENUMna TEXTlub BLOBwpisać, przy czym kolumna została już zdefiniowana jako unikalne ograniczenia lub indeks. Polecenie Alter Table SQL zakończy się niepowodzeniem.

Rozwiązaniem problemu jest usunięcie kolumny TEXTlub BLOBz indeksu lub ograniczenia unikalnego lub ustawienie innego pola jako klucza podstawowego. Jeśli nie możesz tego zrobić i chcesz nałożyć limit na kolumnę TEXTlub BLOB, spróbuj użyć VARCHARtype i umieść na niej limit długości. Domyślnie VARCHARjest ograniczony do maksymalnie 255 znaków, a jego limit musi być określony pośrednio w nawiasie zaraz po jego deklaracji, tzn. VARCHAR(200)Ograniczy go do 200 znaków.

Czasami, nawet jeśli nie używasz TEXTlub nie używasz BLOBpokrewnego typu w tabeli, może również pojawić się błąd 1170. Dzieje się tak w sytuacji, gdy VARCHARkolumna jest określona jako klucz podstawowy, ale niepoprawnie ustawiono długość lub rozmiar znaków. VARCHARMożna akceptuje tylko do 256 znaków, więc coś takiego jak VARCHAR(512)zmusi MySQL do auto-przekonwertować VARCHAR(512)na SMALLTEXTtyp danych, który następnie nie powiedzie się z powodu błędu 1170 o długości klucza, jeśli kolumna jest używana jako klucza podstawowego lub unikalnego lub nie-unikalnego indeksu. Aby rozwiązać ten problem, określ wartość VARCHARpola mniejszą niż 256 .

Odniesienie: MySQL Error 1170 (42000): Kolumna BLOB / TEXT używana w specyfikacji klucza bez długości klucza

Należy zdefiniować, która część wiodąca TEXTkolumny ma być indeksowana.

InnoDBma ograniczenie liczby 768bajtów na klucz indeksu i nie będzie można utworzyć indeksu dłużej.

To zadziała dobrze:

CREATE TABLE t_length (
      mydata TEXT NOT NULL,
      KEY ix_length_mydata (mydata(255)))
    ENGINE=InnoDB;

Pamiętaj, że maksymalna wartość rozmiaru klucza zależy od zestawu znaków kolumny. Są to 767znaki jak dla zestawu znaków jednobajtowychLATIN1 i tylko 255dla UTF8( MySQLtylko zastosowania BMPwymagające co najwyżej 3bajtów na znak)

Jeśli potrzebujesz, aby cała kolumna była PRIMARY KEYobliczeniem SHA1lub MD5skrótem i użyj jej jako PRIMARY KEY.

Możesz określić długość klucza we wniosku o zmianę tabeli, na przykład:

alter table authors ADD UNIQUE(name_first(20), name_second(20));

MySQL zabrania ono indeksowanie pełnej wartości BLOB, TEXTa długiVARCHAR kolumny, ponieważ dane w nich zawarte mogą być ogromne, a pośrednio indeksu DB będzie duża, co oznacza brak korzyści z indeksu.

MySQL wymaga zdefiniowania pierwszych N znaków do indeksowania, a sztuczką jest wybranie liczby N, która jest wystarczająco długa, aby zapewnić dobrą selektywność, ale wystarczająco krótką, aby zaoszczędzić miejsce. Prefiks powinien być wystarczająco długi, aby indeks był tak samo przydatny, jak w przypadku indeksowania całej kolumny.

Zanim przejdziemy dalej, zdefiniujmy kilka ważnych terminów. Selektywność indeksu to stosunek całkowitych odrębnych indeksowanych wartości i całkowitej liczby wierszy . Oto jeden przykład tabeli testowej:

+-----+-----------+
| id  | value     |
+-----+-----------+
| 1   | abc       |
| 2   | abd       |
| 3   | adg       |
+-----+-----------+

Jeśli indeksujemy tylko pierwszy znak (N = 1), wówczas tabela indeksów będzie wyglądać następująco:

+---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| a             | 1,2,3     |
+---------------+-----------+

W tym przypadku selektywność indeksu jest równa IS = 1/3 = 0,33.

Zobaczmy teraz, co się stanie, jeśli zwiększymy liczbę indeksowanych znaków do dwóch (N = 2).

+---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| ab             | 1,2      |
| ad             | 3        |
+---------------+-----------+

W tym scenariuszu IS = 2/3 = 0,66, co oznacza, że ​​zwiększyliśmy selektywność indeksu, ale zwiększyliśmy również rozmiar indeksu. Sztuczka polega na znalezieniu minimalnej liczby N, która doprowadzi do maksymalnej selektywności indeksu .

Istnieją dwa podejścia do wykonywania obliczeń dla tabeli bazy danych. Zrobię demonstrację w Internecie tym zrzutu bazy danych .

Powiedzmy, że chcemy dodać do indeksu kolumnę nazwisko w pracownikach tabeli i chcemy zdefiniować najmniejszą liczbę N która zapewni najlepszą selektywność indeksu.

Najpierw określmy najczęstsze nazwiska:

select count(*) as cnt, last_name 
from employees 
group by employees.last_name 
order by cnt

+-----+-------------+
| cnt | last_name   |
+-----+-------------+
| 226 | Baba        |
| 223 | Coorg       |
| 223 | Gelosh      |
| 222 | Farris      |
| 222 | Sudbeck     |
| 221 | Adachi      |
| 220 | Osgood      |
| 218 | Neiman      |
| 218 | Mandell     |
| 218 | Masada      |
| 217 | Boudaillier |
| 217 | Wendorf     |
| 216 | Pettis      |
| 216 | Solares     |
| 216 | Mahnke      |
+-----+-------------+
15 rows in set (0.64 sec)

Jak widać, nazwisko Baba jest najczęstsze. Teraz znajdziemy najczęściej występujące prefiksy nazwisk , zaczynając od pięcioliterowych prefiksów.

+-----+--------+
| cnt | prefix |
+-----+--------+
| 794 | Schaa  |
| 758 | Mande  |
| 711 | Schwa  |
| 562 | Angel  |
| 561 | Gecse  |
| 555 | Delgr  |
| 550 | Berna  |
| 547 | Peter  |
| 543 | Cappe  |
| 539 | Stran  |
| 534 | Canna  |
| 485 | Georg  |
| 417 | Neima  |
| 398 | Petti  |
| 398 | Duclo  |
+-----+--------+
15 rows in set (0.55 sec)

Występuje znacznie więcej wystąpień każdego prefiksu, co oznacza, że ​​musimy zwiększać liczbę N, aż wartości będą prawie takie same jak w poprzednim przykładzie.

Oto wyniki dla N = 9

select count(*) as cnt, left(last_name,9) as prefix 
from employees 
group by prefix 
order by cnt desc 
limit 0,15;

+-----+-----------+
| cnt | prefix    |
+-----+-----------+
| 336 | Schwartzb |
| 226 | Baba      |
| 223 | Coorg     |
| 223 | Gelosh    |
| 222 | Sudbeck   |
| 222 | Farris    |
| 221 | Adachi    |
| 220 | Osgood    |
| 218 | Mandell   |
| 218 | Neiman    |
| 218 | Masada    |
| 217 | Wendorf   |
| 217 | Boudailli |
| 216 | Cummings  |
| 216 | Pettis    |
+-----+-----------+

Oto wyniki dla N = 10.

+-----+------------+
| cnt | prefix     |
+-----+------------+
| 226 | Baba       |
| 223 | Coorg      |
| 223 | Gelosh     |
| 222 | Sudbeck    |
| 222 | Farris     |
| 221 | Adachi     |
| 220 | Osgood     |
| 218 | Mandell    |
| 218 | Neiman     |
| 218 | Masada     |
| 217 | Wendorf    |
| 217 | Boudaillie |
| 216 | Cummings   |
| 216 | Pettis     |
| 216 | Solares    |
+-----+------------+
15 rows in set (0.56 sec)

To bardzo dobre wyniki. Oznacza to, że możemy wykonać indeks na kolumnie last_namez indeksowaniem tylko pierwszych 10 znaków. W definicji tabeli kolumna last_namejest zdefiniowana jako VARCHAR(16), a to oznacza, że ​​zapisaliśmy 6 bajtów (lub więcej, jeśli w nazwisku występuje znak UTF8) na pozycję. W tej tabeli znajduje się 1637 różnych wartości pomnożonych przez 6 bajtów, czyli około 9 KB, i wyobraź sobie, jak ta liczba by wzrosła, gdyby nasza tabela zawierała milion wierszy.

Możesz przeczytać inne sposoby obliczania liczby N w moim poście Prefiksy indeksów w MySQL .

Wystąpił ten błąd podczas dodawania indeksu do tabeli z kolumnami typu tekstowego. Musisz zadeklarować rozmiar, którego chcesz użyć dla każdego typu tekstu.

Wprowadź wielkość rozmiaru w nawiasie ()

Jeśli używanych jest zbyt wiele bajtów, możesz zadeklarować rozmiar w nawiasach dla varchar, aby zmniejszyć ilość używaną do indeksowania. Dzieje się tak nawet wtedy, gdy zadeklarowałeś rozmiar typu już takiego jak varchar (1000). Nie musisz tworzyć nowego stołu, jak powiedzieli inni.

Dodawanie indeksu

alter table test add index index_name(col1(255),col2(255));

Dodanie unikalnego indeksu

alter table test add unique index_name(col1(255),col2(255));

alter table authors ADD UNIQUE(name_first(767), name_second(767));

UWAGA : 767 to liczba znaków do której MySQL będzie indeksował kolumny podczas zajmowania się indeksami obiektów blob / text

Ref: http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

Innym doskonałym sposobem radzenia sobie z tym jest utworzenie pola TEXT bez wyjątkowego ograniczenia i dodanie pola VARCHAR rodzeństwa, które jest unikalne i zawiera skrót (MD5, SHA1 itp.) Pola TEXT. Oblicz i przechowuj podsumowanie w całym polu TEXT, gdy wstawiasz lub aktualizujesz pole TEXT, wtedy masz ograniczenie unikatowości w całym polu TEXT (a nie w części wiodącej), które można szybko przeszukać.

Nie używaj długich wartości jako klucza podstawowego. To zniszczy twoje osiągi. Zobacz instrukcję mysql, rozdział 13.6.13 „InnoDB Performance Tuning and Rozwiązywanie problemów”.

Zamiast tego należy mieć zastępczy klucz int jako podstawowy (z auto_increment), a swój klucz loong jako dodatkowy UNIQUE.

Dodaj kolejną kolumnę varChar (255) (domyślnie jako pusty ciąg znaków nie jest pusty), aby wstrzymać przepełnienie, gdy 255 znaków nie wystarczy, i zmień tę PK, aby użyć obu kolumn. Nie wydaje się to jednak dobrze zaprojektowanym schematem bazy danych i zaleciłbym, aby modelarz danych spojrzał na to, co masz, z myślą o przefaktoryzowaniu go w celu uzyskania większej normalizacji.

Rozwiązaniem tego problemu jest to, że w CREATE TABLEinstrukcji można dodać ograniczenie UNIQUE ( problemtextfield(300) )po utworzeniu definicji kolumny, aby na przykład określić keydługość 300znaków dla TEXTpola. Wtedy pierwsze 300znaki z problemtextfield TEXTpola będą musiały być unikalne, a wszelkie późniejsze różnice zostaną zignorowane.

Ponadto, jeśli chcesz użyć indeksu w tym polu, powinieneś użyć silnika pamięci MyISAM i typu indeksu FULLTEXT.

Jak dotąd nikt o tym nie wspominał ... z utf8mb4, który jest 4-bajtowy i może również przechowywać emotikony (nigdy nie powinniśmy więcej używać 3-bajtowego utf8) i możemy uniknąć błędów, tak jak Incorrect string value: \xF0\x9F\x98\...nie powinniśmy używać typowego VARCHAR (255), a raczej VARCHAR ( 191) ponieważ w przypadku, gdy utf8mb4 i VARCHAR (255) ta sama część danych jest przechowywana poza stroną i nie możesz utworzyć indeksu dla kolumny VARCHAR (255), ale dla VARCHAR (191) możesz to zrobić. Jest tak, ponieważ maksymalny rozmiar indeksowanej kolumny to 767 bajtów dla ROW_FORMAT = COMPACT lub ROW_FORMAT = REDUNDANT.

W przypadku nowszych formatów wierszy ROW_FORMAT = DYNAMIC lub ROW_FORMAT = COMPRESSED (co wymaga nowszego formatu pliku innodb_file_format = Barracuda nie jest starszą antylopą) maksymalny rozmiar indeksowanej kolumny to 3072. Jest dostępny od MySQL> = 5.6.3, gdy innodb_large_prefix = 1 (domyślnie wyłączone MySQL <= 5.7.6 i domyślnie włączony dla MySQL> = 5.7.7). Więc w tym przypadku możemy użyć VARCHAR (768) dla utf8mb4 (lub VARCHAR (1024) dla starego utf8) dla kolumny indeksowanej. Opcja innodb_large_prefix jest przestarzała od 5.7.7, ponieważ jej zachowanie jest wbudowane w MySQL 8 (w tej wersji opcja jest usunięta).

Musisz zmienić typ kolumny na varcharlub integerdo indeksowania.

Przejdź do mysql edit table-> zmień typ kolumny na varchar(45).

Użyj w ten sposób

@Id
@Column(name = "userEmailId", length=100)
private String userEmailId;