concat 索引_记一次生产mysql数据库批量迁移表索引

概述

由于业务需要，需迁移测试环境上的索引到正式库上。下面简单记录下迁移过程中的脚本。

---

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

---

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

到正式库执行结果语句即可。

---

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~