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


总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对共生网络的支持。如果你想了解更多相关内容请查看下面相关链接