正常情况下,数据库的设计是需求调研之后确定下来的,也就是根据具体的需求,设计出表关系、各个字段类型。很少出现已经有生产数据却需要修改表字段类型的情况。如果出现这种情况,个人觉得这是当时设计数据库时的不足。
实际情况是总会出现不正常情况,最近遇到一个问题,一个小系统已经上线,已经有生产数据,但是客户要求修改表中某个字段的类型。对于生产数据的操作总是要小心谨慎,通过上网查找。找到两种解决方案。以下是测试情况:
SQL> create table test_m (id varchar2 (100) ,mc varchar2(100));
Table created
SQL> insert into test_m (id,mc) values ('1','11');
1 row inserted
SQL> insert into test_m (id,mc) values ('2','22');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test_m;
ID MC
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 11
2 22
SQL>
SQL> alter table test_m modify mc number(10);
alter table test_m modify mc number(10)
ORA-01439: 要更改数据类型, 则要修改的列必须为空
如错误信息所示,如果修改数据类型,则要求被修改的列内容为空。但是如果只是修改列字段的长度,而不是修改类型,则不会报错,如下,将mc字段长度由100改到1000:
SQL> alter table test_m modify mc varchar2(1000);
Table altered
如要修改表的字段类型,可以通过以下两种方式:
第一,建立临时表,将原字段内容置为null,之后修改字段类型,修改完成后,再将数据同步回去。
SQL> create table test_m_temp as select * from test_m;
Table created
SQL> update test_m set mc = null;
2 rows updated
SQL> commit;
Commit complete
SQL> alter table test_m modify mc number(10);
Table altered
这样就成功的修改了字段类型,但是还要讲数据同步回去。
SQL> update test_m t set mc = (select mc from test_m_temp m where t.id = m.id);
2 rows updated
SQL> commit;
Commit complete
到此第一种方法结束。
第二种方法,新增加一列,然后将该列重新命名为要舍弃的列。
SQL> desc test_m;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID VARCHAR2(100) Y
MC NUMBER(10) Y
SQL> alter table test_m add mct varchar2(100);
Table altered
SQL> alter table test_m drop column mc;
Table altered
SQL> alter table test_m rename column mct to mc;
Table altered
SQL> desc test_m;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID VARCHAR2(100) Y
MC VARCHAR2(100) Y
SQL> select * from test_m;
ID MC
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 11
2 22
具体情况还要根据实际情况处理。
实际情况是总会出现不正常情况,最近遇到一个问题,一个小系统已经上线,已经有生产数据,但是客户要求修改表中某个字段的类型。对于生产数据的操作总是要小心谨慎,通过上网查找。找到两种解决方案。以下是测试情况:
SQL> create table test_m (id varchar2 (100) ,mc varchar2(100));
Table created
SQL> insert into test_m (id,mc) values ('1','11');
1 row inserted
SQL> insert into test_m (id,mc) values ('2','22');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test_m;
ID MC
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 11
2 22
SQL>
SQL> alter table test_m modify mc number(10);
alter table test_m modify mc number(10)
ORA-01439: 要更改数据类型, 则要修改的列必须为空
如错误信息所示,如果修改数据类型,则要求被修改的列内容为空。但是如果只是修改列字段的长度,而不是修改类型,则不会报错,如下,将mc字段长度由100改到1000:
SQL> alter table test_m modify mc varchar2(1000);
Table altered
如要修改表的字段类型,可以通过以下两种方式:
第一,建立临时表,将原字段内容置为null,之后修改字段类型,修改完成后,再将数据同步回去。
SQL> create table test_m_temp as select * from test_m;
Table created
SQL> update test_m set mc = null;
2 rows updated
SQL> commit;
Commit complete
SQL> alter table test_m modify mc number(10);
Table altered
这样就成功的修改了字段类型,但是还要讲数据同步回去。
SQL> update test_m t set mc = (select mc from test_m_temp m where t.id = m.id);
2 rows updated
SQL> commit;
Commit complete
到此第一种方法结束。
第二种方法,新增加一列,然后将该列重新命名为要舍弃的列。
SQL> desc test_m;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID VARCHAR2(100) Y
MC NUMBER(10) Y
SQL> alter table test_m add mct varchar2(100);
Table altered
SQL> alter table test_m drop column mc;
Table altered
SQL> alter table test_m rename column mct to mc;
Table altered
SQL> desc test_m;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID VARCHAR2(100) Y
MC VARCHAR2(100) Y
SQL> select * from test_m;
ID MC
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 11
2 22
具体情况还要根据实际情况处理。