今天小编为大家分享一篇关于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的用法。
以上就是本文的全部内容,希望对大家的学习有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。后面小编会分享更多运维方面的干货,感兴趣的朋友走一波关注哩~
如果觉得《mysql begin end 用法_超实用的Mysql动态更新数据库脚本的示例讲解(推荐)》对你有帮助,请点赞、收藏,并留下你的观点哦!