概述

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

1、导出所有索引

包括PRIMARY KEY和INDEX

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 = 'FSL_ATT_UAT' 
GROUP BY
	TABLE_NAME,
	INDEX_NAME 
ORDER BY
	TABLE_NAME ASC,
	INDEX_NAME ASC

2、不包括PRIMARY KEY，只包含INDEX

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 = 'fsl_att_uat' 
	AND UPPER( INDEX_NAME ) != 'PRIMARY' 
GROUP BY
	TABLE_NAME,
	INDEX_NAME 
ORDER BY
	TABLE_NAME ASC,
	INDEX_NAME ASC

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

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