【转】自增长列和序列的区别

最近工作需要,用到DB2,但是对DB2的序列缺乏理解,找了大量资料,寻找资料的过程中,发现了这篇博文,感觉总结的很具体详细,特收藏。

 

 

原博文地址: http://blog.csdn.net/chinayuan/archive/2009/03/05/3961092.aspx

 

 

 

 

 

 

 

 

----------------------------------------------------------------
identity 和sequence的区别:
----------------------------------------------------------------
db2,sql server 都不支持一个表中包含两个以及两个以上的identity字段
oracle 不支持idenity字段
sql server不支持sequence用法
db2 支持sequence,identity的两种用法

identity因为只能在一个table中用一次。
但是一个sequence可以在一张表中的多个字段多被用到。



----------------------------------------------------------------
db2 使用方法:(支持sequence,identity)
----------------------------------------------------------------
参考资料: http://www.ibm.com/developerworks/cn/data/library/techarticles/0302fielding/0302fielding.html

identity :(两种使用方法)
1. Select IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
2.CREATE TABLE WBIA_JDBC_EventStore
(
event_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) PRIMARY KEY,
)


sequence:(两种使用方法)
CREATE SEQUENCE orders_seq AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER

1.values nextval for $sequenceName$
2.examples:
INSERT INTO customer_orders_t
VALUES
(
NEXT VALUE FOR orders_seq,
....
)


复杂的实验内容:(一个表中有一个自增长的字段,另外两个字段都用sequence来插入数据)下面sql语句得到正常运行
drop sequence sequence1;
drop sequence sequence2;
CREATE SEQUENCE sequence1 AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER;
CREATE SEQUENCE sequence2 AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER;

drop table temp;
CREATE TABLE temp
(
event_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) PRIMARY KEY,
xid VARCHAR(200),
object_key VARCHAR(80) ,
object_name VARCHAR(40) ,
object_function VARCHAR(40) ,
event_priority INTEGER ,
event_time TIMESTAMP default CURRENT TIMESTAMP NOT NULL,
event_status INTEGER ,
event_comment VARCHAR(100)
);


insert into temp(event_priority,event_status) values(NEXT VALUE FOR sequence1, NEXT VALUE FOR sequence2);

insert into temp(event_priority,event_status) values(NEXT VALUE FOR sequence1, NEXT VALUE FOR sequence1);

insert into temp(event_priority,event_status) values(NEXT VALUE FOR sequence2, NEXT VALUE FOR sequence2);

select event_id,event_priority,event_status from temp;


----------------------------------------------------------------
oracle 使用方法:(只支持sequence)
----------------------------------------------------------------
CREATE SEQUENCE event_sequence start with 1;

1.select $sequenceName$.nextval from dual
2.examples:
INSERT INTO wbia_jdbc_eventstore (event_id, object_key, object_name, object_function, event_priority, event_status)
VALUES(event_sequence.nextval, :NEW.pkey, 'InDb2adminCustomerBG', 'Create', 1, 0);

复杂的实验内容:(一个表中有两个字段都用sequence来插入数据)下面sql语句得到正常运行

drop sequence sequence1;
drop sequence sequence2;
CREATE SEQUENCE sequence1 start with 1;
CREATE SEQUENCE sequence2 start with 1;

drop table temp;
CREATE TABLE temp
(
event_id NUMBER(20) PRIMARY KEY,
xid VARCHAR2(200),
object_key VARCHAR2(80),
object_name VARCHAR2(40),
object_function VARCHAR2(40),
event_priority NUMBER(5) ,
event_time DATE DEFAULT SYSDATE NOT NULL,
event_status NUMBER(5),
event_comment VARCHAR2(100)
);


insert into temp(event_id,event_priority,event_status) values(sequence1.nextval, sequence1.nextval,sequence2.nextval);

insert into temp(event_id,event_priority,event_status) values(sequence1.nextval, sequence1.nextval,sequence1.nextval);

insert into temp(event_id,event_priority,event_status) values(sequence1.nextval, sequence2.nextval,sequence2.nextval);

select event_id,event_priority,event_status from temp;



----------------------------------------------------------------
sql server identity 使用方法:(只支持identity)
----------------------------------------------------------------
CREATE TABLE WBIA_JDBC_EVENTSTORE (
event_id DECIMAL(20) IDENTITY(1,1) PRIMARY KEY,
)

复杂的实验内容:(一个表中有一个自增长的字段,不允许两个以上自增长的列)下面sql语句得到正常运行

drop table temp;
CREATE TABLE temp
(
event_id INT NOT NULL PRIMARY KEY,
event_priority DECIMAL(20) IDENTITY(1,1) ,
-- event_status INT IDENTITY(2,1) , --Multiple identity columns specified for table 'temp'. Only one identity column per table is allowed.
);


insert into temp(event_id) values(1);
insert into temp(event_id) values(2);


select event_id,event_priority from temp;


----------------------------------------------------------------
MySQL identity 使用方法:(只支持identity)
----------------------------------------------------------------
drop table temp;
CREATE TABLE temp
(
event_id BIGINT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
xid VARCHAR(200) ,
object_key VARCHAR(80) ,
object_name VARCHAR(40) ,
object_function VARCHAR(40) ,
event_priority INTEGER ,
event_time TIMESTAMP ,
event_status INTEGER ,
event_comment VARCHAR(100)
) ENGINE=INNODB AUTO_INCREMENT=100000;;

--ENGINE=INNODB:表示说明你所创建表的类型:
--默认的是:MYISAM,旧版的是:ISAM,而INNODB类型的表支持外健、事务等高级应用。
--在创建表的最后用ENGINE=INNODB 说明;
--AUTO_INCREMENT=100000: 设置ID自动增长列,且从100000开始增长.

insert into temp(event_priority,event_status) values(1,1);

insert into temp(event_priority,event_status) values(1,1);

insert into temp(event_priority,event_status) values(1,1);

select event_id,event_priority,event_status from temp;



-----------------------------------------------------------------------
sequence技术专题:http://baike.baidu.com/view/71967.htm
-----------------------------------------------------------------------
Sequence是数据库系统按照一定规则自动增加的数字序列。这个序列一般作为代理主键(因为不会重复),没有其他任何意义。
Sequence是数据库系统的特性,有的数据库有Sequence,有的没有。比如Oracle、DB2、PostgreSQL数据库有Sequence,MySQL、SQL Server、Sybase等数据库没有Sequence。
根据我个人理解,Sequence是数据中一个特殊存放等差数列的表,该表受数据库系统控制,任何时候数据库系统都可以根据当前记录数大小加上步长来获取 到该表下一条记录应该是多少,这个表没有实际意义,常常用来做主键用,非常不错,呵呵,不过很郁闷的各个数据库厂商尿不到一个壶里--各有各的一套对 Sequence的定义和操作。在此我对常见三种数据库的Sequence的定义和操作做一个对比和总结,以便日后查看。
一、定义Sequence
定义一个seq_test,最小值为10000,最大值为99999999999999999,从20000开始,增量的步长为1,缓存为20的循环排序Sequence。
Oracle的定义方法:
create sequence seq_test
minvalue 10000
maxvalue 99999999999999999
start with 20000
increment by 1
cache 20
cycle
order;


DB2的写法:
create sequence seq_test
as bigint
start with 20000
increment by 1
minvalue 10000
maxvalue 99999999999999999
cycle
cache 20
order;

PostgreSQL的写法:
create sequence seq_test
increment by 1
minvalue 10000
maxvalue 99999999999999999
start 20000
cache 20
cycle;

二、Oracle、DB2、PostgreSQL数据库Sequence值的引用参数为:currval、nextval,分别表示当前值和下一个值。下面分别从三个数据库的Sequence中获取nextval的值。
Oracle中:seq_test.nextval
例如:select seq_test.nextval from dual;
DB2中:nextval for SEQ_TOPICMS
例如:values nextval for seq_test;
PostgreSQL中:nextval(seq_test)
例如:select nextval(seq_test);

三、Sequence与indentity的区别与联系
Sequence与indentity的基本作用都差不多。都可以生成自增数字序列。
Sequence是数据库系统中的一个对象,可以在整个数据库中使用,和表没有任何关系;indentity仅仅是指定在表中某一列上,作用范围就是这个表。

四、postgresql中的Sequence分配策略(postgresql8.3.x)
调用select nextval(seq_test);后,系统马上分配一个序号给用户,然后系统的次Sequence马上加上之前设置的步长(increment by 1),不论此序号用户是否使用。
调用select currval(seq_test);返回当前的序列号,该序列号只要没有使用,就不会变化,直到被使用后,才会变化,并且该序列号已经分配给当前请求,不会再分配给其他的请求。
所以,减少浪费时,使用currval,不在乎浪费,而且id不连续的话,可以使用nextval。因为一旦分配给你后,使用失败,将会把该序列号丢弃!


ORACLE SEQUENCE的简单介绍
在oracle中sequence就是所谓的序列号,每次取的时候它会自动增加,一般用在需要按序列号排序的地方。
1、Create Sequence
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限,
CREATE SEQUENCE emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加,不循环
CACHE 10;
一旦定义了emp_sequence,你就可以用CURRVAL,NEXTVAL
CURRVAL=返回 sequence的当前值
NEXTVAL=增加sequence的值,然后返回 sequence 值
比如:
emp_sequence.CURRVAL
emp_sequence.NEXTVAL
可以使用sequence的地方:
- 不包含子查询、snapshot、VIEW的 SELECT 语句
- INSERT语句的子查询中
- NSERT语句的VALUES中
- UPDATE 的 SET中
可以看如下例子:
INSERT INTO emp VALUES
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);
SELECT empseq.currval FROM DUAL;
但是要注意的是:
- 第一次NEXTVAL返回的是初始值;随后的NEXTVAL会自动增加你定义的INCREMENT BY值,然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE的值,但是在第一次NEXTVAL初始化之后才能使用CURRVAL,否则会出错。一次NEXTVAL会增加一次 SEQUENCE的值,所以如果你在同一个语句里面使用多个NEXTVAL,其值就是不一样的。明白?
- 如果指定CACHE值,ORACLE就可以预先在内存里面放置一些sequence,这样存取的快些。cache里面的取完后,oracle自动再取一组 到cache。 使用cache或许会跳号, 比如数据库突然不正常down掉(shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。

2、Alter Sequence
你或者是该sequence的owner,或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值,必须 drop sequence 再 re-create .
Alter sequence 的例子
ALTER SEQUENCE emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE -- 到10000后从头开始
NOCACHE ;
影响Sequence的初始化参数:
SEQUENCE_CACHE_ENTRIES =设置能同时被cache的sequence数目。
可以很简单的Drop Sequence
DROP SEQUENCE order_seq;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值