項目迭代長了之后由缆，生產(chǎn)環(huán)境和測試環(huán)境創(chuàng)建的索引可能存在不一致情況，需要分別導(dǎo)出來進(jìn)行比對猾蒂，補(bǔ)全缺失的索引均唉。網(wǎng)上好多文章的腳本執(zhí)行都有問題，下面這個腳本親測通過肚菠，大家可放心使用浸卦。
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 = '數(shù)據(jù)庫名'
GROUP BY
TABLE_NAME,
INDEX_NAME
ORDER BY
TABLE_NAME ASC,
INDEX_NAME ASC
