ASE中调整新添加的列在表中的位置

转自：http://www.dbainfo.net/sybase-ase-table-newcolumn-place.htm

在Sybase中，表内新添加的列都位于表的最后。其它数据库也基本相同，这和数据库的内部物理存储是有必然关系的。我研究过sybase 中数据的物理存储结构，理解在sybase以及其它数据库中新增加的列必须放置在最后的原因。

在项目实践中，可能有这样的需求：需要将最后添加的一列放置到前面的位置。比如：表test中原来的列为：a,b,c，不想让最后添加的列d放在第四个位置，而让列d放在第2个位置，形成：a,d,b,c这样的顺序。如何实现呢？

一般的办法是：

1.新建一个临时表：test_bak,

1
2
3
4
5
6
7
8
9

        

create table test_bak ( a 列属性,

d 列属性,

b 列属性,

c 列属性

)

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

1
2
3

        

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

select a,d,b,c from test

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

1
2
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的顺序显示的。所以，此法顶能实现你的要求。

标签: 列顺序
本文链接: http://www.dbainfo.net/sybase-ase-table-newcolumn-place.htm
版权所有:本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
转载务必注明原始出处:Sybase数据库技术,数据库恢复[http://www.dbainfo.net]

呵呵， 转载的我的一篇博文。

本文能够实现通过调整colid的值来改变表列显示顺序的原因是：ASE存储引擎存取表数据的时候不是根据colid而是根据offset进行的。