MySQL 導出所有索引與約束的 SQL 腳本大全

應用場景

資料庫遷移、架構同步、版本控制、災難恢復時，常需導出目標資料庫的所有索引、主鍵、唯一鍵、外鍵等約束定義。本文提供可直接執行的 SQL 查詢，輸出標準 ALTER TABLE 陳述式，支援 MyISAM、InnoDB 等引擎。

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;'
) AS `sql_stmt`
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'source_database_name'
  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 ASC SEPARATOR ', '), ');'
) AS `Show_Add_Indexes`
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'pbq'
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;'
) AS `sql_stmt`
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'destination_database_name'
  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 ', '
    ),
    ';'
) AS `sql_stmt`
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'destination_database_name'
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME;

使用建議

1. 將查詢結果匯出為 .sql 檔案。
2. 執行前務必備份目標資料庫。
3. 若涉及外鍵，建議先導出 information_schema.TABLE_CONSTRAINTS 與 REFERENTIAL_CONSTRAINTS 產生 DROP FOREIGN KEY / ADD CONSTRAINT 語句。
4. 在從庫或維護窗口執行，避免鎖表影響線上業務。

擴充：導出外鍵約束

SELECT CONCAT(
    'ALTER TABLE `', TABLE_NAME, '` ',
    'ADD CONSTRAINT `', CONSTRAINT_NAME, '` ',
    'FOREIGN KEY (`', COLUMN_NAME, '`) ',
    'REFERENCES `', REFERENCED_TABLE_NAME, '` (`', REFERENCED_COLUMN_NAME, '`) ',
    'ON DELETE ', DELETE_RULE, ' ON UPDATE ', UPDATE_RULE, ';'
) AS `fk_sql`
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_db'
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME, CONSTRAINT_NAME;

結語

善用 information_schema 系統表，可快速生成標準化的 DDL 腳本，實現資料庫結構的版本化管理與自動化部署。建議將上述查詢封裝為 Shell/Python 腳本，納入 CI/CD 流程。