#1071 - La clé spécifiée était trop longue ; la longueur maximale de la clé est de 1000 octets.

Question

Je sais qu'il a déjà été répondu à des questions portant ce titre, mais je vous invite à poursuivre votre lecture. J'ai lu attentivement toutes les autres questions/réponses sur cette erreur avant de poster.

J'obtiens l'erreur ci-dessus pour la requête suivante :

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;

Quelqu'un sait-il pourquoi et comment le réparer ? Le problème est que cette même requête fonctionne parfaitement sur ma machine locale, et fonctionnait aussi bien sur mon hôte précédent. Il s'agit d'un projet mature - phpdevshell - donc je suppose que ces personnes savent ce qu'elles font, mais on ne sait jamais.

Tout indice sera apprécié.

J'utilise phpMyAdmin.

Answer 1

Comme le dit @Devart, la longueur totale de votre index est trop longue.

La réponse courte est que vous ne devriez pas indexer des colonnes VARCHAR aussi longues de toute façon, car l'index sera très volumineux et inefficace.

La meilleure pratique consiste à utiliser indices de préfixe donc vous n'indexez qu'une sous-chaîne gauche des données. La plupart de vos données seront de toute façon beaucoup plus courtes que 255 caractères.

Vous pouvez déclarer une longueur de préfixe par colonne lorsque vous définissez l'index. Par exemple :

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

Mais quelle est la meilleure longueur de préfixe pour une colonne donnée ? Voici une méthode pour le découvrir :

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

Il vous indique la proportion de lignes qui n'ont pas plus d'une longueur de chaîne donnée dans le champ menu_link colonne. Vous pouvez obtenir un résultat comme celui-ci :

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

Cela vous indique que 80 % de vos chaînes de caractères ont moins de 20 caractères et que toutes vos chaînes de caractères ont moins de 50 caractères. Il n'est donc pas nécessaire d'indexer plus qu'une longueur de préfixe de 50, et encore moins d'indexer la longueur totale de 255 caractères.

PS : Le INT(1) y INT(32) Les types de données indiquent un autre malentendu à propos de MySQL. L'argument numérique n'a aucun effet lié au stockage ou à la plage de valeurs autorisées pour la colonne. INT est toujours de 4 octets, et il autorise toujours des valeurs comprises entre -2147483648 et 2147483647. L'argument numérique concerne le remplissage des valeurs pendant l'affichage, qui n'a aucun effet à moins que vous n'utilisiez l'option ZEROFILL option.

Answer 2

Cette erreur signifie que la longueur de l'index index est supérieure à 1000 octets. MySQL et les moteurs de stockage peuvent avoir cette restriction. J'ai obtenu une erreur similaire sur MySQL 5.5 - 'Specified key was too long ; max key length is 3072 bytes' lorsque j'ai exécuté ce script :

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 est multi-octets, et la longueur de la clé est calculée de la façon suivante : 500 * 3 * 6 = 9000 octets.

Mais attention, la prochaine requête fonctionne !

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;

...parce que j'ai utilisé CHARSET=latin1, dans ce cas la longueur de la clé est de 500 * 6 = 3000 octets.

Answer 3

J'ai eu ce problème et je l'ai résolu de la manière suivante :

Cause

Il y a un bug connu avec MySQL lié à MyISAM, le caractère UTF8 et les index que vous pouvez vérifier ici.

Résolution

• Assurez-vous que MySQL est configuré avec le moteur de stockage InnoDB.

• Modifiez le moteur de stockage utilisé par défaut afin que les nouvelles tables soient toujours créées de manière appropriée :

  set GLOBAL storage_engine='InnoDb';

• Pour MySQL 5.6 et plus, utilisez ce qui suit :

  SET GLOBAL default_storage_engine = 'InnoDB';

• Et enfin, assurez-vous que vous suivez les instructions fournies dans le document Migrer vers MySQL .

Référence

Answer 4

Exécutez cette requête avant de créer ou de modifier une table.

SET @@global.innodb_large_prefix = 1;

ceci fixera la longueur maximale de la clé à 3072 octets

Answer 5

Si vous utilisez Laravel 7 ou Laravel 8, allez dans config/database.php

'engine' => 'innoDb',

qui devrait fonctionner notamment en utilisant Wamp ou Xampp.