由于业务需要，需要将测试环境上的索引迁移到正式数据库。下面简单记录一下迁移过程中的脚本。1.导出所有索引包PRIMARYKEY和INDEXSELECTCONCAT('ALTERTABLE`',TABLE_NAME,'`','ADD',IF(NON_UNIQUE=1,CASEUPPER(INDEX_TYPE)WHEN'FULLTEXT'THEN'FULLTEXTINDEX'WHEN'SPATIAL'THEN'SPATIALINDEX'ELSECONCAT('INDEX`',INDEX_NAME,'`USING',INDEX_TYPE)END,IF(??UPPER(INDEX_NAME)='PRIMARY',CONCAT('PRIMARYKEYUSING',INDEX_TYPE),CONCAT('UNIQUEINDEX',INDEX_NAME,'`USING',INDEX_TYPE))),'(',GROUP_CONCAT(DISTINCTCONCAT('`',COLUMN_NAME,'`')ORDERBYSEQ_IN_INDEXASCSEPARATOR','),');')AS'Show_Add_Indexes'FROMinformation_schema.STATISTICSWHERETABLE_SCHEMA='FSL_ATT_UAT'GROUPBYTABLE_NAME,INDEX_NAMEORDERBYTABLE_NAMEASC,INDEX_NAMEASC2。不包含PRIMARYKEY，只包含INDEXSELECTCONCAT('ALTERTABLE`',TABLE_NAME,'`','ADD',IF(NON_UNIQUE=1,CASEUPPER(INDEX_TYPE)WHEN'FULLTEXT'THEN'FULLTEXTINDEX'WHEN'SPATIAL'THEN'SPATIALINDEX'ELSECONCAT('INDEX`',INDEX_NAME,'`USING',INDEX_TYPE)END,IF(??UPPER(INDEX_NAME)='PRIMARY',CONCAT('PRIMARYKEYUSING',INDEX_TYPE),CONCAT('UNIQUEINDEX',INDEX_NAME,'`USING',INDEX_TYPE))),'(',GROUP_CONCAT(DISTINCTCONCAT('`',COLUMN_NAME,'`')ORDERBYSEQ_IN_INDEXASCSEPARATOR','),');')AS'Show_Add_Indexes'FROMinformation_schema.STATISTICSWHERETABLE_SCHEMA='fsl_att_uat'ANDUPPER(INDEX_NAME)!='PRIMARY'GROUPBYTABLE_NAME,INDEX_NAMEORDERBYTABLEAS,INDEX_NAMEORDERBYTABLE去官方库执行result语句
