MySQL导出所有Index和约束的方法

本文汇总了MySQL导出所有Index 和约束的方法，提供给大家以方便大家查询使用。具体如下：
1. 导出创建自增字段语句：

SELECTCONCAT（"ALTER TABLE `",TABLE_NAME,"` ","MODIFY COLUMN `",COLUMN_NAME,"` ",IF（UPPER（DATA_TYPE） = "INT",REPLACE（SUBSTRING_INDEX（UPPER（COLUMN_TYPE）,"）",1）,"INT","INTEGER"）,UPPER（COLUMN_TYPE））,"） UNSIGNED NOT NULL AUTO_INCREMENT;"）FROM information_schema.COLUMNSWHERE TABLE_SCHEMA = "source_database_name" ANDEXTRA = UPPER（"AUTO_INCREMENT"）ORDER BY TABLE_NAME ASC

2. 导出所有索引：

SELECTCONCAT（"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.STATISTICSWHERE TABLE_SCHEMA = "pbq"GROUP BY TABLE_NAME, INDEX_NAMEORDER BY TABLE_NAME ASC, INDEX_NAME ASC

3. 创建删除所有自增字段：

SELECTCONCAT（"ALTER TABLE `",TABLE_NAME,"` ","MODIFY COLUMN `",COLUMN_NAME,"` ",IF（UPPER（DATA_TYPE） = "INT",REPLACE（SUBSTRING_INDEX（UPPER（COLUMN_TYPE）,"）",1）,"INT","INTEGER"）,UPPER（COLUMN_TYPE））,"） UNSIGNED NOT NULL;"）FROM information_schema.COLUMNSWHERE TABLE_SCHEMA = "destination_database_name" ANDEXTRA = UPPER（"AUTO_INCREMENT"）ORDER BY TABLE_NAME ASC

4. 删除库所有索引：

SELECTCONCAT（"ALTER TABLE `",TABLE_NAME,"` ",GROUP_CONCAT（DISTINCTCONCAT（"DROP ",IF（UPPER（INDEX_NAME） = "PRIMARY","PRIMARY KEY",CONCAT（"INDEX `", INDEX_NAME, "`"）））SEPARATOR ", "）,";"）FROM information_schema.STATISTICSWHERE TABLE_SCHEMA = "destination_database_name"GROUP BY TABLE_NAMEORDER BY TABLE_NAME ASC

希望本文所述示例能够对大家有所帮助。