您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 判斷資料表欄位與索引是否存在的存儲過程方案

編輯：MySQL 綜合教學

利用資訊結構表與存儲過程實現結構變更的冪等操作

在資料庫結構遷移或自動化部署中，頻繁需要「若不存在則建立」的冪等操作。MySQL 雖無原生 IF NOT EXISTS 擴充語法支援 ALTER TABLE ADD COLUMN/INDEX，但可透過查詢 information_schema 結合存儲過程優雅解決。

核心原理

MySQL 的 information_schema 資料庫保存著所有元數據：

COLUMNS 表：記錄所有資料表欄位資訊
STATISTICS 表：記錄所有索引資訊

透過查詢這兩張表，配合存儲過程的流程控制，即可實現條件式結構變更。

案例一：判斷欄位是否存在並條件新增

DROP PROCEDURE IF EXISTS schema_change;
DELIMITER //
CREATE PROCEDURE schema_change()
BEGIN
    DECLARE CurrentDatabase VARCHAR(64);
    SELECT DATABASE() INTO CurrentDatabase;
    
    IF NOT EXISTS (
        SELECT * FROM information_schema.columns 
        WHERE table_schema = CurrentDatabase 
        AND table_name = 'rtc_order' 
        AND column_name = 'IfUpSend'
    ) THEN
        ALTER TABLE rtc_order 
        ADD COLUMN `IfUpSend` BIT NOT NULL DEFAULT 0 COMMENT '是否上傳';
    END IF;
END//
DELIMITER ;
CALL schema_change();

關鍵點解析

DECLARE CurrentDatabase VARCHAR(64)：儲存當前資料庫名，避免硬編碼
SELECT DATABASE() INTO CurrentDatabase：動態獲取上下文資料庫
information_schema.columns 過濾條件：資料庫名 + 資料表名 + 欄位名三重匹配
DEFAULT 0：Bit 型別需顯式指定預設值（原文遺漏）

案例二：判斷索引是否存在並條件新增

DROP PROCEDURE IF EXISTS schema_change;
DELIMITER //
CREATE PROCEDURE schema_change()
BEGIN
    DECLARE CurrentDatabase VARCHAR(64);
    SELECT DATABASE() INTO CurrentDatabase;
    
    IF NOT EXISTS (
        SELECT * FROM information_schema.statistics 
        WHERE table_schema = CurrentDatabase 
        AND table_name = 'rtc_phototype' 
        AND index_name = 'index_name'
    ) THEN
        ALTER TABLE `rtc_Phototype` 
        ADD INDEX index_name (`imgtype`);
    END IF;
END//
DELIMITER ;
CALL schema_change();

索引查詢關鍵差異

查詢 information_schema.statistics 而非 columns 表
過濾欄位為 index_name 而非 column_name
資料表名區分大小寫（`rtc_Phototype`），需與實際一致

進階應用模式

1. 批次處理多欄位/多索引

CREATE PROCEDURE batch_schema_update()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE col_name VARCHAR(64);
    DECLARE cur CURSOR FOR 
        SELECT column_name FROM temp_columns_to_add;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO col_name;
        IF done THEN LEAVE read_loop; END IF;
        
        SET @sql = CONCAT('IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name="target_table" AND column_name="', col_name, '") THEN ALTER TABLE target_table ADD COLUMN ', col_name, ' VARCHAR(255); END IF;');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END