Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to batch migrate table indexes in MySQL database. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

1. Export all indexes

Including PRIMARY KEY and 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

two。 Does not include PRIMARY KEY, only contains 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

The above is how to batch migrate table indexes in the MySQL database shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.