完结小说,完美世界辰东

主頁(yè) > 知識(shí)庫(kù) > 深入解讀PostgreSQL中的序列及其相關(guān)函數(shù)的用法

深入解讀PostgreSQL中的序列及其相關(guān)函數(shù)的用法

一、簡(jiǎn)介

序列對(duì)象（也叫序列生成器）就是用CREATE SEQUENCE 創(chuàng)建的特殊的單行表。一個(gè)序列對(duì)象通常用于為行或者表生成唯一的標(biāo)識(shí)符。

二、創(chuàng)建序列

方法一：直接在表中指定字段類(lèi)型為serial 類(lèi)型

david=# create table tbl_xulie (
david(# id serial,
david(# name text);
NOTICE: CREATE TABLE will create implicit sequence "tbl_xulie_id_seq" for serial column "tbl_xulie.id"
CREATE TABLE
david=#

方法二：先創(chuàng)建序列名稱(chēng)，然后在新建的表中列屬性指定序列就可以了，該列需int 類(lèi)型

創(chuàng)建序列的語(yǔ)法：

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]

實(shí)例：

david=# create sequence tbl_xulie2_id_seq increment by 1 minvalue 1 no maxvalue start with 1;   
CREATE SEQUENCE
david=# 
david=# create table tbl_xulie2 (
david(# id int4 not null default nextval('tbl_xulie2_id_seq'),
david(# name text);
CREATE TABLE
david=#

三、查看序列

david=# \d tbl_xulie
             Table "public.tbl_xulie"
 Column | Type  |            Modifiers            
--------+---------+--------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name  | text  | 

david=# \d tbl_xulie2
             Table "public.tbl_xulie2"
 Column | Type  |            Modifiers            
--------+---------+---------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name  | text  | 

david=#

查看序列屬性

david=# \d tbl_xulie_id_seq

 Sequence "public.tbl_xulie_id_seq"


 Column   | Type  |    Value    
---------------+---------+---------------------
 sequence_name | name  | tbl_xulie_id_seq
 last_value  | bigint | 1
 start_value  | bigint | 1
 increment_by | bigint | 1
 max_value   | bigint | 9223372036854775807
 min_value   | bigint | 1
 cache_value  | bigint | 1
 log_cnt    | bigint | 0
 is_cycled   | boolean | f
 is_called   | boolean | f
Owned by: public.tbl_xulie.id

david=# select * from tbl_xulie2_id_seq;

  sequence_name  | last_value | start_value | increment_by |   max_value   | min_value | cache_value | log_cnt | is_cycled | is_called 
-------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 tbl_xulie2_id_seq |     1 |      1 |      1 | 9223372036854775807 |     1 |      1 |    0 | f     | f
(1 row)

四、序列應(yīng)用

4.1 在INSERT 命令中使用序列

david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');   
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# select * from tbl_xulie;

 id | name 
----+-------
 1 | David
 2 | Sandy
(2 rows)

4.2 數(shù)據(jù)遷移后更新序列

david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# 
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Eagle');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Miles');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Simon');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Rock'); 
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Peter');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sally');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Nicole');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Monica');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Renee'); 
INSERT 0 1
david=# select * from tbl_xulie;

id | name 
----+--------
 15 | Sandy
 16 | David
 17 | Eagle
 18 | Miles
 19 | Simon
 20 | Rock
 21 | Peter
 22 | Sally
 23 | Nicole
 24 | Monica
 25 | Renee
(11 rows)

david=# copy tbl_xulie to '/tmp/tbl_xulie.sql';
COPY 11
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# alter sequence tbl_xulie_id_seq restart with 100;
ALTER SEQUENCE
david=# select currval('tbl_xulie_id_seq');
 currval

---------
   25
(1 row)

david=# select nextval('tbl_xulie_id_seq');
 nextval

---------
   100
(1 row)

david=# select nextval('tbl_xulie_id_seq');

 nextval 
---------
   101
(1 row)

david=# begin;
BEGIN
david=# copy tbl_xulie from '/tmp/tbl_xulie.sql';
COPY 11
david=# select setval('tbl_xulie_id_seq', max(id)) from tbl_xulie;
 setval

--------
   25
(1 row)

david=# end;
COMMIT
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Flash');
INSERT 0 1
david=# select * from tbl_xulie;

id | name 
----+--------
 15 | Sandy
 16 | David
 17 | Eagle
 18 | Miles
 19 | Simon
 20 | Rock
 21 | Peter
 22 | Sally
 23 | Nicole
 24 | Monica
 25 | Renee
 26 | Flash
(12 rows)

david=# select nextval('tbl_xulie_id_seq');
 nextval

---------
   27
(1 row)

五、序列函數(shù)

下面序列函數(shù)，為我們從序列對(duì)象中獲取最新的序列值提供了簡(jiǎn)單和并發(fā)讀取安全的方法。

5.1 查看下一個(gè)序列值

david=# select nextval('tbl_xulie_id_seq');
 nextval

---------
    3
(1 row)

david=# select nextval('tbl_xulie_id_seq');
 nextval

---------
    4
(1 row)

5.2 查看序列最近使用值

david=# select nextval('tbl_xulie_id_seq');
 nextval

---------
    4
(1 row)

david=# select currval('tbl_xulie_id_seq');
 currval

---------
    4
(1 row)

david=# select currval('tbl_xulie_id_seq');
 currval

---------
    4
(1 row)

5.3 重置序列

方法一：使用序列函數(shù)

a. setval(regclass, bigint)

david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1);
 setval

--------
   1
(1 row)

david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');         
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');   
INSERT 0 1
david=# select * from tbl_xulie;

 id | name 
----+-------
 2 | Sandy
 3 | David
(2 rows)

david=# select currval('tbl_xulie_id_seq');
 currval

---------
    3
(1 row)

david=# select nextval('tbl_xulie_id_seq');

 nextval 
---------
    4
(1 row)

b. setval(regclass, bigint, boolean)

b.1 setval(regclass, bigint, true)

david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1, true);
 setval

--------
   1
(1 row)

david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# select * from tbl_xulie;

 id | name 
----+-------
 2 | Sandy
 3 | David
(2 rows)

效果同a. setval(regclass, bigint)

b.2 setval(regclass, bigint, false)

david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1, false);
 setval

--------
   1
(1 row)

david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# select * from tbl_xulie;

 id | name 
----+-------
 1 | Sandy
 2 | David
(2 rows)

方法二：修改序列

修改序列的語(yǔ)法：

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ]
  [ RESTART [ [ WITH ] restart ] ]
  [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]
ALTER SEQUENCE name OWNER TO new_owner
ALTER SEQUENCE name RENAME TO new_name
ALTER SEQUENCE name SET SCHEMA new_schema

實(shí)例：

david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# alter sequence tbl_xulie_id_seq restart with 0;
ERROR: RESTART value (0) cannot be less than MINVALUE (1)
david=# alter sequence tbl_xulie_id_seq restart with 1;
ALTER SEQUENCE
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# select * from tbl_xulie;

id | name 
----+-------
 1 | David
 2 | Sandy
(2 rows)

david=# select nextval('tbl_xulie_id_seq');
 nextval

---------
    3
(1 row)

六、刪除序列

語(yǔ)法：


DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

當(dāng)有表字段使用到PG序列時(shí)，不能直接刪除。

david=# drop sequence tbl_xulie2_id_seq;
ERROR: cannot drop sequence tbl_xulie2_id_seq because other objects depend on it
DETAIL: default for table tbl_xulie2 column id depends on sequence tbl_xulie2_id_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.
david=# drop table tbl_xulie2;
DROP TABLE
david=# drop sequence tbl_xulie2_id_seq;
DROP SEQUENCE
david=#

說(shuō)明：對(duì)于序列是由建表時(shí)指定serial 創(chuàng)建的，刪除該表的同時(shí)，對(duì)應(yīng)的序列也會(huì)被刪除。

七、其他說(shuō)明
a.currval取得的是當(dāng)前會(huì)話(huà)的序列值，在當(dāng)前會(huì)話(huà)中該值不會(huì)因?yàn)槠渌麜?huì)話(huà)取了nextval而變化。會(huì)變化的是全局的last_value值,并且當(dāng)前會(huì)話(huà)中如果沒(méi)有讀過(guò)nextval值時(shí)直接讀currval是會(huì)報(bào)錯(cuò)的。
b.對(duì)于序列是由建表時(shí)指定serial時(shí)創(chuàng)建時(shí)，刪除該表的同時(shí)，對(duì)應(yīng)的序列也會(huì)被刪除。
c.表主鍵數(shù)據(jù)可以用跟表相關(guān)的序列，也可以用其他序列，但不推薦，只是PG默認(rèn)它沒(méi)錯(cuò)。
d.為使多用戶(hù)并發(fā)下同一個(gè)序列取值不會(huì)重復(fù)，nextval是不會(huì)rollback的，不過(guò)可以使用setval重置
如果一個(gè)序列對(duì)象是帶著缺省參數(shù)創(chuàng)建的，那么對(duì)它調(diào)用 nextval 將返回從1 開(kāi)始的后續(xù)的數(shù)值。其它的行為可以通過(guò)使用 CREATE SEQUENCE 命令里的特殊參數(shù)獲??；參閱其命令參考頁(yè)獲取更多信息。
e.為了避免從同一個(gè)序列獲取數(shù)值的當(dāng)前事務(wù)被阻塞， nextval 操作決不會(huì)回滾；也就是說(shuō)，一旦一個(gè)數(shù)值已經(jīng)被抓走，那么就認(rèn)為它已經(jīng)用過(guò)了，即使調(diào)用 nextval 的事務(wù)后面又退出了也一樣。這就意味著退出的事務(wù)可能在序列賦予的數(shù)值中留下"空洞"。 setval 操作也決不回滾。

您可能感興趣的文章:

postgresql 中的序列nextval詳解
PostgreSQL 序列綁定字段與不綁定字段的區(qū)別說(shuō)明
PostgreSQL 序列增刪改案例
postgresql重置序列起始值的操作
postgresql 實(shí)現(xiàn)更新序列的起始值
postgresql修改自增序列操作
PostgreSQL Sequence序列的使用詳解

標(biāo)簽：蚌埠烏海株洲珠海錦州衡陽(yáng) 晉城來(lái)賓

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《深入解讀PostgreSQL中的序列及其相關(guān)函數(shù)的用法》，本文關(guān)鍵詞深入,解讀,PostgreSQL,中的,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。