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

%s 个评论

要回复文章请先登录注册