wake-up-neo.com

MySQL-Fehler: Schlüsselangabe ohne Schlüssellänge

Ich habe eine Tabelle mit einem Primärschlüssel, der ein Varchar (255) ist. In einigen Fällen sind 255 Zeichen nicht ausreichend. Ich habe versucht, das Feld in einen Text zu ändern, erhalte jedoch die folgende Fehlermeldung:

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

wie kann ich das beheben?

edit: Ich sollte auch darauf hinweisen, dass diese Tabelle einen zusammengesetzten Primärschlüssel mit mehreren Spalten hat.

298
GSto

Der Fehler tritt auf, weil MySQL nur die ersten N-Zeichen einer BLOB- oder TEXT -Spalte indizieren kann. Der Fehler tritt also hauptsächlich dann auf, wenn es einen Feld-/Spaltentyp von TEXT oder BLOB gibt oder die zu TEXT oder BLOB gehören, wie TINYBLOB, MEDIUMBLOB , LONGBLOB, TINYTEXT, MEDIUMTEXT und LONGTEXT, dass Sie versuchen, einen Primärschlüssel oder einen Index zu erstellen. Mit voller BLOB oder TEXT ohne den Längenwert kann MySQL die Eindeutigkeit der Spalte nicht garantieren, da sie variabel und dynamisch ist. Wenn Sie also BLOB oder TEXT als Index verwenden, muss der Wert N angegeben werden, damit MySQL die Schlüssellänge bestimmen kann. MySQL unterstützt jedoch keine Schlüssellängenbegrenzung für TEXT oder BLOB. TEXT(88) funktioniert einfach nicht.

Der Fehler wird auch angezeigt, wenn Sie versuchen, eine Tabellenspalte vom Typ non-TEXT und non-BLOB wie VARCHAR und ENUM in den Typ TEXT oder BLOB zu konvertieren, wobei die Spalte bereits vorhanden ist als eindeutige Nebenbedingungen oder Index definiert. Der SQL-Befehl "Alter Table" schlägt fehl.

Die Lösung des Problems besteht darin, die Spalte TEXT oder BLOB aus dem Index oder der eindeutigen Einschränkung zu entfernen oder ein anderes Feld als Primärschlüssel festzulegen. Wenn Sie dies nicht tun können und die Spalte TEXT oder BLOB mit einem Grenzwert versehen möchten, verwenden Sie den Typ VARCHAR und setzen Sie einen Längenlimit. Standardmäßig ist VARCHAR auf maximal 255 Zeichen begrenzt, und sein Grenzwert muss unmittelbar nach der Deklaration implizit in einer Klammer angegeben werden, d. H. VARCHAR(200) begrenzt ihn auf nur 200 Zeichen.

Auch wenn Sie in Ihrer Tabelle keinen verwandten Typ TEXT oder BLOB verwenden, wird möglicherweise auch der Fehler 1170 angezeigt. Dies geschieht in einer Situation, in der Sie beispielsweise VARCHAR als Primärschlüssel angeben, die Länge oder Zeichengröße jedoch falsch einstellen. VARCHAR kann nur bis zu 256 Zeichen akzeptieren. Daher zwingt MySQL, wie VARCHAR(512), VARCHAR(512) automatisch in einen SMALLTEXT-Datentyp, der bei Verwendung der Spalte mit dem Fehler 1170 fehlschlägt als Primärschlüssel oder eindeutiger oder nicht eindeutiger Index. Um dieses Problem zu lösen, geben Sie als Größe für das Feld VARCHAR eine Zahl unter 256 ein.

Referenz: MySQL-Fehler 1170 (42000): BLOB/TEXT-Spalte, die in der Schlüsselspezifikation ohne Schlüssellänge verwendet wird

490
OMG Ponies

Sie sollten definieren, welcher führende Teil einer TEXT-Spalte Sie indizieren möchten.

InnoDB hat eine Beschränkung auf 768 Bytes pro Indexschlüssel, und Sie können keinen längeren Index erstellen.

Das wird gut funktionieren:

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

Beachten Sie, dass der maximale Wert der Schlüsselgröße vom Spaltenzeichensatz abhängt. Es sind 767 Zeichen für einen Single-Byte-Zeichensatz wie LATIN1 und nur 255 Zeichen für UTF8 (MySQL verwendet nur BMP, was höchstens 3 Byte pro Zeichen erfordert.

Wenn Sie Ihre gesamte Spalte als PRIMARY KEY verwenden möchten, berechnen Sie SHA1 oder MD5-Hash und verwenden Sie sie als PRIMARY KEY.

73
Quassnoi

Sie können die Schlüssellänge in der Anforderungstabelle ändern, etwa:

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

MySQL lässt die Indizierung eines vollen Werts der Spalten BLOB, TEXT und long VARCHAR nicht zu, da die darin enthaltenen Daten sehr groß sein können und der DB-Index implizit groß ist.

MySQL erfordert, dass Sie zuerst N Zeichen definieren, die indiziert werden sollen. Der Trick besteht darin, eine Zahl N zu wählen, die lang genug ist, um eine gute Selektivität zu erreichen, aber kurz genug, um Platz zu sparen. Das Präfix sollte lang genug sein, um den Index nahezu so nützlich zu machen, als wäre es die Indexierung der gesamten Spalte.

Bevor wir weiter gehen, definieren wir einige wichtige Begriffe. Indexselektivität ist das Verhältnis von die verschiedenen indizierten Gesamtwerte und die Gesamtzahl der Zeilen. Hier ist ein Beispiel für die Testtabelle:

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

Wenn wir nur das erste Zeichen indizieren (N = 1), sieht die Indextabelle wie die folgende Tabelle aus:

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

In diesem Fall ist die Indexselektivität gleich IS = 1/3 = 0,33.

Lassen Sie uns nun sehen, was passiert, wenn wir die Anzahl indizierter Zeichen auf zwei erhöhen (N = 2).

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

In diesem Szenario ist IS = 2/3 = 0,66, was bedeutet, dass wir die Indexselektivität erhöht haben, aber wir haben auch die Größe des Index erhöht. Der Trick besteht darin, die minimale Anzahl N zu finden, die zu maximal Indexselektivität führt. 

Es gibt zwei Ansätze, die Sie für Ihre Datenbanktabelle berechnen können. Ich werde auf dem diesem Datenbank-Dump demonstrieren.

Angenommen, wir möchten dem Index die Spalte Nachname in der Tabelle Mitarbeiter hinzufügen, und wir möchten die kleinste Zahl N definieren, die den besten Index ergibt Selektivität.

Zuerst lassen Sie uns die häufigsten Nachnamen ermitteln:

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)

Wie Sie sehen können, ist der Nachname Baba der häufigste. Jetzt werden wir die am häufigsten vorkommenden last_name-Präfixe finden, beginnend mit fünf Buchstaben.

+-----+--------+
| 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)

Es gibt viel mehr Vorkommen von jedem Präfix, was bedeutet, dass wir die Anzahl N erhöhen müssen, bis die Werte fast die gleichen sind wie im vorherigen Beispiel.

Hier sind Ergebnisse für 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    |
+-----+-----------+

Hier sind Ergebnisse für 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)

Das sind sehr gute Ergebnisse. Dies bedeutet, dass wir einen Index für die Spalte last_name erstellen können, wobei nur die ersten 10 Zeichen indiziert werden. In der Tabellendefinitionsspalte ist last_name als VARCHAR(16) definiert. Dies bedeutet, dass wir pro Eintrag 6 Byte (oder mehr, wenn im Nachname UTF8-Zeichen enthalten sind) gespeichert haben. In dieser Tabelle gibt es 1637 verschiedene Werte, die mit 6 Bytes multipliziert werden, etwa 9 KB, und stellen Sie sich vor, wie diese Anzahl zunehmen würde, wenn unsere Tabelle Millionen Zeilen enthält.

Sie können andere Methoden zur Berechnung der Anzahl von N in meinem Beitrag Präfix-Indizes in MySQL nachlesen.

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

NOTE: 767 ist die Anzahl der Zeichen, bis zu der MySQL Spalten indiziert, während Blob/Text-Indizes verwendet werden

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

4
Abhishek Goel

Keine langen Werte als Primärschlüssel. Das wird deine Leistung zerstören. Weitere Informationen finden Sie im mysql-Handbuch, Abschnitt 13.6.13 'InnoDB Performance Tuning and Troubleshooting'.

Verwenden Sie stattdessen einen Ersatzschlüssel int als Primärschlüssel (mit auto_increment) und Ihren langen Schlüssel als sekundären UNIQUE.

3
Per Lindberg

Eine weitere hervorragende Methode, um damit umzugehen, besteht darin, ein TEXT-Feld ohne die eindeutige Einschränkung zu erstellen und ein gleichgeordnetes VARCHAR-Feld hinzuzufügen, das eindeutig ist und einen Digest (MD5, SHA1 usw.) des TEXT-Felds enthält. Berechnen und speichern Sie den Digest über das gesamte TEXT-Feld, wenn Sie das TEXT-Feld einfügen oder aktualisieren. Dann haben Sie eine Eindeutigkeitsbeschränkung für das gesamte TEXT-Feld (nicht für einen führenden Teil), die schnell durchsucht werden kann.

2
par

Fügen Sie eine weitere varChar (255) -Spalte hinzu (mit dem Standardwert als leere Zeichenfolge nicht NULL), um den Überlauf zu speichern, wenn 255 Zeichen nicht ausreichen, und ändern Sie diesen PK so, dass beide Spalten verwendet werden. Dies hört sich jedoch nicht nach einem gut entworfenen Datenbankschema an, und ich würde empfehlen, einen Datenmodellierer dazu zu bringen, sich das anzusehen, was Sie im Hinblick auf ein Refactoring für eine weitere Normalisierung haben.

1
Charles Bretana

Niemand hat es bisher erwähnt ... mit utf8mb4, das 4-Byte ist und auch Emoticons speichern kann (wir sollten niemals mehr 3-Byte-utf8 verwenden), und wir können Fehler vermeiden, wie Incorrect string value: \xF0\x9F\x98\..., und wir sollten keine typischen VARCHAR (255) aber eher VARCHAR (191) weil in utf8mb4 und VARCHAR (255) derselbe Teil der Daten außerhalb der Seite gespeichert wird und Sie keinen Index für die Spalte VARCHAR (255) erstellen können, sondern für VARCHAR (191). Dies liegt daran, dass die maximale indizierte Spaltengröße für ROW_FORMAT = COMPACT oder ROW_FORMAT = REDUNDANT 767 Byte beträgt.

Für neuere Zeilenformate ROW_FORMAT = DYNAMIC oder ROW_FORMAT = COMPRESSED (für die ein neueres Dateiformat erforderlich ist innodb_file_format = Barracuda nicht ältere Antilope), ist die maximale indizierte Spaltengröße 3072. Sie ist verfügbar, da MySQL> = 5.6.3 (standardmäßig für deaktiviert ist) MySQL <= 5.7.6 und standardmäßig aktiviert für MySQL> = 5.7.7). In diesem Fall können wir VARCHAR (768) für utf8mb4 (oder VARCHAR (1024) für alte utf8) für indizierte Spalten verwenden. Die Option innodb_large_prefix ist seit 5.7.7 veraltet, da das Verhalten von MySQL 8 integriert ist (in dieser Version ist die Option entfernt).

0
mikep

Wenn Sie den Index in diesem Feld verwenden möchten, sollten Sie auch die MyISAM-Speicher-Engine und den Indextyp FULLTEXT verwenden.

0

Die Lösung des Problems ist, dass Sie in Ihrer CREATE TABLE-Anweisung die Einschränkung UNIQUE ( problemtextfield(300) ) hinzufügen können, nachdem die Spalte Definitionen erstellt hat, um beispielsweise eine key-Länge von 300-Zeichen für ein TEXT-Feld anzugeben. Dann müssten die ersten 300-Zeichen des problemtextfieldTEXT-Felds eindeutig sein, und alle Unterschiede, die danach auftreten, werden nicht berücksichtigt.

0
Who Dunnit

Gehen Sie zu mysql edit table-> ändern Sie den Spaltentyp in varchar(45).

0
Manoj Mishra

Ich habe diesen Fehler beim Hinzufügen eines Index zu einer Tabelle mit Textspalten erhalten. Sie müssen die Größe angeben, die Sie für jeden Texttyp verwenden möchten.

Geben Sie die Größe in die Klammer ein ()

Wenn zu viele Bytes verwendet werden, können Sie eine Größe in den Klammern für varchar angeben, um den für die Indizierung verwendeten Wert zu verringern. Dies gilt auch dann, wenn Sie eine Größe für einen Typ wie varchar (1000) angegeben haben. Sie müssen keine neue Tabelle erstellen, wie andere gesagt haben.

Index wird hinzugefügt

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

Hinzufügen eines eindeutigen Index

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

Sie müssen den Spaltentyp für die Indizierung in varchar oder integer ändern.

0
Manoj Mishra

Verwenden Sie so

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