当前位置： 移动技术网 > IT编程>数据库>Mysql > Mysql动态更新数据库脚本的示例讲解

Mysql动态更新数据库脚本的示例讲解

2019年01月04日  | 移动技术网IT编程  | 我要评论

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

总结

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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网