当前位置: 首页 > news >正文

PostgreSQL序列:创建、管理与高效应用指南

一、引言

在PostgreSQL中,序列(Sequence)是一种用于生成唯一标识符的数据库对象。它们常常被用于为主键字段提供连续且唯一的值,特别是在创建新记录时。序列提供了一种机制,能够确保每次调用都能返回一个唯一的值,通常用于数据库表中的主键字段,以保证每条记录的唯一性。

二、序列在数据库设计中的关键作用

保证数据唯一性:序列能够生成连续的唯一值,确保每个记录都有一个唯一的标识符,这对于维护数据库数据的一致性和完整性至关重要。

简化应用开发:通过序列,应用程序可以自动获取新的唯一标识符,无需编写额外的逻辑来处理生成和分配唯一值,简化了开发过程。

易于管理和扩展:序列的创建和管理相对简单,可以根据需要调整序列的属性和行为,例如设置起始值、递增值、最大值和最小值等。

三、创建序列

使用SERIALBIGSERIAL声明字段类型

在PostgreSQL中,你可以直接在创建表的时候,使用SERIALBIGSERIAL关键字来声明一个序列。例如:

create table test(id serial,name varchar(50)
);

在上面的例子中,id字段被声明为SERIAL,这实际上意味着在创建表的同时,PostgreSQL会自动为你创建一个名为test_id_seq的序列,并为id字段设置默认值为从该序列中获取下一个值。
查看创建的表

postgres=# \d test;Table "public.test"Column |         Type          | Collation | Nullable |             Default
--------+-----------------------+-----------+----------+----------------------------------id     | integer               |           | not null | nextval('test_id_seq'::regclass)name   | character varying(50) |           |          |

可以看到数据库默认给这个自增序列起了一个test_id_seq的名字,来查一下信息:

postgres=# \d+ test_id_seqSequence "public.test_id_seq"Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.test.id

稍微解释一下:

  • Type: 这是序列生成值的类型。在这个例子中,它是 integer,意味着序列生成的是整数。

  • Start: 这是序列的起始值。在这个例子中,起始值是 1。

  • Minimum: 这是序列可以生成的最小值。在这个例子中,最小值是 1。

  • Maximum: 这是序列可以生成的最大值。在这个例子中,最大值是 2147483647,这是 integer 类型在 PostgreSQL 中的最大值。

  • Increment: 这是每次调用 nextval 函数时,序列值增加的数量。在这个例子中,递增值是 1,意味着每次调用 nextval 都会增加 1。

  • Cycles?: 这表示序列是否应该循环。如果设置为 yes,当序列达到其最大值时,它会回到最小值并继续循环。在这个例子中,它是 no,意味着当序列达到最大值时,它不会循环,并且任何后续的 nextval 调用都会返回一个错误。

  • Cache: 这是一个缓存值,用于存储预先生成的序列值。当调用 nextval 时,如果缓存中有值,它会立即返回这些值,从而提高性能。在这个例子中,缓存大小是 1,意味着每次调用 nextval 都会生成一个新值,不会从缓存中获取。

  • Owned by: 这表示哪个表的主键字段与这个序列关联。在这个例子中,public.test.id 表示 test 表的 id 字段使用了这个序列作为默认值。

使用CREATE SEQUENCE语句

如果你想要更多的控制序列的行为,例如设置起始值、递增值、最大值和最小值等,或者你想给不同的字段使用同一个序列,那么可以使用CREATE SEQUENCE语句来手动创建序列。

postgres=# create sequence test_seq;
CREATE SEQUENCE
postgres=# create table test3(id int default nextval('test_seq'));
CREATE TABLE
postgres=# \d+ test3;Table "public.test3"Column |  Type   | Collation | Nullable |            Default            | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+-------------------------------+---------+-------------+--------------+-------------id     | integer |           |          | nextval('test_seq'::regclass) | plain   |             |              |
Access method: heap

在这个例子中,我们首先创建了一个名为test_seq的序列,然后我们创建了一个名为test3的表,其中的id字段使用了这个序列作为默认值,并查看了这个表的信息,可以看到序列的信息。

这个方式创建的序列有个问题,如果关联的表被删了,这个序列还是存在的。如下例子:

postgres=# drop table test3;
DROP TABLE
postgres=# \d+ test_seq;Sequence "public.test_seq"Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

删除test3后还是可以看到这个序列的信息,解释一下,如果创建序列的时候没有指定序列类型,默认是bigint的,最大值9223372036854775807 。可以使用DROP SEQUENCE test_seq;语法删除这个序列,如果不知道哪些序列是自由序列,可以使用以下sql查询数据库中的自由序列。

SELECT ns.nspname AS schema_name,seq.relname AS seq_name
FROM pg_class AS seq
JOIN pg_namespace ns ON seq.relnamespace = ns.oid
WHERE seq.relkind = 'S' AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE deptype = 'a' AND objid = seq.oid)
ORDER BY seq.relname;

序列的命名规则与约束

  • 序列名称必须以字母开头,可以包含字母、数字和下划线,但不能包含空格。

  • 序列名称不能与数据库中的其他对象(如表、索引等)重名。

  • 序列名称可以在同一schema内唯一,但不同的schema可以有相同名称的序列。

  • 序列的命名应该具有描述性,以便于理解和管理。

创建带有缓存的序列

postgres=# create sequence myseq cache 10;
CREATE SEQUENCE

在这个例子中,我们创建了一个名为myseq 的序列,并设置了缓存大小为10。这意味着当获取下一个序列值时,数据库会首先从缓存中获取,当缓存中的值用完后,再生成新的值并放入缓存。这样可以提高获取序列值的性能。
此时如果再开一个窗口登录数据库获取这个序列的下一个值,能得到11,这是因为前面已经加载了10个值到缓存中。

[postgres@pcp postgresql-15.8]$ psql -U postgres -p5432
psql (15.8)
Type "help" for help.postgres=# select nextval('myseq');nextval
---------11
(1 row)

序列常用方法

获取当前值:

要获取序列的当前值,你可以使用currval函数。但是,请注意,currval函数只有在以下情况下才有效:

  • 你已经对同一序列调用了nextval函数。
  • 你正在一个事务中,且该事务至少有一次nextval调用。如果以上条件不满足,currval会返回一个错误。

示例:

postgres=# select currval('myseq');currval
---------13
(1 row)

获取下一个值:

要获取序列的下一个值,你可以使用nextval函数。这个函数会返回序列的下一个值,并自动更新序列的当前值。

例如:

postgres=# select nextval('myseq');nextval
---------14
(1 row)

重置序列值:

在PostgreSQL中,你不能直接设置序列的当前值。但是,可以通过SETVAL函数来重置序列的当前值。

例如:

postgres=# select setval('myseq',10);setval
--------10
(1 row)postgres=# select currval('myseq');currval
---------10
(1 row)postgres=# select setval('myseq',15);setval
--------15
(1 row)postgres=# select currval('myseq');currval
---------15
(1 row)

在数据库管理中,序列(SEQUENCE)是一个非常重要的工具,它允许我们为数据库中的表生成唯一的标识符。无论是用于标识用户的唯一ID,还是用于跟踪时间戳的序列号,序列都扮演着至关重要的角色。

通过了解如何获取序列的当前值、下一个值,以及如何设置序列的值,我们可以更好地管理和控制数据库中的唯一标识符。这样,我们可以确保在插入新数据时,每个数据项都有一个独特的标识符,从而维护数据的一致性和完整性。

总的来说,序列是数据库管理中不可或缺的一部分,它让我们能够轻松地为表生成唯一的标识符,并有效地管理数据库中的数据。通过了解序列的工作原理,我们可以更加高效地使用数据库,并确保数据的准确性和完整性。


http://www.mrgr.cn/news/71858.html

相关文章:

  • java 打包后无法运行(签名、依赖冲突或编译配置不一致)
  • Python操作neo4j库py2neo使用之创建和查询(二)
  • 面试小札:Java的类加载过程和类加载机制。
  • Python 爬虫 (1)基础 | 目标网站
  • 大语言模型---什么是注意力机制?LlaMA 中注意力机制的数学定义
  • JavaEE之线程初阶(上)
  • 【MySQL】数据库知识突破:数据类型全解析与详解
  • Pandas中astype() 方法
  • 「Mac玩转仓颉内测版5」入门篇5 - Cangjie控制结构(上)
  • 大数据 ETL + Flume 数据清洗 — 详细教程及实例(附常见问题及解决方案)
  • 10款视频剪辑工具使用感受与适用场景推荐!!!!
  • kaggle 如何利用API下载数据集
  • Linux驱动开发(5):平台设备驱动
  • Java 网络通信之 Socket 编程全解析
  • 番外篇 | 关于YOLO11算法的改进点总结
  • 【java】如何理解线程安全问题
  • 轻松搭建自己的RAG知识库
  • 论文2—《基于柔顺控制的智能神经导航手术机器人系统设计》文献阅读分析报告
  • 代码训练营 day64|算法优化、带负权值图的最短路径
  • 一维前缀和/差分,二维前缀和/差分
  • 【时时三省】(C语言基础)函数介绍strtok
  • 概率论中的PMF、PDF和CDF
  • 关于CJS,AMD,CMD,UMD的了解
  • 推荐一款强大的行车记录仪播放器:Dashcam Viewer Plus
  • Java小型项目-音乐评论分析
  • 论文解读:CARAT