在实际项目开发中,我们经常无法避免后期修改表结构的操作,以oracle数据库为例。假如现有表TEST_TAB_A;其表结构为:
create table TEST_TAB_A(
id varchar2(32) not null,
name varchar2(20) default null,
filepath varchar2(600) default null,
constraint pk_TEST_TAB_A_ID PRIMARY KEY (id)
);
一、关于数据库字段的更新
如改变某张表中某字段的数据类型,数据类型的长度等操作。一般分为以下两种情况:
1. 修改空表的字段
这种情况下,我们完全不用考虑此修改是否会影响表中数据,就可以直接大胆改,假如现在因需求变化我们需要调整TEST_TAB_A的表结构。
eg:需要将该表的主键id的数据类型修改为long,长度改为36,将普通字段filepath的数据类型改为clob。其SQL为:
alter table TEST_TAB_A modify id long(36);
alter table TEST_TAB_A modify filepath clob;
不需要commit的,因为修改表结构不用提交,只有修改了表中数据时才需要使用commit提交数据。上述SQL中,关键字用紫色标出。
2.修改非空表
修改非空表时,因为表中有数据,表中有数据的情况下不允许直接做修改。所以需要考虑以下两种情况;即表中数据是否可以删除。
(1)表中数据可删除,那么就可以先删除表中数据,再修改表中字段。以eg为例。此时的SQL为:
delete from TEST_TAB_A ; //使用delete语句删除表中数据
commit; //使用commit语句进行数据删除后的提交
alter table TEST_TAB_A modify id long(36);
alter table TEST_TAB_A modify filepath clob;
(2)表中数据不可删除,此时就需要以下操作:
首先使用一个临时字段暂时保存要修改字段中的数据,然后对需要修改的字段做修改,修改完成后,将临时字段的值赋给修改后的字段,最后删除临时字段。具体操作是:先将要修改的字段重命名为其他名称,让它暂时保存一下要修改字段中的数据,因为一张表中不能出现两个名称相同的字段(第二范式),然后再重新添加一个要修改的字段,并且这一步操作完成该字段数据类型的修改,接着使用update语句将重名后的字段的值赋给修改后的字段,最后将重命名的字段删除,其实字段重命名的目的就在于暂时保存下需要修改字段中的数据。以eg为例;SQL为:
将id字段的数据类型修改为long(36)
alter table TEST_TAB_A add id long(36); //修改需要修改字段的数据类型
update TEST_TAB_A set id=id_bak; //执行赋值操作
commit; //提交数据
alter table TEST_TAB_A drop column id_bak; //删除重命名的字段
alter table TEST_TAB_A add filepath long(36);
update TEST_TAB_A set filepath=filepath_bak;
commit;
alter table TEST_TAB_A drop column filepath_bak;