如何将SHOW INDEX转换为ALTER TABLE以在MySQL中添加索引

Question

我在桌子上执行了 SHOW INDEX ，这是我得到的输出：

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:

根据这些信息，如何构建 ALTER 语句以向表添加索引？

Answer 1

我已经扩展了比尔上面的好答案。输出选项已扩展为包括 ADD PRIMARY KEY、ADD UNIQUE INDEX 或 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 没有足够的信息。你可以试试这个：

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;

您需要填写我留下占位符 ?, ? 的架构和表名称。

这只是为了让您开始。我知道它不考虑一些选项，包括前缀索引、表达式索引或注释。我将把它作为练习留给读者。

它还会为每个索引生成一个单独的 alter table 语句。如果你想做一个alter table来添加所有索引，请使用子查询为每个索引生成列列表，然后group_concat()将它们组合在外部查询中。