不同於Oracle：SEQUENCE的區別

GreatSQL發表於2024-04-08
Oracle

不同於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 Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL 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 有以下幾個常用的引數

引數名 介紹
START WITH 起始值
INCREMENT BY 步長
MINVALUE/NOMINVALUE 最小值
MAXVALUE/NOMINVALUE 最大值
CYCLE/NOCYCLE 是否回收
CACHE/NOCACHE （cache效能好但有丟資料的風險）

INCREMENT BY 怎麼用

INCREMENT BY 的值大於0時，為遞增序列

INCREMENT BY 的值小於0時，為遞減序列

何時能使用NOMINVALUE &NOMINVALUE

  1. INCREMENT BY的值大於0時（遞增序列），可以用NOMAXVALUE；
  2. 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 seq1
START WITH 101
minvalue 100
INCREMENT BY -10
MAXVALUE 130
nocache
 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_SEQUENCE
start with -2
increment by -1
minvalue 1
maxvalue 100
nocycle
nocache;  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_SEQUENCE
start with 101
increment by -1
minvalue 1
maxvalue 100
nocycle
nocache;   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_SEQUENCE
start with -2
increment by -1
minvalue -1
maxvalue 100
nocycle
nocache;
  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_SEQUENCE
start with -3
increment by -1
minvalue -1
maxvalue 100
nocycle
nocache;   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_SEQUENCE
start with 101
increment by -1
minvalue 1
maxvalue 100
nocycle
nocache;  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_SEQUENCE
start with -1
increment by -1
minvalue 1
maxvalue 100
nocycle
nocache;  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_SEQUENCE
start with 101
increment by 1
minvalue 1
maxvalue 100
nocycle
nocache;  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 ?????

sequence
Specify 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 selected
greatsql> 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 會有兩個特殊情況。

相關文件

  • SEQUENCE Oracle文件：

    • https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-SEQUENCE.html#GUID-E9C78A8C-615A-4757-B2A8-5E6EFB130571

    • https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Sequence-Pseudocolumns.html

  • GreatSQL SEQUENCE文件：

    • https://greatsql.cn/docs/8032-25/user-manual/5-enhance/sql-compat/5-3-easyuse-ora-syntax-sequence.html

  • ORA-04013,CACHE 值必須小於CYCLE值；解決方案

    • https://www.cnblogs.com/PingPo/p/14312384.html

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章