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

Question

Ich habe eine Tabelle mit einem Primärschlüssel, der ein varchar(255) ist. Es sind einige Fälle aufgetreten, in denen 255 Zeichen nicht ausreichen. Ich habe versucht, das Feld in einen Text zu ändern, aber ich erhalte 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.

Answer 1

Der Fehler tritt auf, weil MySQL nur die ersten N Zeichen eines BLOBs indizieren kann oder TEXT Spalte. Der Fehler tritt also hauptsächlich auf, wenn ein Feld/Spaltentyp von TEXT oder BLOB oder diese gehören zu TEXT o BLOB Typen wie TINYBLOB , MEDIUMBLOB , LONGBLOB , TINYTEXT , MEDIUMTEXT y LONGTEXT dass Sie versuchen, einen Primärschlüssel oder Index zu erstellen. Bei vollständiger BLOB o TEXT ohne den Längenwert ist MySQL nicht in der Lage, die Eindeutigkeit der Spalte zu garantieren, da sie eine variable und dynamische Größe hat. Wenn Sie also BLOB o TEXT Typen als Index, muss der Wert von N angegeben werden, damit MySQL die Schlüssellänge bestimmen kann. MySQL unterstützt jedoch keine Begrenzung der Schlüssellänge für TEXT o BLOB . TEXT(88) einfach nicht funktionieren.

Der Fehler tritt auch auf, wenn Sie versuchen, eine Tabellenspalte von non-TEXT y non-BLOB Typ wie zum Beispiel VARCHAR y ENUM in TEXT o BLOB Typ, wobei die Spalte bereits als eindeutige Einschränkung oder Index definiert wurde. Der SQL-Befehl Alter Table schlägt fehl.

Die Lösung des Problems besteht darin, die TEXT o BLOB Spalte aus dem Index oder der eindeutigen Beschränkung zu entfernen oder ein anderes Feld als Primärschlüssel festzulegen. Wenn Sie das nicht tun können und eine Begrenzung für die TEXT o BLOB Spalte, versuchen Sie zu verwenden VARCHAR und eine Längenbeschränkung für sie festlegen. Standardmäßig, VARCHAR ist auf maximal 255 Zeichen begrenzt, und die Grenze muss implizit in einer Klammer direkt nach der Deklaration angegeben werden, d.h. VARCHAR(200) wird die Länge auf 200 Zeichen begrenzt.

Manchmal, auch wenn Sie keine TEXT o BLOB verwandte Typ in Ihrer Tabelle, kann auch der Fehler 1170 erscheinen. Dies geschieht zum Beispiel, wenn Sie Folgendes angeben VARCHAR Spalte als Primärschlüssel verwendet, aber ihre Länge oder Zeichengröße falsch eingestellt. VARCHAR kann nur bis zu 256 Zeichen akzeptieren, so dass alles wie VARCHAR(512) zwingt MySQL zur automatischen Konvertierung der VARCHAR(512) zu einer SMALLTEXT Datentyp, der anschließend mit Fehler 1170 bei der Schlüssellänge fehlschlägt, wenn die Spalte als Primärschlüssel oder eindeutiger oder nicht eindeutiger Index verwendet wird. Um dieses Problem zu lösen, geben Sie eine Zahl kleiner als 256 als Größe für VARCHAR Feld.

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

Answer 2

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

InnoDB hat eine Einschränkung von 768 Bytes pro Indexschlüssel und Sie können keinen Index erstellen, der länger ist als das.

Das wird gut funktionieren:

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

Beachten Sie, dass der Höchstwert für die Schlüsselgröße vom Zeichensatz der Spalte abhängt. Er ist 767 Zeichen für einen Ein-Byte-Zeichensatz wie LATIN1 und nur 255 Zeichen für UTF8 ( MySQL verwendet nur BMP was höchstens erfordert 3 Bytes pro Zeichen)

Wenn Sie Ihre gesamte Spalte als die PRIMARY KEY berechnen SHA1 o MD5 Hash und verwenden ihn als PRIMARY KEY .

Answer 3

Sie können die Schlüssellänge in der alter table-Anfrage angeben, etwa so:

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

Answer 4

MySQL verbietet die Indizierung eines vollen Wertes von BLOB , TEXT und lange VARCHAR Spalten, weil die Daten, die sie enthalten, riesig sein können und der DB-Index implizit groß sein wird, was bedeutet, dass der Index keinen Nutzen bringt.

MySQL verlangt, dass Sie die ersten N Zeichen definieren, die indiziert werden sollen, und der Trick besteht darin, eine Zahl N zu wählen, die lang genug ist, um gute Selektivität zu bieten, aber kurz genug, um Platz zu sparen. Das Präfix sollte lang genug sein, um den Index fast so nützlich zu machen, wie er wäre, wenn Sie die ganze Spalte indizieren würden.

Bevor wir fortfahren, sollten wir einige wichtige Begriffe definieren. Index-Selektivität ist das Verhältnis von die Gesamtzahl der eindeutigen indizierten Werte und die Gesamtzahl der Zeilen . Hier ist ein Beispiel für eine Testtabelle:

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

Wenn wir nur das erste Zeichen indizieren (N=1), dann 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.

Sehen wir uns nun an, was passiert, wenn wir die Anzahl der indizierten 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 einer maximalen Indexselektivität .

Es gibt zwei Möglichkeiten, wie Sie Berechnungen für Ihre Datenbanktabelle durchführen können. Ich werde eine Demonstration über die dieser Datenbank-Dump .

Nehmen wir an, wir wollen eine Spalte hinzufügen letzter_name in Tabelle Mitarbeiter zum Index, und wir wollen die kleinste Zahl definieren N die die beste Indexselektivität ergibt.

Zunächst wollen wir 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 ist die häufigste. Jetzt werden wir die am häufigsten vorkommenden letzter_name Präfixe, beginnend mit Präfixen aus 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 jedes Präfixes, was bedeutet, dass wir die Anzahl N erhöhen müssen, bis die Werte fast die gleichen sind wie im vorherigen Beispiel.

Hier die 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 die 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. Das bedeutet, dass wir einen Index für die Spalte last_name mit Indizierung nur der ersten 10 Zeichen. In der Tabellendefinitionsspalte last_name ist definiert als VARCHAR(16) und das bedeutet, dass wir 6 Bytes (oder mehr, wenn der Nachname UTF8-Zeichen enthält) pro Eintrag eingespart haben. In dieser Tabelle gibt es 1637 eindeutige Werte, multipliziert mit 6 Bytes ergibt das etwa 9 KB, und stellen Sie sich vor, wie diese Zahl wachsen würde, wenn unsere Tabelle Millionen von Zeilen enthält.

Sie können andere Methoden zur Berechnung der Anzahl der N in meinem Beitrag Vorangestellte Indizes in MySQL .

Answer 5

Ich habe diese Fehlermeldung erhalten, als ich einen Index zu einer Tabelle mit Textspalten hinzugefügt habe. Sie müssen die Größe angeben, die Sie für jede Textart verwenden möchten.

Geben Sie die Größe in Klammern ( ) an.

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

Hinzufügen des Index

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