KingbaseES 自增列三种方式

KingbaseES中3种自增长类型sequence，serial，identity的区别：

对象	显示插入	显示插入后更新最大值	清空表后是否重置	是否跟事务一起回滚	多对象共享	支持重置	出现错误后序列值是否增长
sequence	是	否	否	否	是	是	是
serial	是	否	否	否	否	是	是
identity	是	否	否	否	否	是	是

1、Sequence

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

create sequence seq_1 INCREMENT BY 1 MINVALUE 1 START WITH 1; create table test_seq (     id int not null default nextval('seq_1') primary key,     name varchar(10) );   隐式插入 insert into test_seq (name) values ('aaa'); insert into test_seq (name) values ('bbb'); insert into test_seq (name) values ('ccc'); <br>显式插入 insert into  test_seq (id,name) values (5,'ddd'); <br>再次隐式插入 test1=# insert into test_seq (name) values ('eee'); INSERT 0 1 test1=# insert into test_seq (name) values ('fff'); 错误: 重复键违反唯一约束"test_seq_pkey" 描述: 键值"(id)=(5)" 已经存在 <br>再次执行语句可正常插入，序列因为之前的错误调用自动增加 test1=# insert into test_seq (name) values ('fff'); INSERT 0 1

重置序列

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

--重置序列的最大值 select setval('seq_1',(select max(id) from  test_seq)::BIGINT);   --事务回滚后，序列号并不会回滚 test1=# begin; BEGIN test1=# insert into  test_seq (name) values ('ggg'); INSERT 0 1 test1=# rollback; ROLLBACK   -- truncate 表之后，序列不受影响 test1=# truncate table  test_seq; TRUNCATE TABLE test1=# insert into  test_seq (name) values ('ggg'); INSERT 0 1 test1=#  select * from test_seq;  id | name ----+------   9 | ggg (1 行记录)   --重置序列 ALTER SEQUENCE seq_1 RESTART WITH 1; test1=# ALTER SEQUENCE  seq_1 RESTART WITH 1; ALTER SEQUENCE test1=# insert into  test_seq (name) values ('ggg'); INSERT 0 1 test1=#  select * from test_seq;                     id | name ----+------   9 | ggg   1 | ggg

　　

2、Serial

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

create table  test_serial (     id serial primary key,     name varchar(100) ); 隐式插入 insert into   test_serial(name) values ('aaa'); insert into   test_serial(name) values ('bbb'); insert into   test_serial(name) values ('ccc'); 显示插入 insert into   test_serial(id,name) values (5,'ddd); select * from  test_serial;   --再次隐式插入,第二次会报错 test1=# insert into   test_serial(id,name) values (5,'ddd); INSERT 0 1 test1=# insert into   test_serial(name) values ('eee'); INSERT 0 1 test1=# insert into   test_serial(name) values ('fff'); 错误:  重复键违反唯一约束"test_serial_pkey" 描述:  键值"(id)=(5)" 已经存在   --再次执行语句可正常插入，序列因为之前的错误调用自动增加 test1=# insert into test_serial(name) values ('fff'); INSERT 0 1   --重置serial SELECT SETVAL((SELECT sys_get_serial_sequence(' test_serial', 'id')), 1, false);

　　

3、Identity

Identity是R6版本新增的语法，R3数据库不支持该语法。

identity定义成generated by default as identity允许显式插入，

identity定义成always as identity 不允许显示插入，但是加上overriding system value也可以显式插入。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

create table  test_identiy_1 (     id int generated always as identity (START WITH 1 INCREMENT BY 1)  primary key ,     name varchar(100) ); insert into  test_identiy_1(name) values ('aaa'); insert into  test_identiy_1(name) values ('bbb'); insert into  test_identiy_1(name) values ('ccc');   --显式插入值 如果定义为generated always as identity则不允许显式插入，除非增加overriding system value 提示。 test1=# insert into test_identiy_1(id,name) values (5,'ccc'); 错误:  无法插入到列"id" 描述:  列"id"是定义为GENERATED ALWAYS的标识列. 提示:  使用OVERRIDING SYSTEM VALUE覆盖. test1=# insert into test_identiy_1(id,name)overriding system value values (5,'ccc'); INSERT 0 1

 

generate by default:

?

1 2 3 4 5 6 7 8 9 10 11 12

create table  test_identiy_2 (     id int generated by default as identity (START WITH 1 INCREMENT BY 1)  primary key ,     name varchar(100) );   insert into  test_identiy_2(name) values ('aaa'); insert into  test_identiy_2(name) values ('bbb'); insert into  test_identiy_2(name) values ('ccc');   test1=# insert into test_identiy_2(id,name) values (5,'ccc'); INSERT 0 1

 

重置 Identity　　

?

1 2 3

重置Identity的方式有2种： 1.  ALTER TABLE test_identiy_1 ALTER COLUMN id RESTART WITH 100; 2.  TRUNCATE table test_identiy_1 RESTART IDENTITY;