背景

在使用Mysql時(shí)，創(chuàng)建表結(jié)構(gòu)時(shí)可以通過關(guān)鍵字auto_increment來指定主鍵是否自增。但在Postgresql數(shù)據(jù)庫中，雖然可以實(shí)現(xiàn)字段的自增，但從本質(zhì)上來說卻并不支持Mysql那樣的自增。

在MySQL當(dāng)中，我們可以通過勾選來實(shí)現(xiàn)ID自增，我們的navcat如下圖所示：

pgsql沒有auto_increment這么一說，所以想要建立自增主鍵就得想想其他辦法了!

什么是序列

序列是 PostgreSQL 中的一種特殊對象，用于生成唯一的數(shù)字值，通常用于表的主鍵字段。序列保證了在并發(fā)插入時(shí)，生成的 ID 唯一且自增。

Postgresql的自增機(jī)制

在PostgreSQL中，實(shí)現(xiàn)表中的id列作為主鍵并設(shè)置為自增，通常涉及到使用序列（SEQUENCE）和設(shè)置默認(rèn)值的概念。

方式1：

使用SERIAL或BIGSERIAL數(shù)據(jù)類型是最簡便的方式，它們會(huì)自動(dòng)創(chuàng)建序列并設(shè)置默認(rèn)值。

使用SERIAL或BIGSERIAL數(shù)據(jù)類型時(shí)，PostgreSQL會(huì)自動(dòng)為我們創(chuàng)建一個(gè)序列并將它設(shè)為對應(yīng)字段的默認(rèn)值，這能極大簡化開發(fā)過程。

例如，當(dāng)我們希望id字段自增時(shí)，只需在建表時(shí)將id字段的類型指定為SERIAL（對于較小的整數(shù)）或BIGSERIAL（對于較大的整數(shù)），無需手動(dòng)創(chuàng)建序列或編寫額外的代碼來管理主鍵的增長。

方式2：

使用CREATE SEQUENCE與ALTER TABLE命令手動(dòng)創(chuàng)建序列并設(shè)置默認(rèn)值。

基本使用

使用SERIAL或BIGSERIAL數(shù)據(jù)類型

Postgresql中字段的自增是通過序列來實(shí)現(xiàn)的。

Postgresql提供了三種serial數(shù)據(jù)類型：smallserial，serial，bigserial。它們與真正的類型有所區(qū)別，在創(chuàng)建表結(jié)構(gòu)時(shí)會(huì)先創(chuàng)建一個(gè)序列，并將序列賦值給使用的字段。
這些屬性類似于 MySQL 數(shù)據(jù)庫支持的 AUTO_INCREMENT 屬性。

PostgreSQL的SERIAL不是真正的數(shù)據(jù)類型，而是在創(chuàng)建表時(shí)的一個(gè)快捷方式，用于自動(dòng)創(chuàng)建支持自增的序列。

如果尚未創(chuàng)建表，可用如下方法

方法一：

create table test_a (

  id serial,
  name character varying(128),
  constraint pk_test_a_id primary key( id)
);

方法二：

create table test_b(

  id serial PRIMARY KEY,
  name character varying(128)
); 

這兩種方法，會(huì)自動(dòng)創(chuàng)建名為表名_字段名_seq的序列，且MAXVALUE=9223372036854775807

這兩種方法用的是pg的serial類型實(shí)現(xiàn)自增，drop表的時(shí)候指定的序列也會(huì)drop掉。

這種使用方式，每次向表中插入新記錄時(shí)，id列將自動(dòng)增長，無需手動(dòng)指定id值。

手動(dòng)創(chuàng)建序列和設(shè)置默認(rèn)值

先創(chuàng)建主鍵表

create table test_c (

  id integer PRIMARY KEY,
  name character varying(128)
);  

使用CREATE SEQUENCE命令創(chuàng)建一個(gè)序列。例如：

CREATE SEQUENCE test_c_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

將序列賦值給主鍵
創(chuàng)建序列后，需要通過ALTER TABLE命令來顯式設(shè)置某列的默認(rèn)值為該序列的下一個(gè)值：

alter table test_c alter column id set default nextval('test_c_id_seq');

這種方法在drop表的時(shí)候序列不會(huì)隨著drop掉，這種方式，seq是一個(gè)獨(dú)立的東西，插入語句，不能忽略id字段。

實(shí)戰(zhàn)demo：PostgreSQL 手動(dòng)序列管理

• 刪除序列

DROP SEQUENCE IF EXISTS xxx_seq;

• DROP SEQUENCE: 刪除指定的序列。
• IF EXISTS: 如果序列不存在，則不會(huì)拋出錯(cuò)誤，而是安全地跳過此操作。
• xxx_seq: 指定要?jiǎng)h除的序列。

• 創(chuàng)建序列
  創(chuàng)建序列是生成唯一數(shù)字值的重要步驟。以下是創(chuàng)建序列的 SQL 示例。

CREATE SEQUENCE xxx_seq
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

• CREATE SEQUENCE: 創(chuàng)建一個(gè)新的序列。
• INCREMENT 1: 每次調(diào)用 nextval 時(shí)，序列的值增加 1。
• MINVALUE 1: 序列的最小值為 1。
• MAXVALUE 9223372036854775807: 序列的最大值，設(shè)置為 64 位整數(shù)的最大值。
• START 1: 序列的初始值為 1。
• CACHE 1: 指定緩存的序列值數(shù)量，可以提高性能。設(shè)置為 1 意味著每次只緩存一個(gè)值。

3. 使用序列
   一旦序列創(chuàng)建完成，您可以使用 nextval 函數(shù)獲取下一個(gè)值，并將其插入到表中。

INSERT INTO t_test (id, name)
VALUES (nextval('xxx_seq'), 'admin2');

nextval: 調(diào)用序列的下一個(gè)值。
將序列值作為 id 插入到 t_test 表中，確保每條記錄的 ID 唯一。

• 查看序列信息
  您可以查詢系統(tǒng)視圖以查看序列的當(dāng)前狀態(tài)。

SELECT * FROM xxx_seq;

• 說明
  此查詢將返回序列的當(dāng)前值、增量、最小值和最大值等信息。

設(shè)置序列的當(dāng)前值

SELECT setval('xxx_seq', 1, false);

• setval 是 PostgreSQL 提供的一個(gè)函數(shù)，用于設(shè)置序列的當(dāng)前值。
• xxx_seq 是一個(gè)標(biāo)識符，表示您要操作的序列。
• 1， 這是要設(shè)置的值。此語句將序列的當(dāng)前值設(shè)置為 1。
• false 這是一個(gè)布爾參數(shù)，指示下次調(diào)用 nextval 時(shí)序列的行為：
  • false: 表示下次調(diào)用 nextval 時(shí)，序列將返回 2（即在設(shè)置的值上加 1）。
  • true: 如果設(shè)置為 true，則下次調(diào)用 nextval 時(shí)將返回 1（即從設(shè)置的值開始）。

這條語句的效果是將序列 xxx_seq 的當(dāng)前值設(shè)置為 1，并且在下次獲取下一個(gè)值時(shí)，序列將返回 2。這通常用于重置序列，以確保新的 ID 從特定值開始，常見于數(shù)據(jù)清理或重新插入數(shù)據(jù)的場景。

工作常用總結(jié)

創(chuàng)建表時(shí)候自定義序列：id SERIAL PRIMARY KEY 和 id int8 NOT NULL DEFAULT nextval(…) 的主要區(qū)別

• SERIAL:
  SERIAL 是一種 PostgreSQL 特定的數(shù)據(jù)類型，它實(shí)際上是一個(gè)簡寫，用于創(chuàng)建一個(gè)整型列并同時(shí)創(chuàng)建一個(gè)序列。
  當(dāng)使用 SERIAL 時(shí)，PostgreSQL 會(huì)自動(dòng)為該列創(chuàng)建一個(gè)序列，并將該列的默認(rèn)值設(shè)置為調(diào)用該序列的 nextval 函數(shù)。
  例如，id SERIAL PRIMARY KEY 會(huì)隱式執(zhí)行下面的操作:

id int4 NOT NULL DEFAULT nextval('some_seq');

• int8 NOT NULL DEFAULT nextval(…):
  種方式更顯式地定義了列的類型和默認(rèn)值。
  您可以選擇使用 int8（64 位整數(shù)），并手動(dòng)指定要使用的序列名稱和行為。
  這種方式更加靈活，允許開發(fā)者在表定義中自定義序列的名稱和特性。
  通過顯式定義序列，您可以在多個(gè)表之間共享同一個(gè)序列，確保 ID 的唯一性，同時(shí)在需要時(shí)也能靈活地調(diào)整序列的行為。這種方式提供了更大的控制力，適用于復(fù)雜的數(shù)據(jù)庫設(shè)計(jì)需求。

id SERIAL PRIMARY KEY 和 id int8 NOT NULL DEFAULT nextval(…) 的主要區(qū)別

• 使用 SERIAL:

適合簡單場景，快速定義自增主鍵。
可能不夠靈活，無法自定義序列的行為或名稱。

• 使用 int8 和 nextval(…):

提供更大的控制和靈活性。
可以指定不同的序列名，允許在多個(gè)表中使用同一個(gè)序列，或者在需要時(shí)替換序列。

總結(jié)：如果只需要簡單的自增 ID，使用 SERIAL 更為簡潔。如果需要更大的靈活性和控制，尤其是在管理序列時(shí)，使用 int8 NOT NULL DEFAULT nextval(…) 是更好的選擇。

以上就是PostgreSQL設(shè)置主鍵自增的方法詳解的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL主鍵自增的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論