Comment convertir SHOW INDEX en ALTER TABLE pour ajouter un index dans MySQL

Question

J'ai exécuté SHOW INDEX sur la table et voici le résultat que j'ai obtenu :

Table: logfile
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 759103
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

Sur la base de ces informations, comment construire l'instruction ALTER pour ajouter un index à la table ?

Answer 1

J'ai développé l'excellente réponse de Bill ci-dessus. Les options de sortie ont été étendues pour inclure ADD PRIMARY KEY, ADD UNIQUE INDEX ou ADD INDEX

select concat('ALTER TABLE ', table_schema, '.', table_name, ' ADD ', 
  if(index_name = 'PRIMARY', 'PRIMARY KEY ', if(non_unique, 'INDEX ', 'UNIQUE INDEX ')), 
  if (index_name = 'PRIMARY','', index_name), ' (', group_concat('', column_name, '' order by seq_in_index), ');') 
  as 'alter table statement'
from information_schema.statistics 
where table_schema = '' 
group by table_schema, table_name, index_name, non_unique
order by table_schema, table_name, non_unique asc

Answer 2

SHOW INDEX ne contient pas suffisamment d'informations. Vous pouvez essayer ceci :

select concat('ALTER TABLE `', table_schema, '`.`', table_name, '` ADD ', 
  if(non_unique, '', 'UNIQUE '), 'INDEX `', index_name, '` (', 
  group_concat('`', column_name, '`' order by seq_in_index), ');') as _ddl
from information_schema.statistics 
where (table_schema, table_name) = (?, ?) 
group by table_schema, table_name, index_name, non_unique;

Vous devez remplir les noms de schéma et de table que j'ai laissés comme espaces réservés ?, ?

C'est juste pour vous aider à démarrer. Je sais qu'il ne prend pas en compte certaines options, notamment l'indexation des préfixes, l'indexation des expressions ou les annotations. Je vais le laisser comme exercice au lecteur.

Il génère également une instruction alter table distincte pour chaque index. Si vous souhaitez créer une table alternée qui ajoute tous les index, utilisez une sous-requête pour générer une liste de colonnes pour chaque index, puis group_concat() pour les combiner dans la requête externe.