技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> Oracle --> 如何更改字段至兼容的不同类型

如何更改字段至兼容的不同类型

浏览:3071次  出处信息

    有些时候,我们可能会遇到这样一种现象,将一个字段“不小心”定义为varchar2类型,而后来插入里面的数据都是数字,之后发现这个字段确实应该为number类型,而此时你又想在不影响用户使用的前提下,或者说尽量小的影响用户,保证数据不丢失的情况下,来更改这个数据类型,这个时候如果你用普通的alter table modify命令,你可能就要遇到错误了。就想下面这种情况一样:

22:25:31 SQL> select * from t;

A

----------

10

22:25:32 SQL> desc t

 名称                 是否为空? 类型

 -------------------- --------- --------------------

A                              VARCHAR2(10)

22:25:34 SQL> alter table t modify(a number);

alter table t modify(a number)

                     *

第 1 行出现错误:

ORA-01439: 要更改数据类型, 则要修改的列必须为空

    其实,类似的现象还有很多,如本来应该是date类型,结果却被定义成varchar2类型了,等等,主要就是那些兼容的数据类型之间的转换定义。当然,为什么一开始会犯这样的错误,那可能原因就有多方面了,如一开始需求就不对等等,这个这里就不多说了。

    下面,我就以上面那个例子,来主要2种处理这种问题的方法。

    第一种方法,通过增加列来完成。

22:25:44 SQL> alter table t add (b number);

表已更改。

22:34:16 SQL> update t set b = to_number(a)

已更新 1 行。

22:34:39 SQL> update t set a = null;

已更新 1 行。

22:34:50 SQL> commit;

提交完成。

22:34:52 SQL> select * from t;

A                   B

---------- ----------

                   10

22:34:55 SQL> alter table t modify a number

表已更改。

22:35:21 SQL> update t set a=b;

已更新 1 行。

已用时间:  00: 00: 00.01

22:35:33 SQL> commit;

提交完成。

22:35:52 SQL> alter table t drop column b;

表已更改。

    在这种方法中最后也可以先drop column a,然后rename column b to a,达到的效果是一样的。

    第二种,通过新建表,在新表上更改字段,最后rename新表来完成。

22:41:44 SQL> create table t1 as select * from t where 1=2;

表已创建。

22:42:14 SQL> alter table t1 modify (a number);

表已更改。

22:42:24 SQL> insert into t1 select * from t;

已创建 1 行。

22:42:37 SQL> commit;

提交完成。

22:42:40 SQL> drop table t;

表已删除。

22:42:46 SQL> rename t1 to t;

表已重命名。

22:43:00 SQL> desc t;

 名称           是否为空? 类型

 -------------- --------- -------------

 A                        NUMBER

    我就知道这两种常见的方法,不知道还有没有什么其他的好方法。如果有,请分享。建议使用第一种。

 

======================================================

由于网易回复有1000字数限制,下面的内容是回答湖南朋友的:

约束和索引都被摧残了,可以做个实验证明一下。

先建立测试表并插入数据
15:28:45 SQL> create table t (a varchar2(10) not null);
表已创建。
已用时间:  00: 00: 00.03
15:28:47 SQL> insert into t values (\'10\');
已创建 1 行。
已用时间:  00: 00: 00.00
15:28:55 SQL> commit;
提交完成。
已用时间:  00: 00: 00.00

 

然后增加一个列,并且把数据copy过去
15:29:08 SQL> alter table t add (b number);
表已更改。
已用时间:  00: 00: 00.01
15:29:31 SQL> update t set b=to_number(a);
已更新 1 行。
已用时间:  00: 00: 00.01
15:29:42 SQL> commit;
提交完成。
已用时间:  00: 00: 00.00
15:29:45 SQL> alter table t modify b not null;
表已更改。
已用时间:  00: 00: 00.10

 

在原来的列上建立索引和约束
15:29:59 SQL> create index ind_t_a on t(a);
索引已创建。
已用时间:  00: 00: 00.15
15:31:01 SQL> alter table t add constraint uk_t_a unique(a);
表已更改。
已用时间:  00: 00: 00.03

 

乾坤大挪移
15:31:20 SQL> alter table t drop column a;
表已更改。
已用时间:  00: 00: 01.20
15:32:06 SQL> alter table t rename column b to a;
表已更改。
已用时间:  00: 00: 00.03

 

查看索引是否还在?可以看到索引已经被摧残掉了
15:33:17 SQL> select index_name,status from user_indexes where table_name=\'T\';
未选定行
已用时间:  00: 00: 00.12
15:34:20 SQL> select index_name,status from user_indexes order by 1;
INDEX_NAME                     STATUS
------------------------------ --------
PK_DEPT                        VALID
PK_EMP                         VALID
已用时间:  00: 00: 00.35

 

查看约束是否还在?可以看到它也被摧残了
15:38:07 SQL> select constraint_name,constraint_type,invalid,table_name from user_constraints where table_name=\'T\';
CONSTRAINT_NAME                CONSTRAINT_TYPE      INVALID TABLE_NAME
------------------------------ -------------------- ------- ------------------------------
SYS_C005423                    C                            T
已用时间:  00: 00: 00.03
15:38:24 SQL> select constraint_name,constraint_type,invalid,table_name from user_constraints;
CONSTRAINT_NAME                CONSTRAINT_TYPE      INVALID TABLE_NAME
------------------------------ -------------------- ------- ------------------------------
BIN$lNdA2W97SK69TBAu945o7w==$0 C                            BIN$W9ZnSgtTS9+Fs/438TqKuA==$0
BIN$6JXde6KdTsum1GYM3py1eg==$0 C                            BIN$W9ZnSgtTS9+Fs/438TqKuA==$0
SYS_C005423                    C                            T
FK_DEPTNO                      R                            EMP
PK_DEPT                        P                            DEPT
PK_EMP                         P                            EMP
已选择6行。
已用时间:  00: 00: 00.23

 

验证一下,看对不对,果然,唯一约束已经不起作用了
15:38:31 SQL> select * from t;
         A
----------
        10
已用时间:  00: 00: 00.03
15:39:26 SQL> desc t;
 名称            是否为空? 类型
 --------------- --------- ---------------------------
 A                NOT NULL NUMBER
15:39:29 SQL> insert into t values(10);
已创建 1 行。
已用时间:  00: 00: 00.01
15:39:38 SQL> commit;
提交完成。
已用时间:  00: 00: 00.00
15:39:44 SQL> select * from t;
         A
----------
        10
        10

 

那为什么还有一个C约束呢?上面的查询明明不是显示着吗?那是not null约束:
15:51:06 SQL> select table_name,constraint_name,column_name from user_cons_columns where table_name=\'T\';
TABLE_NAME                     CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
T                              SYS_C005423                    A

16:09:29 SQL> select search_condition from user_constraints where table_name=\'T\';

SEARCH_CONDITION
--------------------------------------------------------------------------------
"A" IS NOT NULL

建议继续学习:

  1. 12款很棒的浏览器兼容性测试工具推荐    (阅读:4844)
  2. Javascript和CSS浏览器兼容总结    (阅读:3907)
  3. 兼容所有浏览器的设为首页与显示小策略    (阅读:2948)
  4. 前端设计中的浏览器CSS Hack汇总    (阅读:2772)
  5. 定位元素间的Z值比较及z-index在不同浏览器下默认值的影响    (阅读:2661)
  6. Blob/Text字段类型在MySQL Cluster中的处理    (阅读:2413)
  7. 抛弃 CSS Hacks 后的浏览器兼容方案    (阅读:1958)
  8. 抛弃 CSS Hacks 后的浏览器兼容方案    (阅读:1680)
  9. 让数据解析能够做到向前向后完全兼容(最近做项目总结)    (阅读:1586)
  10. js代码因逗号不规范导致IE不兼容的问题    (阅读:1116)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1