postgres的序列（Sequence）的使用 |
您所在的位置：网站首页 › plsql创建序列 › postgres的序列（Sequence）的使用 |
postgres的序列（Sequence）的使用
|
介绍
序列对象（也叫序列生成器）就是用CREATE SEQUENCE 创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。 二、创建序列方法一：直接在表中指定字段类型为serial 类型 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=#方法二：先创建序列名称，然后在新建的表中列属性指定序列就可以了，该列需int 类型 创建序列的语法： 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 } ] 实例 ： 1、查询数据库中有哪些序列 --查看数据库中有哪些序列 --r =普通表， i =索引，S =序列，v =视图，m =物化视图， c =复合类型，t = TOAST表，f =外部表 select * from pg_class where relkind='S'2、查找Sequence中的值 SELECT nextval(‘seq_commodity’)； 这里nextval表示下一个值 3、查看表中的 序列 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 |4、查看序列属性 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; 序列应用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 ----+------- | David | Sandy (2 rows)1.2 数据迁移后更新序列 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 ----+-------- | Sandy | David | Eagle | Miles | Simon | Rock | Peter | Sally | Nicole | Monica | 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 --------- (1 row) david=# select nextval('tbl_xulie_id_seq'); nextval --------- (1 row) david=# select nextval('tbl_xulie_id_seq'); nextval --------- (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 -------- (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 ----+-------- | Sandy | David | Eagle | Miles | Simon | Rock | Peter | Sally | Nicole | Monica | Renee | Flash (12 rows) david=# select nextval('tbl_xulie_id_seq'); nextval --------- (1 row) 序列函数下面序列函数，为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法。 1.2 查看序列最近使用值 david=# select nextval('tbl_xulie_id_seq'); nextval --------- (1 row) david=# select currval('tbl_xulie_id_seq'); currval --------- (1 row) david=# select currval('tbl_xulie_id_seq'); currval --------- (1 row) 重置序列1、1方法一：使用序列函数 a. setval(regclass, bigint) david=# truncate tbl_xulie; TRUNCATE TABLE david=# select setval('tbl_xulie_id_seq', 1); setval -------- (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 ----+------- | Sandy | David (2 rows) david=# select currval('tbl_xulie_id_seq'); currval --------- (1 row) david=# select nextval('tbl_xulie_id_seq'); nextval --------- (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 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 ----+------- | Sandy | David (2 rows)效果同a. setval(regclass, bigint) b.2 setval(regclass, bigint, false) 方法二：修改序列 修改序列的语法： 1、1 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实例： 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 ----+------- | David | Sandy (2 rows) david=# select nextval('tbl_xulie_id_seq'); nextval --------- (1 row) 删除序列语法： DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]当有表字段使用到PG序列时，不能直接删除。 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说明：对于序列是由建表时指定serial 创建的，删除该表的同时，对应的序列也会被删除。 2、PostgreSQL数据库实现表字段的自增在使用Mysql时，创建表结构时可以通过关键字auto_increment来指定主键是否自增。但在Postgresql数据库中，虽然可以实现字段的自增，但从本质上来说却并不支持Mysql那样的自增。 Postgresql的自增机制 Postgresql中字段的自增是通过序列来实现的。整体机制是：1、序列可以实现自动增长；2、表字段可以指定默认值。3、结合两者，将默认值指定为自增序列便实现了对应字段值的自增。 Postgresql提供了三种serial数据类型：smallserial，serial，bigserial。它们与真正的类型有所区别，在创建表结构时会先创建一个序列，并将序列赋值给使用的字段。 也就是说，这三个类型是为了在创建唯一标识符列时方便使用而封装的类型。 bigserial创建一个bigint类型的自增，serial创建一个int类型的自增，smallserial创建一个smallint类的自增。 自增方式一示例 使用示例如下： create table biz_test(id serial PRIMARY KEY,name varchar);此时生成的表结构为： aa=# \d biz_test Table "public.biz_test" Column | Type | Modifiers --------+-------------------+------------------------------------------------------- id | integer | not null default nextval('biz_test_id_seq'::regclass) name | character varying | Indexes: "biz_test_pkey" PRIMARY KEY, btree (id)我们可以看到ID字段默认值为nextval(‘biz_test_id_seq’::regclass)。也就是说，在执行创建语句时首先创建了一个以“表名”+"_id_seq"的序列。然后再将该序列赋值给id字段。对应序列的类型为Integer类型。 此时，通过一条insert语句来验证一下是否实现了自增。 aa=# insert into biz_test(name) values('Tom'); INSERT 0 1执行查询语句查看插入的数据： aa=# insert into biz_test(name) values('Tom'); INSERT 0 1 aa=# select * from biz_test; id | name ----+------ 1 | Tom (1 row)发送数据的确插入成功，并实现了id的自增。 自增方式二示例通过上面的示例可以衍生出另外一种实现方式。既然使用默认的三种类型可以完成自增的实现，那么将对应的底层实现进行拆分，是不是也可以实现自增的效果呢？ 第一步：创建一个序列 aa=# create sequence biz_test_id_seq; CREATE SEQUENCE第二步，创建表结构时将该序列设置为字段的默认值 aa=# create table biz_test(id integer primary key default nextval('biz_test_id_seq')); CREATE TABLE这样，同样实现了字段的自增效果。 aa=# \d biz_test Table "public.biz_test" Column | Type | Modifiers --------+---------+------------------------------------------------------- id | integer | not null default nextval('biz_test_id_seq'::regclass) Indexes: "biz_test_pkey" PRIMARY KEY, btree (id)针对第二步，如果建表的时并没有设置该字段为默认值，可以后续添加该字段为自增，使用alter语句来进行修改。 ALTER TABLE ONLY public.biz_test ALTER COLUMN id SET DEFAULT nextval('public.biz_test_id_seq'::regclass); 创建序列的语法上面创建序列时使用了默认值，如果需要指定序列的起始值、步长等参数，可以使用如下语句进行序列的创建。 CREATE SEQUENCE public.biz_test_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;上述语法其实已经很明显了，START WITH指定起始值，INCREMENT BY指定增长的步长。 Postgresql查找索引的方法与Mysql也不一样，对应的查询语句是： select * from pg_indexes where tablename='biz_test'; schemaname | tablename | indexname | tablespace | indexdef ------------+-----------+---------------+------------+----------------------------------------------------------------------- public | biz_test | biz_test_pkey | | CREATE UNIQUE INDEX biz_test_pkey ON public.biz_test USING btree (id) (1 row)或者： select * from pg_statio_all_indexes where relname='biz_test'; relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit -------+------------+------------+----------+---------------+---------------+-------------- 20753 | 20757 | public | biz_test | biz_test_pkey | 0 | 0 (1 row)关于PostgreSQL数据库实现表字段的自增就讲这么多，在学习该项技术时给我最大的启发就是：实现同一功能的不同技术的横向对比，是拓展多维度解决思路的利器。 扩展 函数 --执行函数 select sequence_reset(); --删除函数（执行完自定义函数后一般给他删除掉，不影响其他值） drop function public.sequence_reset();查看的序列是否被数据库中的其他对象引用 pigdb=# SELECT p.relname, a.adsrc FROM pg_class p JOIN pg_attrdef a on (p.relfilenode = a.adrelid) WHERE a.adsrc ~ 'shipments_ship_id_seq'; relname | adsrc -----------+-------------------------------------------- shipments | nextval('shipments_ship_id_seq'::regclass) (1 row)这里检查到 shipments_ship_id_seq 序列被 shipments 引用。你可以把这个序列名替换成任何一个你像查看的序列；或者不添加任何条件查看当前数据库中所有序列的引用。 一次序列删除： pigdb=# DROP TABLE shipments; DROP TABLE pigdb=# DROP SEQUENCE shipments_ship_id_seq; DROP SEQUENCE参考链接 ： 七、参考资料 PostgreSQL - 序列（Sequence） ： https://blog.csdn.net/weixin_39211722/article/details/89349457?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task PostgreSQL官方说明：http://www.postgresql.org/docs/9.2/static/functions-sequence.html PostgreSQL: 数据迁移之序列问题：http://francs3.blog.163.com/blog/static/40576727201281351925766/ https://blog.csdn.net/hbn1326317071/article/details/84637293 PostgreSQL 序列（SEQUENCE） ： https://www.cnblogs.com/mchina/archive/2013/04/10/3012493.html |
1、1查看下一个序列值【本文地址】
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |