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创建一个外部序列，标准语法如下：

1. CREATE SEQUENCE seq_name
2.   [ AS data_type ]
3.   [ START WITH s ]
4.   [ INCREMENT BY i ]
5.   [ MAXVALUE M | NO MAXVALUE ]
6.   [ MINVALUE m | NO MINVALUE ]
7.   [ CYCLE | NO CYCLE ]
8. ;

复制代码

其中，只有序列名称 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。

1. MySQL 和 SQLite 目前还不支持创建序列对象。
2. Oracle 不支持指定序列的数据类型，默认使用 NUMBER 类型。

复制代码

以下示例使用默认值创建一个名为 seq1 的序列：

1. -- For Oracle, SQL Server, PostgreSQL and Db2
2. 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 的循环序列。

1. -- For Oracle, SQL Server, PostgreSQL and Db2
2. CREATE SEQUENCE seq2
3. START WITH 10
4. INCREMENT BY -2
5. MAXVALUE 100
6. MINVALUE 0
7. CYCLE;

复制代码

序列 seq2 的取值依次为 10，8，6，…，0，100，98，… ；序列取值到达最小值（0）之后，再次从最大值开始（100）。
不同的数据库产品针对 SQL 标准进行了一些专有的扩展，以下是常见的扩展选项。
 
CACHE
Oracle、SQL Server、PostgreSQL 以及 Db2 都支持序列的缓存选项，例如：

1. -- For Oracle, SQL Server, PostgreSQL and Db2
2. CREATE SEQUENCE seq2
3. START WITH 10
4. INCREMENT BY -2
5. MAXVALUE 100
6. MINVALUE 0
7. CYCLE
8. CACHE 20;

复制代码

使用 CACHE 选项可以减少获取序列值的磁盘操作，可以提高序列生成的性能。不过，此时服务崩溃（如电源故障）可能导致缓存中保留的序列号丢失。
使用序列

在 SQL 标准中，获取序列下一个值的语法如下：

1. NEXT VALUE FOR seq_name

复制代码

1. SQL Server 和 Db2 遵循 SQL 标准；
2. Oracle 使用伪列 seq_name.NEXTVAL 获取下一个序列值；
3. PostgreSQL 使用函数 nextval(‘seq_name’) 获取下一个序列。

复制代码

　举例说明，以下查询使用序列 seq2 返回一系列数值。

1. -- For SQL Server and Db2
2. SELECT NEXT VALUE FOR seq2 AS Seq,
3.        employee_id,
4.        first_name,
5.        last_name
6.   FROM employees;

复制代码

1. -- For Oracle only
2. SELECT seq2.NEXTVAL AS Seq,
3.        employee_id,
4.        first_name,
5.        last_name
6.   FROM employees;

复制代码

1. -- For PostgreSQL only
2. SELECT nextval('seq2') AS Seq,
3.        employee_id,
4.        first_name,
5.        last_name
6.   FROM employees;

复制代码

查询结果如下：
　
 
 
 
对于查询结果中的每一行，即使存在同一个序列的多次调用，也只生成一次新值。例如

1. -- For SQL Server and Db2
2. SELECT NEXT VALUE FOR seq2 AS Seq, -- seq2.NEXTVAL for Oracle
3.        NEXT VALUE FOR seq2 AS Seq_2, -- nextval('seq2') for PostgreSQL
4.        employee_id,
5.        first_name,
6.        last_name
7.   FROM employees;<br>

复制代码

查询结果中的每一行，字段 Seq 和 Seq_2 都返回了相同的值：

 
 
 
同样可以在 INSERT 语句中使用序列的值，以下示例使用序列 seq2 为表 test_seq 生成主键值：

1. CREATE TABLE test_seq
2.     ( id   INTEGER NOT NULL PRIMARY KEY
3.     , name CHARACTER VARYING(20)
4.     ) ;

复制代码

1. -- For SQL Server and Db2
2. INSERT INTO test_seq(id, name)
3. VALUES (NEXT VALUE FOR seq2, 'sql'); -- seq2.NEXTVAL for Oracle
4. INSERT INTO test_seq(id, name)
5. VALUES (NEXT VALUE FOR seq2, 'java'); -- nextval('seq2') for PostgreSQL
6. INSERT INTO test_seq(id, name)
7. VALUES (NEXT VALUE FOR seq2, 'c++');
9. SELECT id, name
10.   FROM test_seq;
11. id  |name |
12. ----|-----|
13. 96  |c++  |
14. 98  |java |
15. 100 |sql  |<br>

复制代码

此外，插入到表中的序列值有可能是存在间断，例如在事务中生成了序列值，然后事务被回滚，已经使用的序列值将会被丢弃。如果使用了扩展的 CACHE 选项，当服务器故障时可能会丢失掉已经缓存的序列值。
相关内容
关于数据库自增字段的 3 种实现方式，可以参考这篇文章。
参考资料：
Oracle 官方文档；
SQL Server 官方文档；
PostgreSQL 官方文档；
Db2 官方文档。

本文作者：「不剪发的Tony老师」



免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！