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

147人阅读 评论(0) 收藏 举报
分类:

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








0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:466259次
    • 积分:4843
    • 等级:
    • 排名:第6551名
    • 原创:97篇
    • 转载:31篇
    • 译文:0篇
    • 评论:314条
    个人简介
    我只是一个简单的Coder,为了兴趣和理想奋斗在生活的道路上
    希望大家多多交流,共同进步,有疑问或想法请留言或者私信,也可以加群:157797573

    博客专栏
    最新评论
    你还在一个人孤独的奋斗吗?
    其实,你并不寂寞,欢迎加入CSDN Java群,这里是你的另一个家!    点击加入:CSDN Java群