SQL 序列生成器

本篇介紹SQL:2016（ISO/IEC 9075:2016）標準中定義的序列生成器（Sequence generator）和相關操作，以及六種主流資料庫中的實作及差異：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite，

序列概述

在 SQL 中，序列生成器（簡稱序列）用于自動生成一系列不重復的數字，序列的資料型別通常是整數，包含一個最小值，一個最大值，一個起始值，一個增量值，以及一個回圈使用選項，

如果序列的增量是一個正整數，稱為升序序列，例如 1，3，5，7，…（增量為 2）；如果序列的增量是一個負整數，稱為降序序列，例如2，1，0，-1，…（增量為 -1），

由于序列可以生成不重復的值，因此通常用于主鍵值的生成；由于序列的值具有遞增特性，因此可以用于記錄各個操作發生的先后順序，

序列可以通過兩種方式進行定義：使用CREATE SEQUENCE陳述句創建一個外部序列；或者通過一個標識列（identity column）或自動增長列（auto increment column）隱式創建一個內部序列，

本文先介紹外部序列相關的操作，包括序列的創建、使用、修改和洗掉；最后介紹六種資料庫中實作的標識列或自增列功能，

創建序列

在 SQL 中使用CREATE SEQUENCE創建一個外部序列，標準語法如下：

CREATE SEQUENCE seq_name
  [ AS data_type ]
  [ START WITH s ]
  [ INCREMENT BY i ]
  [ MAXVALUE M | NO MAXVALUE ]
  [ MINVALUE m | NO MINVALUE ]
  [ CYCLE | NO CYCLE ]
;

其中，只有序列名稱 seq_name 是必須的引數；其他可選引數的意義如下：

AS data_type指定序列的資料型別，只能是整數型別；
START WITH s指定序列的初始值，s 是一個數值常量，默認為序列的最小值或者最大值；
INCREMENT BY i指定序列的增量值，i 是一個數值常量，負數表示降序序列，默認值為 1；
MAXVALUE M指定序列允許的最大值；NO MAXVALUE表示不指定最大值，通常由序列的資料型別或者資料庫產品自定義一個最大值；
MINVALUE m指定序列允許的最小值；NO MINVALUE表示不指定最小值，通常由序列的資料型別或者資料庫產品自定義一個最小值；
CYCLE表示當序列的取值到達最大值（最小值）時，是否重新回圈使用；NO CYCLE表示當序列到達最大值（最小值）時，無法生成新的序列值，默認為NO CYCLE，

MySQL 和 SQLite 目前還不支持創建序列物件，
Oracle 不支持指定序列的資料型別，默認使用 NUMBER 型別，

以下示例使用默認值創建一個名為 seq1 的序列：

-- For Oracle, SQL Server, PostgreSQL and Db2
CREATE SEQUENCE seq1;

對于 Oracle，以上陳述句創建一個從 1 開始，增量為 1，最小值為 1，最大值為 1028 - 1 的非回圈序列，

對于 SQL Server，以上陳述句創建一個從 -263 開始，增量為 1，最小值為 -263，最大值為 263 - 1 的非回圈序列，

對于 PostgreSQL，以上陳述句創建一個從 1 開始，增量為 1，最小值為 1，最大值為 263 - 1 的非回圈序列，

對于 Db2，以上陳述句創建一個從 1 開始，增量為 1，最小值為 1，最大值為 231 - 1 的非回圈序列，

以下陳述句創建一個從 10 開始，增量為 -2（降序），最小值為 0，最大值為 100 的回圈序列，

-- For Oracle, SQL Server, PostgreSQL and Db2
CREATE SEQUENCE seq2
 START WITH 10
 INCREMENT BY -2
 MAXVALUE 100
 MINVALUE 0
 CYCLE;

序列 seq2 的取值依次為 10，8，6，…，0，100，98，… ；序列取值到達最小值（0）之后，再次從最大值開始（100），

不同的資料庫產品針對 SQL 標準進行了一些專有的擴展，以下是常見的擴展選項，

CACHE

Oracle、SQL Server、PostgreSQL 以及 Db2 都支持序列的快取選項，例如：

-- For Oracle, SQL Server, PostgreSQL and Db2
CREATE SEQUENCE seq2
 START WITH 10
 INCREMENT BY -2
 MAXVALUE 100
 MINVALUE 0
 CYCLE
 CACHE 20;

使用 CACHE 選項可以減少獲取序列值的磁盤操作，可以提高序列生成的性能，不過，此時服務崩潰（如電源故障）可能導致快取中保留的序列號丟失，

使用序列

在 SQL 標準中，獲取序列下一個值的語法如下：

NEXT VALUE FOR seq_name

SQL Server 和 Db2 遵循 SQL 標準；
Oracle 使用偽列 seq_name.NEXTVAL 獲取下一個序列值；
PostgreSQL 使用函式 nextval(‘seq_name’) 獲取下一個序列，

　舉例說明，以下查詢使用序列 seq2 回傳一系列數值，

-- For SQL Server and Db2
SELECT NEXT VALUE FOR seq2 AS Seq,
       employee_id,
       first_name,
       last_name
  FROM employees;

-- For Oracle only
SELECT seq2.NEXTVAL AS Seq,
       employee_id,
       first_name,
       last_name
  FROM employees;

-- For PostgreSQL only
SELECT nextval('seq2') AS Seq,
       employee_id,
       first_name,
       last_name
  FROM employees;

查詢結果如下：

對于查詢結果中的每一行，即使存在同一個序列的多次呼叫，也只生成一次新值，例如

-- For SQL Server and Db2
SELECT NEXT VALUE FOR seq2 AS Seq, -- seq2.NEXTVAL for Oracle
       NEXT VALUE FOR seq2 AS Seq_2, -- nextval('seq2') for PostgreSQL
       employee_id,
       first_name,
       last_name
  FROM employees;

查詢結果中的每一行，欄位 Seq 和 Seq_2 都回傳了相同的值：

同樣可以在 INSERT 陳述句中使用序列的值，以下示例使用序列 seq2 為表 test_seq 生成主鍵值：

CREATE TABLE test_seq
    ( id   INTEGER NOT NULL PRIMARY KEY
    , name CHARACTER VARYING(20)
    ) ;

-- For SQL Server and Db2
INSERT INTO test_seq(id, name)
VALUES (NEXT VALUE FOR seq2, 'sql'); -- seq2.NEXTVAL for Oracle
INSERT INTO test_seq(id, name)
VALUES (NEXT VALUE FOR seq2, 'java'); -- nextval('seq2') for PostgreSQL
INSERT INTO test_seq(id, name)
VALUES (NEXT VALUE FOR seq2, 'c++');

SELECT id, name
  FROM test_seq;
id  |name |
----|-----|
96  |c++  |
98  |java |
100 |sql  |

此外，插入到表中的序列值有可能是存在間斷，例如在事務中生成了序列值，然后事務被回滾，已經使用的序列值將會被丟棄，如果使用了擴展的 CACHE 選項，當服務器故障時可能會丟失掉已經快取的序列值，

相關內容
關于資料庫自增欄位的 3 種實作方式，可以參考這篇文章，

參考資料：

Oracle 官方檔案；
SQL Server 官方檔案；
PostgreSQL 官方檔案；
Db2 官方檔案，

本文作者：「不剪發的Tony老師」

本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/SQL-sequence-generator.html

標籤：其他

上一篇：5.1 主鍵約束

下一篇：2022-8-15 資料庫 mysql 第一天