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

本文刊载的所有内容，包括文字、图片、音频、视频、软件、程序、以及网页版式设计等部门来源于互联网，版权均归原作者所有！本网站提供的内容服务于个人学习、研究或欣赏，以及其他非商业性或非盈利性用途，但同时应遵守著作权法及其他相关法律的规定，不得侵犯本网站及相关权利人的合法权利。
联系信息：邮箱aoxolcom@163.com或见网站底部。

THE END