Mysql动态更新数据库脚本的示例讲解
具体的upgrade脚本如下:
动态删除索引
DROPPROCEDUREIFEXISTSUPGRADE; DELIMITER$$ CREATEPROCEDUREUPGRADE() BEGIN --RESOURCE.AUDIO_ATTRIBUTE IFEXISTS(SELECT*FROMINFORMATION_SCHEMA.STATISTICSWHERETABLE_SCHEMA='RESOURCE'ANDTABLE_NAME='AUDIO_ATTRIBUTE'ANDINDEX_NAME='resource_publish_resource_id_index') THEN ALTERTABLE`AUDIO_ATTRIBUTE`DROPINDEXresource_publish_resource_id_index; ENDIF; END$$ DELIMITER; CALLUPGRADE(); DROPPROCEDUREIFEXISTSUPGRADE;
动态添加字段
DROPPROCEDUREIFEXISTSUPGRADE; DELIMITER$$ CREATEPROCEDUREUPGRADE() BEGIN --HOMEWORK.HOMEWORK_QUESTION_GROUP.FROM_ID IFNOTEXISTS(SELECT*FROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_SCHEMA='HOMEWORK'ANDTABLE_NAME='HOMEWORK_QUESTION_GROUP'ANDCOLUMN_NAME='FROM_ID') THEN ALTERTABLE`HOMEWORK_QUESTION_GROUP`ADDCOLUMNFROM_IDVARCHAR(50)NULL; ENDIF; --HOMEWORK.HOMEWORK_QUESTION_GROUP.QUESTION_TYPE IFNOTEXISTS(SELECT*FROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_SCHEMA='HOMEWORK'ANDTABLE_NAME='HOMEWORK_QUESTION_GROUP'ANDCOLUMN_NAME='QUESTION_TYPE') THEN ALTERTABLE`HOMEWORK_QUESTION_GROUP`ADDCOLUMNQUESTION_TYPEVARCHAR(50)NULL; ENDIF; --HOMEWORK.HOMEWORK_QUESTION_GROUP.DIFFICULTY IFNOTEXISTS(SELECT*FROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_SCHEMA='HOMEWORK'ANDTABLE_NAME='HOMEWORK_QUESTION_GROUP'ANDCOLUMN_NAME='DIFFICULTY') THEN ALTERTABLE`HOMEWORK_QUESTION_GROUP`ADDCOLUMNDIFFICULTYVARCHAR(50)NULL; ENDIF; END$$ DELIMITER; CALLUPGRADE(); DROPPROCEDUREIFEXISTSUPGRADE;
其他语法类似,主要区分EXISTS和NOTEXISTS的用法。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对毛票票的支持。如果你想了解更多相关内容请查看下面相关链接