调整新添加的列在表中的位置,调整数据库表中列的位置

发表评论

在Sybase中，表内新添加的列都位于表的最后。其它数据库也基本相同，这和数据库的内部物理存储是有必然关系的。我研究过sybase 中数据的物理存储结构，理解在sybase以及其它数据库中新增加的列必须放置在最后的原因。 在项目实践中，可能有这样的需求：需要将最后添加的一列放置到前面的位置。比如：表test中原来的列为：a,b,c，不想让最后添加的列d放在第四个位置，而让列d放在第2个位置，形成：a,d,b,c这样的顺序。如何实现呢？ 一般的办法是： 1.新建一个临时表：test_bak,

create table test_bak ( a 列属性,

d 列属性,

b 列属性,

c 列属性

)
 

2.将原表test的数据拷贝到临时表test_bak中

insert into test_bak(a,d,b,c)

select a,d,b,c from test
 

3.将原表删除，临时表改名为原表的名字

drop table test

sp_rename test_bak,test
 

以上是常规的办法。如果表内的数据量很大的时候，则会比较耗费时间。 下面我介绍另外一个比较简便的方法。

主要思路是调整表在syscolumns中对应的colid的值。

先建立一个新的测试表test

1> drop table test

2> go

1> create table test(id int not null,name varchar(30) null,age tinyint not null, se_x char(1) not null,birthday datetime null)

2> go

1> select *  from test
2> go
id          name                           age se_x birthday
----------- ------------------------------ --- --- --------------------------

(0 rows affected)

插入一条测试数据

1> insert into test
2> values(1,'andkylee',28,'F','1982-03-20 12:00:00')
3> go
(1 row affected)
1> select *  from test
2> go
id          name                           age se_x birthday
----------- ------------------------------ --- --- --------------------------
           1 andkylee                        28 F          Mar 20 1982 12:00PM
(1 row affected)

调整各个的顺序，使之倒序。将原来的id,name,age,se_x,birthday改成：birthday,se_x,age,name,id
调整方法：修改syscolumns表中测试表test的各个列的colid的顺序。

1> select *  from test
2> go
birthday                   se_x age name                           id
-------------------------- --- --- ------------------------------ -----------
        Mar 20 1982 12:00PM F    28 andkylee                                 1

(1 row affected)

列的顺序逆序后，显示插入数据。
 

1> insert into test(id,name,age,se_x,birthday)
2> values(2,'liu',30,'M','2000-01-01 11:59:59')
3> go
(1 row affected)
1> select * from test
2> go
birthday                   se_x age name                           id
-------------------------- --- --- ------------------------------ -----------
        Mar 20 1982 12:00PM F    28 andkylee                                 1
        Jan  1 2000 11:59AM M    30 liu                                      2

(2 rows affected)

如果按照调整数据之前的列的顺序插入数据，则会报错。

1> insert into test
2> values(3,'zhang',29,'F','1980-01-01 11:59:59')
3> go

Msg 206, Level 16, State 2:
Server 'SYB_NFJD_TEST', Line 1:
Operand type clash: INT is incompatible with DATETIME
Msg 257, Level 16, State 1:
Server 'SYB_NFJD_TEST', Line 1:
Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.  Use the
CONVERT function to run this query.

新的数据要按照修改后的列的顺序插入。

1> insert into test
2> values('1980-01-01 11:59:59','F',29,'zhang',3)
3> go
(1 row affected)

最后，查看测试表的数据。

1> select * from test
2> go
birthday                   se_x age name                           id
-------------------------- --- --- ------------------------------ -----------
        Mar 20 1982 12:00PM F    28 andkylee                                 1
        Jan  1 2000 11:59AM M    30 liu                                      2
        Jan  1 1980 11:59AM F    29 zhang                                    3

(3 rows affected)

备注:绝大多数sybase客户端工具显示表的列时都是按照colid的顺序显示的。所以，此种方法能够实现调整数据库表中列的位置的功能。

————————————————————————————————————
——— 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
——— 转载务必注明原始出处 : http://www.dbainfo.net
——— 关键字： 列显示顺序 syscolumns colid
————————————————————————————————————

  • 本文链接地址：http://www.sybrepair.com/sybase-ase-table-newcolumn-place.htm；
  • 本文为dbainfo个人原创，请在尊重作者劳动成果的前提下进行转载；
  • 转载务必注明原始出处 : Sybase数据库技术，数据库恢复专家；
  • 对《调整新添加的列在表中的位置,调整数据库表中列的位置》有何疑问或见解，请在本文下方发表；
  • 对网站还有其他问题或建议，请提交在留言板，谢谢！
    • 标签: ,
    1. dbainfo
      2011-03-25 18:53:27
      回复 | 引用 | #1

      oracle中更改表中列的顺序

       

      在Oracle中创建表后，如果想修改表的定义可以使用alter table 命令，但是如果要修改列名或调整列的顺序时，alter table 命令将无能为力。当然可以使用原始办法，[ 通过create table 临时表 as select * from 源表 ],然后把原来的表删除，再重建，然后使用 [ insert into 新表 select 列1,列2 from 临时表 ] 就可以解决了，但是这样做需要有足够大的空间。
      下面介绍通过修改数据字典的方法修改表的列名：
      假设在 HR帐号下有EMP_COPY 表(ID,NAME,DID)，现决定要修改为EMP_COPY(ID,DEPARTID,ENAME)
      即修改列名与顺序。
      步骤：
      1. 在HR帐号下使用下面命令查询EMP_COPY对象的编号:
      SQL>
      1   select OBJECT_name,object_id from all_objects
      2* where object_name='EMP_COPY'
      SQL> /
      OBJECT_NAME                     OBJECT_ID
      —————————— ———-
      EMP_COPY                            50555
      2.切换到sys帐号下
      SQL> conn sys/password as sysdba;
      Connected.
      SQL> select obj#,col#,name from col$
      2 where obj#=50555;
            OBJ#       COL# NAME
      ———- ———- —————————-
           50555          1 ID
           50555          2 NAME
           50555          3 DID
      Elapsed: 00:00:00.03
      SQL>
      3. 下面可以通过修改这个系统表来实现修改列名与顺序，注意,COL#就是列的顺序。
      UPDATE COL$ SET COL#=2,name='DEPARTID'
      WHERE OBJ#=50555 and name='DID';
      UPDATE COL$ SET COL#=3,name='ENAME'
      WHERE OBJ#=50555 and name='NAME'
      4. 再次查询
      SQL> select obj#,col#,name from col$
      2 where obj#=50555;
            OBJ#       COL# NAME
      ———- ———- ——————————
           50555          1 ID
           50555          3 ENAME
           50555          2 DEPARTID
      5. 提交修改
      SQL>commit ;
      6. 重启服务
      SQL> SHUTDOWN IMMEDIATE
      SQL>STARTUP
      7. 登录HR,查询SELECT * FROM EMP_COPY;

      SQL> desc emp_copy;
      Name                  
      ———————–
      ID                    
      DEPARTID                  
      ENAME        

    :wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :?: :-| :-x :-o :-P :-D :-? :) :( :!: 8-O 8)