# 1071 - Podany klucz był za długi; maksymalna długość klucza to 1000 bajtów

Wiem, że na pytania o tym tytule odpowiadano już wcześniej, ale proszę czytaj dalej. Przeczytałem dokładnie wszystkie inne pytania / odpowiedzi dotyczące tego błędu przed wysłaniem.

Otrzymuję powyższy błąd dla następującego zapytania:

CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
  `menu_id` varchar(32) NOT NULL,
  `parent_menu_id` int(32) unsigned DEFAULT NULL,
  `menu_name` varchar(255) DEFAULT NULL,
  `menu_link` varchar(255) DEFAULT NULL,
  `plugin` varchar(255) DEFAULT NULL,
  `menu_type` int(1) DEFAULT NULL,
  `extend` varchar(255) DEFAULT NULL,
  `new_window` int(1) DEFAULT NULL,
  `rank` int(100) DEFAULT NULL,
  `hide` int(1) DEFAULT NULL,
  `template_id` int(32) unsigned DEFAULT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `layout` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`menu_id`),
  KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Czy ktoś ma pomysł, dlaczego i jak to naprawić? Problem polega na tym, że to samo zapytanie działa doskonale na moim komputerze lokalnym i działało również na moim poprzednim hoście. Przy okazji, pochodzi z dojrzałego projektu - phpdevshell - więc myślę, że ci faceci wiedzą, co robią, chociaż nigdy nie wiadomo.

Każda wskazówka jest mile widziana.

Używam phpMyAdmin.

Jak mówi @Devart, całkowita długość twojego indeksu jest za długa.

Krótka odpowiedź jest taka, że ​​i tak nie powinieneś indeksować tak długich kolumn VARCHAR, ponieważ indeks będzie bardzo obszerny i nieefektywny.

Najlepszą praktyką jest używanie indeksów prefiksów, aby indeksować tylko lewy podciąg danych. Większość danych i tak będzie o wiele krótsza niż 255 znaków.

Podczas definiowania indeksu można zadeklarować długość prefiksu na kolumnę. Na przykład:

...
KEY `index` (`parent_menu_id`,`menu_link`(50),`plugin`(50),`alias`(50))
...

Ale jaka jest najlepsza długość prefiksu dla danej kolumny? Oto metoda, aby się tego dowiedzieć:

SELECT
 ROUND(SUM(LENGTH(`menu_link`)<10)*100/COUNT(`menu_link`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`menu_link`)<20)*100/COUNT(`menu_link`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`menu_link`)<50)*100/COUNT(`menu_link`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`menu_link`)<100)*100/COUNT(`menu_link`),2) AS pct_length_100
FROM `pds_core_menu_items`;

Informuje o odsetku wierszy, które mają nie więcej niż określoną długość ciągu w menu_linkkolumnie. Możesz zobaczyć takie dane wyjściowe:

+---------------+---------------+---------------+----------------+
| pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 |
+---------------+---------------+---------------+----------------+
|         21.78 |         80.20 |        100.00 |         100.00 |
+---------------+---------------+---------------+----------------+

To mówi ci, że 80% twoich ciągów ma mniej niż 20 znaków, a wszystkie twoje ciągi mają mniej niż 50 znaków. Nie ma więc potrzeby indeksowania więcej niż prefiksu o długości 50, a na pewno nie ma potrzeby indeksowania całej długości 255 znaków.

PS: Typy danych INT(1)i INT(32)wskazują na kolejne nieporozumienie dotyczące MySQL. Argument liczbowy nie ma wpływu na przechowywanie lub zakres wartości dozwolonych dla kolumny. INTma zawsze 4 bajty i zawsze dopuszcza wartości od -2147483648 do 2147483647. Argument liczbowy dotyczy wypełniania wartości podczas wyświetlania, co nie ma żadnego efektu, chyba że użyjesz tej ZEROFILLopcji.

Ten błąd oznacza, że ​​długość indeksu przekracza index1000 bajtów. To ograniczenie może mieć MySQL i silniki pamięci masowej. Mam podobny błąd w MySQL 5.5 - „Określony klucz był za długi; maksymalna długość klucza to 3072 bajtów po uruchomieniu tego skryptu:

CREATE TABLE IF NOT EXISTS test_table1 (
  column1 varchar(500) NOT NULL,
  column2 varchar(500) NOT NULL,
  column3 varchar(500) NOT NULL,
  column4 varchar(500) NOT NULL,
  column5 varchar(500) NOT NULL,
  column6 varchar(500) NOT NULL,
  KEY `index` (column1, column2, column3, column4, column5, column6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

UTF8 jest wielobajtowy, a długość klucza jest obliczana w ten sposób - 500 * 3 * 6 = 9000 bajtów.

Ale uwaga, następne zapytanie działa!

CREATE TABLE IF NOT EXISTS test_table1 (
  column1 varchar(500) NOT NULL,
  column2 varchar(500) NOT NULL,
  column3 varchar(500) NOT NULL,
  column4 varchar(500) NOT NULL,
  column5 varchar(500) NOT NULL,
  column6 varchar(500) NOT NULL,
  KEY `index` (column1, column2, column3, column4, column5, column6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

... ponieważ użyłem CHARSET = latin1, w tym przypadku długość klucza to 500 * 6 = 3000 bajtów.

Miałem ten problem i rozwiązałem go, wykonując następujące czynności:

Przyczyna

Istnieje znany błąd w MySQL związany z MyISAM, zestawem znaków UTF8 i indeksami, które możesz sprawdzić tutaj.

Rozkład

• Upewnij się, że MySQL jest skonfigurowany z silnikiem pamięci masowej InnoDB.

• Zmień używany domyślnie silnik magazynu, aby nowe tabele zawsze były odpowiednio tworzone:

  set GLOBAL storage_engine='InnoDb';

• W przypadku MySQL 5.6 i nowszych użyj następującego:

  SET GLOBAL default_storage_engine = 'InnoDB';

• Na koniec upewnij się, że postępujesz zgodnie z instrukcjami podanymi w artykule Migracja do MySQL .

Odniesienie

uruchom to zapytanie przed utworzeniem lub zmianą tabeli.

SET @@global.innodb_large_prefix = 1;

spowoduje to ustawienie maksymalnej długości klucza na 3072 bajty

Ten limit rozmiaru indeksu wydaje się być większy w przypadku 64-bitowych kompilacji MySQL.

Uderzałem w to ograniczenie, próbując zrzucić naszą deweloperską bazę danych i załadować ją na lokalny virt VMWare. W końcu zdałem sobie sprawę, że zdalny serwer deweloperski był 64-bitowy i utworzyłem 32-bitowy virt. Właśnie utworzyłem 64-bitowy virt i mogłem lokalnie załadować bazę danych.

Właśnie ominąłem ten błąd, po prostu zmieniając wartości „długości” w oryginalnej bazie danych na łącznie około „1000”, zmieniając jej strukturę, a następnie eksportując to samo na serwer. :)

Miałem ten sam problem, użyłem poniższego zapytania, aby go rozwiązać.

Podczas tworzenia bazy danych możesz używać kodowania utf-8

na przykład. create database my_db character set utf8 collate utf8mb4;

EDYCJA: (Biorąc pod uwagę sugestie z komentarzy) Zmieniono utf8_bin na utf8mb4