動態更新資料庫指令碼——Mysql

具體的upgrade指令碼如下：

動態刪除索引

DROP PROCEDURE IF EXISTS UPGRADE;

DELIMITER $$
CREATE PROCEDURE UPGRADE()
BEGIN
-- RESOURCE.AUDIO_ATTRIBUTE
	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'RESOURCE' AND TABLE_NAME = 'AUDIO_ATTRIBUTE' AND INDEX_NAME = 'resource_publish_resource_id_index')
    THEN 
        ALTER TABLE `AUDIO_ATTRIBUTE` DROP INDEX resource_publish_resource_id_index;
	END IF;
		
	
END$$
DELIMITER ;
CALL UPGRADE();
DROP PROCEDURE IF EXISTS UPGRADE;

動態新增欄位

DROP PROCEDURE IF EXISTS UPGRADE;

DELIMITER $$
CREATE PROCEDURE UPGRADE()
BEGIN
-- HOMEWORK.HOMEWORK_QUESTION_GROUP.FROM_ID
	IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'HOMEWORK' AND TABLE_NAME = 'HOMEWORK_QUESTION_GROUP' AND COLUMN_NAME = 'FROM_ID')
    THEN 
        ALTER TABLE `HOMEWORK_QUESTION_GROUP` ADD COLUMN FROM_ID VARCHAR(50) NULL;
	END IF;

-- HOMEWORK.HOMEWORK_QUESTION_GROUP.QUESTION_TYPE
	IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'HOMEWORK' AND TABLE_NAME = 'HOMEWORK_QUESTION_GROUP' AND COLUMN_NAME = 'QUESTION_TYPE')
    THEN 
        ALTER TABLE `HOMEWORK_QUESTION_GROUP` ADD COLUMN QUESTION_TYPE VARCHAR(50) NULL;
	END IF;
	
-- HOMEWORK.HOMEWORK_QUESTION_GROUP.DIFFICULTY
	IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'HOMEWORK' AND TABLE_NAME = 'HOMEWORK_QUESTION_GROUP' AND COLUMN_NAME = 'DIFFICULTY')
    THEN 
        ALTER TABLE `HOMEWORK_QUESTION_GROUP` ADD COLUMN DIFFICULTY VARCHAR(50) NULL;
	END IF;
	
END$$
DELIMITER ;
CALL UPGRADE();
DROP PROCEDURE IF EXISTS UPGRADE;

其他語法類似，主要區分EXISTS和 NOT EXISTS的用法。