select
CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 'ADD ', if(NON_UNIQUE = 1, case UPPER(INDEX_TYPE) when 'FULLTEXT' then 'FULLTEXT INDEX' when 'SPATIAL' then 'SPATIAL INDEX' else CONCAT('INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE) end, if(UPPER(INDEX_NAME) = 'PRIMARY', CONCAT('PRIMARY KEY USING ', INDEX_TYPE), CONCAT('UNIQUE INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE))), '(', GROUP_CONCAT(distinct CONCAT('`', COLUMN_NAME, '`') order by SEQ_IN_INDEX asc separator ', '), ');') as 'Show_Add_Indexes'
from
information_schema.STATISTICS
where
TABLE_SCHEMA = '库名'
and TABLE_NAME = '表名'
group by
TABLE_NAME ,
INDEX_NAME
order by
TABLE_NAME asc ,
INDEX_NAME asc
主要就是通过对information_schema.STATISTICS进行一些concat操作,放在这里记录一下,怕回头忘记