mysql动态更新字段,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的用法。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对服务器之家的支持。