Kiedy wykonałem następujące polecenie:

ALTER TABLE `mytable` ADD UNIQUE (
`column1` ,
`column2`
);

Otrzymałem ten komunikat o błędzie:

#1071 - Specified key was too long; max key length is 767 bytes

Informacje o kolumnie 1 i kolumnie 2:

column1 varchar(20) utf8_general_ci
column2  varchar(500) utf8_general_ci

Myślę, że varchar(20)wymaga tylko 21 bajtów, a varchar(500)tylko 501 bajtów. Więc łączna liczba bajtów to 522, mniej niż 767. Dlaczego więc dostałem komunikat o błędzie?

#1071 - Specified key was too long; max key length is 767 bytes

767 bajtów to podane ograniczenie prefiksu dla tabel InnoDB w MySQL w wersji 5.6 (i wcześniejszych wersjach). Dla tabel MyISAM ma 1000 bajtów. W MySQL w wersji 5.7 i wyższych limit ten został zwiększony do 3072 bajtów.

Musisz także pamiętać, że jeśli ustawisz indeks na duże pole char lub varchar, które jest zakodowane w utf8mb4, musisz podzielić maksymalną długość prefiksu indeksu wynoszącą 767 bajtów (lub 3072 bajtów) przez 4, co daje wynik 191. Jest tak, ponieważ maksymalna długość znaku utf8mb4 wynosi cztery bajty. Dla znaku utf8 byłyby to trzy bajty, co daje maksymalną długość prefiksu indeksu 254.

Jedną z opcji jest po prostu dolny limit na polach VARCHAR.

Inną opcją (zgodnie z odpowiedzią na ten problem ) jest uzyskanie podzbioru kolumny zamiast całej kwoty, tj .:

ALTER TABLE `mytable` ADD UNIQUE ( column1(15), column2(200) );

Dostosuj, aby uzyskać klucz do zastosowania, ale zastanawiam się, czy warto byłoby przejrzeć model danych dotyczący tego podmiotu, aby sprawdzić, czy istnieją ulepszenia, które pozwoliłyby na wdrożenie zamierzonych reguł biznesowych bez naruszania ograniczeń MySQL.

Jeśli ktoś ma problemy z INNODB / Utf-8 próbującym umieścić UNIQUEindeks w VARCHAR(256)polu, przełącz go na VARCHAR(255). Wydaje się, że 255 jest ograniczeniem.

Kiedy osiągniesz limit. Ustaw następujące.

• INNODB utf8 VARCHAR(255)
• INNODB utf8mb4 VARCHAR(191)

MySQL zakłada najgorszy przypadek liczby bajtów na znak w ciągu. W przypadku kodowania MySQL „utf8” jest to 3 bajty na znak, ponieważ kodowanie to nie dopuszcza znaków poza nim U+FFFF. W przypadku kodowania „utf8mb4” MySQL jest to 4 bajty na znak, ponieważ tak właśnie MySQL nazywa rzeczywisty UTF-8.

Zakładając, że używasz „utf8”, twoja pierwsza kolumna zajmie 60 bajtów indeksu, a druga następna 1500.

uruchom to zapytanie przed zapytaniem:

SET @@global.innodb_large_prefix = 1;

zwiększy to limit do 3072 bytes.

Rozwiązanie dla Laravel Framework

Zgodnie z dokumentacją Laravel 5.4. * ; Musisz ustawić domyślną długość ciągu w bootmetodzie app/Providers/AppServiceProvider.phppliku w następujący sposób:

use Illuminate\Support\Facades\Schema;

public function boot() 
{
    Schema::defaultStringLength(191); 
}

Wyjaśnienie tej poprawki podane w dokumentacji Laravel 5.4. * :

Laravel domyślnie korzysta z utf8mb4zestawu znaków, który obejmuje obsługę przechowywania „emotikonów” w bazie danych. Jeśli używasz wersji MySQL starszej niż wersja 5.7.7 lub MariaDB starszej niż wersja 10.2.2, może być konieczne ręczne skonfigurowanie domyślnej długości ciągu generowanego przez migracje, aby MySQL mógł utworzyć dla nich indeksy. Możesz to skonfigurować, wywołując Schema::defaultStringLengthmetodę w swoim AppServiceProvider.

Alternatywnie możesz włączyć innodb_large_prefixopcję dla swojej bazy danych. Instrukcje dotyczące prawidłowego włączania tej opcji znajdują się w dokumentacji bazy danych.

Jakiego kodowania znaków używasz? Niektóre zestawy znaków (jak UTF-16 i tak dalej) używają więcej niż jednego bajtu na znak.

Myślę, że varchar (20) wymaga tylko 21 bajtów, podczas gdy varchar (500) wymaga tylko 501 bajtów. Więc łączna liczba bajtów to 522, mniej niż 767. Dlaczego więc dostałem komunikat o błędzie?

UTF8 wymaga 3 bajtów na znak do przechowywania łańcucha, więc w twoim przypadku 20 + 500 znaków = 20 * 3 + 500 * 3 = 1560 bajtów, co jest więcej niż dozwolone 767 bajtów.

Limit dla UTF8 wynosi 767/3 = 255 znaków , dla UTF8mb4, który wykorzystuje 4 bajty na znak, wynosi 767/4 = 191 znaków.

Istnieją dwa rozwiązania tego problemu, jeśli chcesz użyć dłuższej kolumny niż limit:

1. Użyj „tańszego” kodowania (tego, które wymaga mniej bajtów na znak)
   W moim przypadku musiałem dodać Unikalny indeks do kolumny zawierającej ciąg SEO artykułu, ponieważ używam tylko [A-z0-9\-]znaków dla SEO, użyłem, latin1_general_ciktóry używa tylko jednego bajtu na znak i dlatego kolumna może mieć 767 bajtów długości.
2. Utwórz skrót z kolumny i użyj unikalnego indeksu tylko dla tej
   drugiej Inną opcją dla mnie było utworzenie kolejnej kolumny, która przechowałaby skrót SEO, ta kolumna miałaby UNIQUEklucz do zapewnienia unikalności wartości SEO. Dodałbym również KEYindeks do oryginalnej kolumny SEO, aby przyspieszyć wyszukiwanie.

Odpowiedź na pytanie, dlaczego pojawia się komunikat o błędzie, została już udzielona przez wielu użytkowników tutaj. Moja odpowiedź dotyczy tego, jak to naprawić i jak używać.

Zobacz ten link .

1. Otwórz klienta MySQL (lub klienta MariaDB). Jest to narzędzie wiersza poleceń.
2. Zostaniesz poproszony o hasło, wprowadź poprawne hasło.
3. Wybierz bazę danych za pomocą tego polecenia use my_database_name;

Baza danych zmieniona

4. set global innodb_large_prefix=on;

Zapytanie OK, dotyczy 0 wierszy (0,00 s)

5. set global innodb_file_format=Barracuda;

Zapytanie OK, dotyczy 0 wierszy (0,02 s)

6. Przejdź do swojej bazy danych na phpMyAdmin lub coś podobnego, aby ułatwić zarządzanie. > Wybierz bazę danych> Wyświetl strukturę tabeli > Przejdź do zakładki Operacje . > Zmień ROW_FORMAT na DYNAMIC i zapisz zmiany.
7. Przejdź do zakładki struktury tabeli > Kliknij przycisk Unikatowy .
8. Gotowy. Teraz nie powinien zawierać błędów.

Problem tej poprawki polega na tym, że eksportujesz bazę danych db na inny serwer (na przykład z hosta lokalnego na prawdziwy host) i nie możesz używać wiersza poleceń MySQL na tym serwerze. Nie możesz sprawić, żeby tam działało.

Specified key was too long; max key length is 767 bytes

Otrzymałeś tę wiadomość, ponieważ 1 bajt jest równy 1 znakowi tylko wtedy, gdy używasz latin-1zestawu znaków. Jeśli użyjesz utf8, każdy znak zostanie uznany za 3 bajty podczas definiowania kolumny klucza. Jeśli użyjesz utf8mb4, każdy znak będzie traktowany jako 4 bajty podczas definiowania kolumny klucza. Dlatego musisz pomnożyć limit znaków pola klucza przez 1, 3 lub 4 (w moim przykładzie), aby określić liczbę bajtów, na którą pole klucza próbuje zezwolić. Jeśli używasz uft8mb4, możesz zdefiniować tylko 191 znaków dla natywnego pola klucza podstawowego InnoDB. Tylko nie przekraczaj 767 bajtów.

możesz dodać kolumnę md5 długich kolumn

Wymień utf8mb4się utf8w pliku importu.

Napotkaliśmy ten problem podczas próby dodania indeksu UNIQUE do pola VARCHAR (255) przy użyciu utf8mb4. Podczas gdy problem jest już dobrze zarysowany, chciałem dodać kilka praktycznych rad, w jaki sposób wymyśliliśmy to i rozwiązaliśmy.

Gdy używasz utf8mb4, znaki liczą się jako 4 bajty, podczas gdy w utf8 mogą one wynosić 3 bajty. Bazy danych InnoDB mają taki limit, że indeksy mogą zawierać tylko 767 bajtów. Tak więc, używając utf8, możesz przechowywać 255 znaków (767/3 = 255), ale używając utf8mb4, możesz przechowywać tylko 191 znaków (767/4 = 191).

Absolutnie możesz dodawać regularne indeksy dla VARCHAR(255)pól za pomocą utf8mb4, ale dzieje się tak, że rozmiar indeksu jest automatycznie obcinany do 191 znaków - jak unique_keytutaj:

Jest to w porządku, ponieważ zwykłe indeksy są po prostu używane, aby pomóc MySQL w szybszym wyszukiwaniu danych. Całe pole nie musi być indeksowane.

Dlaczego więc MySQL automatycznie obcina indeks dla zwykłych indeksów, ale zgłasza wyraźny błąd, próbując zrobić to dla unikalnych indeksów? Cóż, aby MySQL mógł dowiedzieć się, czy wstawiana lub aktualizowana wartość już istnieje, musi faktycznie zindeksować całą wartość, a nie tylko jej część.

Na koniec dnia, jeśli chcesz mieć unikalny indeks na polu, cała zawartość pola musi mieścić się w indeksie. W przypadku utf8mb4 oznacza to zmniejszenie długości pola VARCHAR do 191 znaków lub mniej. Jeśli nie potrzebujesz utf8mb4 dla tej tabeli lub pola, możesz upuścić go z powrotem do utf8 i mieć możliwość zachowania 255 pól długości.

Oto moja oryginalna odpowiedź:

Po prostu upuszczam bazę danych i odtwarzam w ten sposób, a błąd zniknął:

drop database if exists rhodes; create database rhodes default CHARACTER set utf8 default COLLATE utf8_general_ci;

Jednak nie działa we wszystkich przypadkach.

W rzeczywistości jest to problem z użyciem indeksów w kolumnach VARCHAR z zestawem znaków utf8(lub utf8mb4), z kolumnami VARCHAR, które mają więcej niż pewną długość znaków. W przypadkuutf8mb4 tej pewnej długości wynosi 191.

Więcej informacji na temat używania długich indeksów w bazie danych MySQL znajduje się w sekcji Długi indeks: http://hanoian.com/content/index.php/24-automate-the-converting-a-mysql-database- zestaw znaków do utf8mb4

5 obejść:

Limit został podniesiony w 5.7.7 (MariaDB 10.2.2?). I można go zwiększyć za pomocą niektórych prac w 5.6 (10.1).

Jeśli osiągasz limit z powodu próby użycia ZESTAWU ZNAKÓW utf8mb4. Następnie wykonaj jedną z następujących czynności (każda ma wadę), aby uniknąć błędu:

⚈  Upgrade to 5.7.7 for 3072 byte limit -- your cloud may not provide this;
⚈  Change 255 to 191 on the VARCHAR -- you lose any values longer than 191 characters (unlikely?);
⚈  ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese;
⚈  Use a "prefix" index -- you lose some of the performance benefits.
⚈  Or... Stay with older version but perform 4 steps to raise the limit to 3072 bytes:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- (or COMPRESSED)

- http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

Rozwiązałem ten problem z:

varchar(200) 

zastąpiony przez

varchar(191)

wszystkie varchary, które mają więcej niż 200, zastępują je 191 lub ustawiają tekst.

Przeprowadziłem wyszukiwanie w tym temacie, ale w końcu wprowadziłem niestandardową zmianę

Dla środowiska roboczego MySQL 6.3.7 Dostępna jest graficzna międzyfazowa

1. Uruchom Workbench i wybierz połączenie.
2. Przejdź do zarządzania lub wystąpienia i wybierz Opcje Plik.
3. Jeśli Workbench poprosi Cię o pozwolenie na odczytanie pliku konfiguracyjnego, a następnie zezwól na to, naciskając dwa razy przycisk OK.
4. Na środku znajduje się okno pliku opcji administratora.
5. Przejdź do zakładki InnoDB i sprawdź przedrostek innodb_large_prefix, jeśli nie jest zaznaczony w sekcji Ogólne.
6. ustaw wartość opcji innodb_default_row_format na DYNAMIC.

W przypadku wersji poniżej 6.3.7 opcje bezpośrednie nie są dostępne, dlatego należy przejść do wiersza polecenia

1. Uruchom CMD jako administrator.
2. Idź do dyrektora, na którym jest zainstalowany serwer mysql. Większość przypadków znajduje się w folderze „C: \ Program Files \ MySQL \ MySQL Server 5.7 \ bin”, więc polecenie to „cd \” „cd Program Files \ MySQL \ MySQL Server 5.7 \ bin”.
3. Teraz uruchom polecenie mysql -u nazwa_użytkownika -p databasescheema Teraz poprosił o hasło odpowiedniego użytkownika. Podaj hasło i wprowadź polecenie mysql.
4. Musimy ustawić niektóre ustawienia globalne, wprowadzać poniższe polecenia jeden po drugim, ustawić globalny innodb_large_prefix = on; ustaw globalny format_pliku_wpodb = barracuda; set global innodb_file_per_table = true;
5. Teraz w końcu musimy zmienić ROW_FORMAT wymaganej tabeli domyślnie jej COMPACT, musimy ustawić ją na DYNAMIC.
6. użyj następującego polecenia alter table nazwa_tabeli ROW_FORMAT = DYNAMIC;
7. Gotowy

Dla laravel 5.7 do 6.0

Kroki do naśladowania

1. Idź do App\Providers\AppServiceProvider.php.
2. Dodaj to do dostawcy use Illuminate\Support\Facades\Schema;u góry.
3. Wewnątrz funkcji Boot Dodaj to Schema::defaultStringLength(191);

to wszystko, ciesz się.

zmień swoje zestawienie. Możesz użyć utf8_general_ci, który obsługuje prawie wszystkie

Właśnie przejście utf8mb4do utf8tworzenia tabel rozwiązało mój problem. Na przykład: CREATE TABLE ... DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;do CREATE TABLE ... DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;.

Aby to naprawić, działa to dla mnie jak urok.

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

Opierając się na kolumnie podanej poniżej, te 2 kolumny ciągów zmiennych używają utf8_general_cisortowania (utf8 sugerowany jest zestaw znaków).

W MySQL, utf8charset używa maksymalnie 3 bajtów na każdy znak. Dlatego musiałby przydzielić 500 * 3 = 1500 bajtów, czyli znacznie więcej niż 767 bajtów, na które pozwala MySQL. Dlatego pojawia się ten błąd 1071.

Innymi słowy, musisz obliczyć liczbę znaków na podstawie reprezentacji bajtów zestawu znaków, ponieważ nie każdy zestaw znaków jest reprezentacją pojedynczego bajtu (jak przypuszczałeś). IE utf8w MySQL używa maksymalnie 3 bajtów na znak, 767 / 3≈255 znaków, a dla utf8mb4maksymalnie 4-bajtowej reprezentacji 767 / 4≈191 znaków.

Wiadomo również, że MySQL

column1 varchar(20) utf8_general_ci
column2  varchar(500) utf8_general_ci

Znalazłem to zapytanie przydatne w wykrywaniu, które kolumny mają indeks naruszający maksymalną długość:

SELECT
  c.TABLE_NAME As TableName,
  c.COLUMN_NAME AS ColumnName,
  c.DATA_TYPE AS DataType,
  c.CHARACTER_MAXIMUM_LENGTH AS ColumnLength,
  s.INDEX_NAME AS IndexName
FROM information_schema.COLUMNS AS c
INNER JOIN information_schema.statistics AS s
  ON s.table_name = c.TABLE_NAME
 AND s.COLUMN_NAME = c.COLUMN_NAME 
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.CHARACTER_MAXIMUM_LENGTH > 191 
  AND c.DATA_TYPE IN ('char', 'varchar', 'text')

Sprawdź, czy sql_modejest jak

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

jeśli tak, zmień na

sql_mode=NO_ENGINE_SUBSTITUTION

LUB

zrestartuj serwer zmieniając plik my.cnf (wstawiając następujące)

innodb_large_prefix=on

W moim przypadku miałem ten problem, kiedy tworzyłem kopię zapasową bazy danych przy użyciu znaków wyjściowych / wejściowych przekierowania linuxa. Dlatego zmieniam składnię, jak opisano poniżej. PS: za pomocą terminala Linux lub Mac.

Kopia zapasowa (bez przekierowania>)

# mysqldump -u root -p databasename -r bkp.sql

Przywróć (bez <przekierowania)

# mysql -u root -p --default-character-set=utf8 databasename
mysql> SET names 'utf8'
mysql> SOURCE bkp.sql

Błąd „Określony klucz był zbyt długi; maksymalna długość klucza to 767 bajtów” po prostu zniknął.

Długości indeksu i MySQL / MariaDB

Laravel domyślnie używa zestawu znaków utf8mb4 , który obejmuje obsługę przechowywania „emoji” w bazie danych. Jeśli używasz wersji MySQL starszej niż wersja 5.7.7 lub MariaDB starszej niż wersja 10.2.2, może być konieczne ręczne skonfigurowanie domyślnej długości ciągu generowanego przez migracje, aby MySQL mógł utworzyć dla nich indeksy. Możesz to skonfigurować, wywołując metodę Schema :: defaultStringLength w swoim AppServiceProvider:

use Illuminate\Support\Facades\Schema;

/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    Schema::defaultStringLength(191);
}

Alternatywnie możesz włączyć opcję innodb_large_prefix dla swojej bazy danych. Instrukcje dotyczące prawidłowego włączania tej opcji znajdują się w dokumentacji bazy danych.

Odnośnik z bloga: https://www.scratchcode.io/specified-key-too-long-error-in-laravel/

Odniesienie z oficjalnej dokumentacji laravel: https://laravel.com/docs/5.7/migrations

Jeśli tworzysz coś takiego:

CREATE TABLE IF NOT EXISTS your_table (
  id int(7) UNSIGNED NOT NULL AUTO_INCREMENT,
  name varchar(256) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY name (name)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ROW_FORMAT=FIXED;

powinno być coś takiego

CREATE TABLE IF NOT EXISTS your_table (
      id int(7) UNSIGNED NOT NULL AUTO_INCREMENT,
      name varchar(256) COLLATE utf8mb4_bin NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ROW_FORMAT=FIXED;

ale musisz sprawdzić unikalność tej kolumny z kodu lub dodać nową kolumnę jako MD5 lub SHA1 kolumny varchar

Z powodu ograniczeń prefiksów ten błąd wystąpi. 767 bajtów to podane ograniczenie prefiksu dla tabel InnoDB w wersjach MySQL starszych niż 5.7. Dla tabel MyISAM ma 1000 bajtów. W MySQL w wersji 5.7 i wyższych limit ten został zwiększony do 3072 bajtów.

Uruchomienie poniższej usługi w usłudze powodującej błąd powinno rozwiązać problem. To musi być uruchomione w CLI MYSQL.

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=on;
SET GLOBAL innodb_large_prefix=on;

Zmień CHARSET pola indeksu reklamacji na „latin1”,
tj. ALTER TABELA tbl ZMIEŃ myfield myfield varchar (600) ZNAK ZESTAWU latin1 DEFAULT NULL;
latin1 zajmuje jeden bajt na jeden znak zamiast czterech

Jeśli zmieniłeś się innodb_log_file_sizeostatnio, spróbuj przywrócić poprzednią wartość, która działała.