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