概述
由于业务需要,需迁移测试环境上的索引到正式库上。下面简单记录下迁移过程中的脚本。
1、导出所有索引
包括PRIMARY KEY和INDEX
SELECTCONCAT('ALTER TABLE `',TABLE_NAME,'` ','ADD ',IF(NON_UNIQUE = 1,CASEUPPER( 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' FROMinformation_schema.STATISTICS WHERETABLE_SCHEMA = 'FSL_ATT_UAT' GROUP BYTABLE_NAME,INDEX_NAME ORDER BYTABLE_NAME ASC,INDEX_NAME ASC
![3695e79e15adb267ba28ca58e7147c6d.png](https://i-blog.csdnimg.cn/blog_migrate/b78ae3b3cbbb852f98680394e0a8407d.jpeg)
2、不包括PRIMARY KEY,只包含INDEX
SELECTCONCAT('ALTER TABLE `',TABLE_NAME,'` ','ADD ',IF(NON_UNIQUE = 1,CASEUPPER ( 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' FROMinformation_schema.STATISTICS WHERETABLE_SCHEMA = 'fsl_att_uat' AND UPPER( INDEX_NAME ) != 'PRIMARY' GROUP BYTABLE_NAME,INDEX_NAME ORDER BYTABLE_NAME ASC,INDEX_NAME ASC
![5de4e66da74f5a7c76afa79269769d76.png](https://i-blog.csdnimg.cn/blog_migrate/04e6c9b2465176d50914f3c082262da2.jpeg)
到正式库执行结果语句即可。
觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~
![c6e60be77d7bcfa144b55ac290edfb38.gif](https://i-blog.csdnimg.cn/blog_migrate/543057a3156ea0c18d676bf9d10e5752.gif)