最近项目中有这样一个变动,需要修改数据库中某个字段类型。但是,表中已经存在大量数据了。DML句:"alter table 表名 modify 字段名 新数据类型 " 只对空表起作用,当存在数据时,不允许修改。
下面介绍两种不删除数据,更改数据类型的操作方法。
首先,我们先熟悉alter对字段的各种操作,后面会用得到。
对字段操作 | 操作方法 |
更新字段名 | alter table TABLE_NAME rename column column_old to column_new; |
添加字段 | alter table TABLE_NAME add COLUMN_NAME varchar(10); |
删除字段 | alter table TABLE_NAME drop column COLUMN_NAME; |
添加字段并附值 | alter table TABLE_NAME ADD COLUMN_NAME NUMBER(1) DEFAULT 1; |
修改字段值 | update TABLE_NAME set filedname=value where filedname=value; |
修改字段数据类型 | alter table tablename modify filedname varchar2(20); |
例如,下面要将fraction的double类型修改为float类型
方法一:
(1) 添加一个新字段 newfield,数据类型设置为新数据类型
(2) 将要修改字段的值复制到刚添加的列中 oldfield—newfield
(3) 删除原来字段oldfield
(4) 重命名newfield
- alter table test add fraction_temp float
- update test set fraction_temp =fraction
- alter table test drop column fraction
- span style="white-space:pre"> </span>alter table test rename column fraction_temp to fraction
方法二:
(1) 添加一个临时字段 field_temp,数据类型设置为新数据类型
(2) 将要修改字段的值复制到刚添加的临时列中 oldfield——field_temp,清空原来字段oldfield值
(3) 修改oldfield的数据类型
(4) 将值从临时字段中拷贝回来
(5) 清空临时字段值,删除临时字段
- alter table test add fraction_temp float
- update test set fraction_temp=fraction,fraction=null
- alter table test modify fraction float
- update test set fraction=fraction_temp,fraction_temp=null
- <span style="white-space:pre"> </span>alter table test drop column fraction_temp
第二种方法通过中间字段来操作,虽然步骤多了一步,但是可以避免第一种方法导致的字段位置变动,不过位置变动也没有什么大的影响吧。
其实,发现还有更简单的方法,数据库客户端支持粘贴复制功能,你可以打开两个客户端,保证一个客户端显示着表中的所有数据,同时从另一个客户端中删除数据,修改数据类型后,再从还保留着数据的客户端中将数据粘过来。