动态更新数据库脚本——Mysql

g87616758

　　具体的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的用法。