MySQL 導出所有索引與約束的 SQL 生成腳本（信息表法）

MySQL 導出所有索引與約束：基於 information_schema 的生成腳本

利用 information_schema.STATISTICS 與 COLUMNS 表，動態生成 CREATE INDEX、ALTER TABLE ... ADD PRIMARY KEY、DROP INDEX 等 DDL，避免手工拼寫，支援遷移、審計、版本控制。

1. 導出創建自增列語句

SELECT CONCAT(
  '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.COLUMNS
WHERE TABLE_SCHEMA = 'source_db' AND EXTRA = 'AUTO_INCREMENT'
ORDER BY TABLE_NAME;

2. 導出所有索引（含主鍵、唯一、普通、全文、空間）

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), ');'
) AS Create_Index_SQL
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'target_db'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME, INDEX_NAME;

3. 導出目標庫刪除自增屬性（遷移前準備）

SELECT CONCAT(
  '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.COLUMNS
WHERE TABLE_SCHEMA = 'dest_db' AND EXTRA = 'AUTO_INCREMENT'
ORDER BY TABLE_NAME;

4. 導出目標庫刪除所有索引（重建前清理）

SELECT CONCAT(
  'ALTER TABLE `', TABLE_NAME, '` ',
  GROUP_CONCAT(DISTINCT CONCAT(
    'DROP ', IF(UPPER(INDEX_NAME)='PRIMARY','PRIMARY KEY',CONCAT('INDEX `', INDEX_NAME, '`'))
  ) SEPARATOR ', '), ';'
) FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'dest_db'
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME;

使用建議

• 在測試庫先跑生成的 SQL，驗證無誤再應用到生產。
• 導出結果可導入版本控制（Git），實現 Schema 變更歷史追蹤。
• 大量索引操作建議低峰期執行，或使用 pt-online-schema-change 避免鎖表。

補充：KEY 與 INDEX 概念區別

• Key：邏輯層面約束（主鍵、外鍵、唯一鍵），保證數據完整性。
• Index：物理層面存取路徑（B+Tree 等），加速查詢。
• MySQL 中 PRIMARY KEY 自動創建 UNIQUE INDEX；UNIQUE KEY 即唯一約束+唯一索引；KEY/INDEX 同義（普通索引）。

總結

通過 information_schema 動態生成 DDL，實現索引/約束的可視化、可版本化、可自動化管理，是大規模庫表遷移與治理的基礎工具。