不同于 Oracle：SEQUENCE 的区别
前言
在使用 Oracle 数据库 SEQUENCE 功能时，发现 Oracle 对边界处理比较奇怪。刚好 GreatSQL 也支持 SEQUENCE，就拿来一起比较一下。
先说结论：GreatSQL 的使用基本和 Oracle 基本一致，但是对 START WITH 的边界限制有所不同。
本次测试使用数据库的版本号
# Oracle版本BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production
# GreatSQL版本greatsql> \S...Server version: 8.0.32-25 GreatSQL, Release 25, Revision 79f57097e3f...1 row in set (0.00 sec)
复制代码
SEQUENCE 使用介绍
SEQUENCE 有以下几个常用的参数
INCREMENT BY 怎么用
INCREMENT BY 的值大于 0 时，为递增序列
INCREMENT BY 的值小于 0 时，为递减序列
何时能使用 NOMINVALUE &NOMINVALUE
INCREMENT BY 的值大于 0 时（递增序列），可以用 NOMAXVALUE；
INCREMENT BY 的值小于 0 时（递减序列），可以用 NOMINVALUE。
To create a sequence that increments without bound, for ascending sequences, omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify the NOMINVALUE.
CYCLE/NOCYCLE
如果是CYCLE，当序列的值超出设定的范围时，会从最大值/最小值开始重新进行循环。
递增数列从最小值开始循环，递减数列从最大值开始循环。
oracle> CREATE SEQUENCE seq1START WITH 101minvalue 100INCREMENT BY -10MAXVALUE 130nocache CYCLE;
#多次执行oracle> select seq1.nextval from dual;#返回值依次为：101->130->120->110>100
复制代码
Oracle SEQUENCE 特性
START WITH 边界
默认情况下是认为 MINVALUE >= START WITH >= MAXVALUE，超出区间就不能创建SEQUENCE
START WITH比MINVALUE小创建失败：
oracle> create SEQUENCE MY_FIRST_SEQUENCEstart with -2increment by -1minvalue 1maxvalue 100nocyclenocache; 2 3 4 5 6 7 create SEQUENCE MY_FIRST_SEQUENCE*ERROR at line 1:ORA-04006: START WITH ???? MINVALUE
复制代码
START WITH比MAXVALUE大：
oracle> create SEQUENCE MY_SECOND_SEQUENCEstart with 101increment by -1minvalue 1maxvalue 100nocyclenocache; 2 3 4 5 6 7 create SEQUENCE MY_SECOND_SEQUENCE*ERROR at line 1:ORA-04008: START WITH ???? MAXVALUE
复制代码
特殊情况
在使用 SEQUENCE 的时候发现有两种特殊情况：
一 、当 INCREMENT BY < 0 处于递减数列时
递减数列，START WITH 比 MINVALUE小 1 的时候，SEQUENCE 还能正常创建：
oracle> create SEQUENCE MY_FIRST_SEQUENCEstart with -2increment by -1minvalue -1maxvalue 100nocyclenocache; 2 3 4 5 6 7 Sequence created.
复制代码
但是 SEQUENCE 是 NOCYCLE，创建后不能使用：
oracle> select MY_FIRST_SEQUENCE.nextval from dual; select MY_FIRST_SEQUENCE.nextval from dual *ERROR at line 1:ORA-08004: ?? MY_FIRST_SEQUENCE.NEXTVAL goes below MINVALUE ?????
复制代码
START WITH 比MINVALUE小太多就不能创建了：
oracle> create SEQUENCE MY_FIRST_SEQUENCEstart with -3increment by -1minvalue -1maxvalue 100nocyclenocache; 2 3 4 5 6 7 create sequence MY_FIRST_SEQUENCE*ERROR at line 1:ORA-04006: START WITH ???? MINVALUE
oracle> drop SEQUENCE MY_FIRST_SEQUENCE;
Sequence dropped.
oracle> create SEQUENCE MY_FIRST_SEQUENCEstart with 101increment by -1minvalue 1maxvalue 100nocyclenocache; 2 3 4 5 6 7 create sequence MY_FIRST_SEQUENCE*ERROR at line 1:ORA-04008: START WITH ???? MAXVALUE
oracle> create sequence MY_FIRST_SEQUENCEstart with -1increment by -1minvalue 1maxvalue 100nocyclenocache; 2 3 4 5 6 7 create sequence MY_FIRST_SEQUENCE*ERROR at line 1:ORA-04006: START WITH ???? MINVALUE
复制代码
二、当 INCREMENT BY > 0 处于递增数列时
递增数列时情况相反
START WITH比MAXVALUE大 1 就能创建
oracle> create sequence MY_FIRST_SEQUENCEstart with 101increment by 1minvalue 1maxvalue 100nocyclenocache; 2 3 4 5 6 7
Sequence created.
复制代码
但是 SEQUENCE 为 NOCYCLE，创建后不能使用：
oracle> select MY_FIRST_SEQUENCE.nextval from dual;select MY_FIRST_SEQUENCE.nextval from dual *ERROR at line 1:ORA-08004: ?? MY_FIRST_SEQUENCE.NEXTVAL exceeds MAXVALUE ?????
复制代码
sequenceSpecify the name of the sequence to be created. The name must satisfy the requirements listed in "Database Object Naming Rules".If you specify none of the clauses INCREMENT BY through GLOBAL, then you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with ‐1 and decreases with no lower limit.To create a sequence that increments without bound, for ascending sequences, omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify the NOMINVALUE.To create a sequence that stops at a predefined limit, for an ascending sequence, specify a value for the MAXVALUE parameter. For a descending sequence, specify a value for the MINVALUE parameter. Also specify NOCYCLE. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify CYCLE.
GreatSQL 特性
GreatSQL 的使用就比较严格了： MINVALUE >= START WITH >= MAXVALUE
没发现像 Oracle 那样的特殊情况
greatsql> create sequence MY_FIRST_SEQUENCE -> start with -1 -> increment by 1 -> minvalue 1 -> maxvalue 100 -> nocycle -> nocache;ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!greatsql> create sequence MY_FIRST_SEQUENCE -> start with 101 -> increment by 1 -> minvalue 1 -> maxvalue 100 -> nocycle -> nocache;ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!greatsql> create sequence MY_FIRST_SEQUENCE -> start with 102 -> increment by 1 -> minvalue 1 -> maxvalue 100 -> nocycle -> nocache;ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!greatsql> create sequence MY_FIRST_SEQUENCE -> start with 101 -> increment by -1 -> minvalue 1 -> maxvalue 100 -> nocycle -> nocache;ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!greatsql> create sequence MY_FIRST_SEQUENCE -> start with -1 -> increment by -1 -> minvalue 1 -> maxvalue 100 -> nocycle -> nocache;ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!greatsql> create sequence MY_FIRST_SEQUENCE -> start with 0 -> increment by -1 -> minvalue 1 -> maxvalue 100 -> nocycle -> nocache;ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!greatsql> drop sequence MY_FIRST_SEQUENCE;ERROR 1046 (3D000): No database selectedgreatsql> create sequence MY_FIRST_SEQUENCE -> start with -10 -> increment by -1 -> minvalue -9 -> maxvalue 100 -> nocycle -> nocache;ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
复制代码
总结
GreatSQL 和 Oracle 对 START WITH 的边界定义基本一致，都是 MINVALUE >= START WITH >= MAXVALUE，但是 Oracle 会有两个特殊情况。
相关文档
评论