数据库各种改表sql写发总结

//修改已存在的表的字段属性
ALTER TABLE region_info MODIFY code_name varchar(20) default ‘‘;

//删除索引
ALTER TABLE poi drop index `uniq_domain_class_name_dim_code`;

//增加unique key
ALTER TABLE poi add unique key `uniq_domain_type_name` (`domain`,`class`,`name`,`dim`,`code`);

//更新字段属性
ALTER TABLE poi MODIFY coords text default NULL;

//删除表字段
alter table poi drop is_delete;

//修改coords不为空
ALTER TABLE poi MODIFY coords text NOT NULL;

//增加一列 is_delete
alter table poi add is_delete int(11) not null default 0;

 执行sql:

alter table message_template add used_by varchar(30) not null default ‘‘ comment ‘负责人‘ after `code` ;
回滚sql:
alter table message_template drop used_by;

执行SQL：
 ALTER TABLE region_info MODIFY code_name varchar(20) NOT NULL;

?ALTER TABLE po drop index `uniq_domain_type_name`;?

ALTER TABLE po add unique key `uniq_domain_class_name_dim_code` (`domain`,`class`,`name`,`dim`,`code`);?

ALTER TABLE po MODIFY coords text NOT NULL;?ALTER TABLE poi MODIFY data text NOT NULL;

回滚SQL:
ALTER TABLE region_info MODIFY code_name varchar(20) default ‘‘;?

ALTER TABLE poi drop index `uniq_domain_class_name_dim_code`;?

ALTER TABLE poi add unique key `uniq_domain_type_name` (`domain`,`class`,`name`,`dim`,`code`);?

ALTER TABLE poi MODIFY coords text default NULL;?ALTER TABLE poi MODIFY data text default NULL;

执行:
ALTER TABLE region_info MODIFY code_name varchar(20) NOT NULL;
ALTER TABLE poi drop index `uniq_domain_type_name`;
ALTER TABLE poi add unique key `uniq_domain_class_name_dim_code` (`domain`,`class`,`name`,`dim`,`code`);
ALTER TABLE poi MODIFY coords text NOT NULL;
ALTER TABLE poi MODIFY data text NOT NULL;

回滚:
ALTER TABLE region_info MODIFY code_name varchar(20) default ‘‘;
ALTER TABLE poi drop index `uniq_domain_class_name_dim_code`;
ALTER TABLE poi add unique key `uniq_domain_type_name` (`domain`,`class`,`name`,`dim`,`code`);
ALTER TABLE poi MODIFY coords text default NULL;
ALTER TABLE poi MODIFY data text default NULL;

alter table message_template add used_by varchar(30) not null default ‘‘ comment ‘负责人‘ after `code` ;

数据库各种改表sql写发总结

标签：ext   unique   存在   回滚   执行   dex   comment   alter   main