Erreur MySQL : spécification de la clé sans longueur de clé

Question

J'ai une table avec une clé primaire qui est une varchar(255). Des cas se sont présentés où 255 caractères ne sont pas suffisants. J'ai essayé de changer le champ en texte, mais j'obtiens l'erreur suivante:

COLONNE BLOB/TEXT 'message_id' utilisée dans la spécification de la clé sans longueur de clé

Comment puis-je résoudre ce problème?

éditer: Je devrais également souligner que cette table a une clé primaire composite avec plusieurs colonnes.

Answer 1

L'erreur se produit parce que MySQL ne peut indexer que les premiers caractères N d'une colonne BLOB ou TEXT. Ainsi, l'erreur se produit principalement lorsqu'il y a un champ/colonne de type TEXT ou BLOB ou ceux appartenant aux types TEXT ou BLOB tels que TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT et LONGTEXT que vous essayez de définir comme clé primaire ou index. Avec un BLOB ou TEXT complet sans la valeur de longueur, MySQL ne peut garantir l'unicité de la colonne car sa taille est variable et dynamique. Ainsi, lors de l'utilisation des types BLOB ou TEXT en tant qu'index, la valeur de N doit être fournie afin que MySQL puisse déterminer la longueur de la clé. Cependant, MySQL ne prend pas en charge une limite de longueur de clé sur TEXT ou BLOB. TEXT(88) ne fonctionnera tout simplement pas.

L'erreur apparaîtra également lorsque vous essayez de convertir une colonne de table d'un type non-TEXT et non-BLOB comme VARCHAR et ENUM en un type TEXT ou BLOB, la colonne étant déjà définie comme contraintes uniques ou index. La commande SQL Alter Table échouera.

La solution au problème est de supprimer la colonne TEXT ou BLOB de l'index ou de la contrainte unique ou de définir un autre champ comme clé primaire. Si vous ne pouvez pas le faire et que vous souhaitez imposer une limite à la colonne TEXT ou BLOB, essayez d'utiliser le type VARCHAR et d'imposer une limite de longueur dessus. Par défaut, VARCHAR est limité à un maximum de 255 caractères et sa limite doit être spécifiée implicitement dans une parenthèse juste après sa déclaration, c'est-à-dire que VARCHAR(200) le limitera à 200 caractères seulement.

Parfois, même si vous n'utilisez pas de type lié à TEXT ou BLOB dans votre table, l'Erreur 1170 peut également apparaître. Cela se produit dans une situation telle que lorsque vous spécifiez une colonne VARCHAR comme clé primaire, mais que vous définissez incorrectement sa longueur ou sa taille de caractères. VARCHAR ne peut accepter que jusqu'à 256 caractères, donc toute chose telle que VARCHAR(512) forcera MySQL à convertir automatiquement VARCHAR(512) en un type de données SMALLTEXT, ce qui échouera ensuite avec l'erreur 1170 sur la longueur de la clé si la colonne est utilisée comme clé primaire ou index unique ou non unique. Pour résoudre ce problème, spécifiez une taille inférieure à 256 pour le champ VARCHAR.

Référence : Erreur MySQL 1170 (42000) : colonne BLOB/TEXT utilisée dans la spécification de clé sans longueur de clé

Answer 2

Vous devriez définir quelle partie principale d'une colonne TEXT vous souhaitez indexer.

InnoDB a une limitation de 768 octets par clé d'index et vous ne pourrez pas créer un index plus long que cela.

Cela fonctionnera bien:

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

Notez que la valeur maximale de la taille de la clé dépend du jeu de caractères de la colonne. C'est 767 caractères pour un jeu de caractères mono-octet comme LATIN1 et seulement 255 caractères pour UTF8 (MySQL utilise uniquement BMP qui nécessite au maximum 3 octets par caractère)

Si vous avez besoin que l'ensemble de votre colonne soit la PRIMARY KEY, calculez le hash SHA1 ou MD5 et utilisez-le comme PRIMARY KEY.

Answer 3

Vous pouvez spécifier la longueur de la clé dans la requête alter table, quelque chose comme :

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

Answer 4

Ne pas avoir de longues valeurs comme clé primaire. Cela détruirait vos performances. Consultez le manuel de MySQL, section 13.6.13 'Optimisation des performances et dépannage d'InnoDB'.

À la place, utilisez une clé int de substitution comme clé primaire (avec auto_increment), et votre clé longue en tant que clé UNIQUE secondaire.

Answer 5

Ajoutez une autre colonne varChar(255) (avec par défaut une chaîne vide non nulle) pour contenir le dépassement lorsque 255 caractères ne suffisent pas, et modifiez cette PK pour utiliser les deux colonnes. Cela ne semble pas être un schéma de base de données bien conçu cependant, et je recommanderais de faire appel à un modélisateur de données pour examiner ce que vous avez en vue de le refondre pour une Normalisation plus poussée.