Mysql动态更新数据库脚本的示例讲解

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;