不同于Oracle：SEQUENCE的区别

恰克与飞鸟

不同于Oracle：SEQUENCE的区别

前言

在使用Oracle数据库SEQUENCE功能时，发现Oracle对边界处理比较奇怪。刚好GreatSQL也支持SEQUENCE，就拿来一起比较一下。
先说结论：GreatSQL 的使用基本和Oracle基本一致，但是对 START WITH 的边界限制有所不同。
本次测试使用数据库的版本号

1. # Oracle版本
2. BANNER
3. --------------------------------------------------------------------------------
4. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
5. PL/SQL Release 11.2.0.4.0 - Production
6. CORE    11.2.0.4.0      Production
7. TNS for Linux: Version 11.2.0.4.0 - Production
8. NLSRTL Version 11.2.0.4.0 - Production
10. # GreatSQL版本
11. greatsql> \S
12. ...
13. Server version:        8.0.32-25 GreatSQL, Release 25, Revision 79f57097e3f
14. ...
15. 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

• 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，当序列的值超出设定的范围时，会从最大值/最小值开始重新进行循环。
递增数列从最小值开始循环，递减数列从最大值开始循环。

1. oracle> CREATE SEQUENCE seq1
2. START WITH 101
3. minvalue 100
4. INCREMENT BY -10
5. MAXVALUE 130
6. nocache
7. CYCLE;
9. #多次执行
10. oracle> select seq1.nextval from dual;
11. #返回值依次为：
12. 101->130->120->110>100

复制代码

Oracle SEQUENCE 特性

START WITH 边界

默认情况下是认为 MINVALUE >= START WITH >= MAXVALUE，超出区间就不能创建SEQUENCE
START WITH比MINVALUE小创建失败：

1. oracle> create SEQUENCE MY_FIRST_SEQUENCE
2. start with -2
3. increment by -1
4. minvalue 1
5. maxvalue 100
6. nocycle
7. nocache;  2    3    4    5    6    7  
8. create SEQUENCE MY_FIRST_SEQUENCE
9. *
10. ERROR at line 1:
11. ORA-04006: START WITH ???? MINVALUE

复制代码

START WITH比MAXVALUE大：

1. oracle> create SEQUENCE MY_SECOND_SEQUENCE
2. start with 101
3. increment by -1
4. minvalue 1
5. maxvalue 100
6. nocycle
7. nocache;   2    3    4    5    6    7  
8. create SEQUENCE MY_SECOND_SEQUENCE
9. *
10. ERROR at line 1:
11. ORA-04008: START WITH ???? MAXVALUE

复制代码

特殊情况

在使用SEQUENCE的时候发现有两种特殊情况：
一 、当INCREMENT BY < 0 处于递减数列时
递减数列，START WITH 比 MINVALUE小1 的时候，SEQUENCE 还能正常创建：

1. oracle> create SEQUENCE MY_FIRST_SEQUENCE
2. start with -2
3. increment by -1
4. minvalue -1
5. maxvalue 100
6. nocycle
7. nocache;
8.   2    3    4    5    6    7  
9. Sequence created.

复制代码

但是SEQUENCE 是 NOCYCLE，创建后不能使用：

1. oracle> select MY_FIRST_SEQUENCE.nextval from dual;
2. select MY_FIRST_SEQUENCE.nextval from dual
3.         *
4. ERROR at line 1:
5. ORA-08004: ?? MY_FIRST_SEQUENCE.NEXTVAL goes below MINVALUE ?????

复制代码

START WITH 比MINVALUE小太多就不能创建了：

1. oracle> create SEQUENCE MY_FIRST_SEQUENCE
2. start with -3
3. increment by -1
4. minvalue -1
5. maxvalue 100
6. nocycle
7. nocache;   2    3    4    5    6    7  
8. create sequence MY_FIRST_SEQUENCE
9. *
10. ERROR at line 1:
11. ORA-04006: START WITH ???? MINVALUE
13. oracle> drop SEQUENCE MY_FIRST_SEQUENCE;
15. Sequence dropped.
17. oracle> create SEQUENCE MY_FIRST_SEQUENCE
18. start with 101
19. increment by -1
20. minvalue 1
21. maxvalue 100
22. nocycle
23. nocache;  2    3    4    5    6    7  
24. create sequence MY_FIRST_SEQUENCE
25. *
26. ERROR at line 1:
27. ORA-04008: START WITH ???? MAXVALUE
29. oracle> create sequence MY_FIRST_SEQUENCE
30. start with -1
31. increment by -1
32. minvalue 1
33. maxvalue 100
34. nocycle
35. nocache;  2    3    4    5    6    7  
36. create sequence MY_FIRST_SEQUENCE
37. *
38. ERROR at line 1:
39. ORA-04006: START WITH ???? MINVALUE

复制代码

二、当INCREMENT BY > 0 处于递增数列时
递增数列时情况相反
START WITH比MAXVALUE大1就能创建

1. oracle> create sequence MY_FIRST_SEQUENCE
2. start with 101
3. increment by 1
4. minvalue 1
5. maxvalue 100
6. nocycle
7. nocache;  2    3    4    5    6    7  
9. Sequence created.

复制代码

但是 SEQUENCE 为 NOCYCLE，创建后不能使用：

1. oracle> select MY_FIRST_SEQUENCE.nextval from dual;
2. select MY_FIRST_SEQUENCE.nextval from dual
3.        *
4. ERROR at line 1:
5. 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那样的特殊情况

1. greatsql> create sequence MY_FIRST_SEQUENCE
2.     -> start with -1
3.     -> increment by 1
4.     -> minvalue 1
5.     -> maxvalue 100
6.     -> nocycle
7.     -> nocache;
8. ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
9. greatsql> create sequence MY_FIRST_SEQUENCE
10.     -> start with 101
11.     -> increment by 1
12.     -> minvalue 1
13.     -> maxvalue 100
14.     -> nocycle
15.     -> nocache;
16. ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
17. greatsql> create sequence MY_FIRST_SEQUENCE
18.     -> start with 102
19.     -> increment by 1
20.     -> minvalue 1
21.     -> maxvalue 100
22.     -> nocycle
23.     -> nocache;
24. ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
25. greatsql> create sequence MY_FIRST_SEQUENCE
26.     -> start with 101
27.     -> increment by -1
28.     -> minvalue 1
29.     -> maxvalue 100
30.     -> nocycle
31.     -> nocache;
32. ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
33. greatsql> create sequence MY_FIRST_SEQUENCE
34.     -> start with -1
35.     -> increment by -1
36.     -> minvalue 1
37.     -> maxvalue 100
38.     -> nocycle
39.     -> nocache;
40. ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
41. greatsql> create sequence MY_FIRST_SEQUENCE
42.     -> start with 0
43.     -> increment by -1
44.     -> minvalue 1
45.     -> maxvalue 100
46.     -> nocycle
47.     -> nocache;
48. ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
49. greatsql> drop sequence MY_FIRST_SEQUENCE;
50. ERROR 1046 (3D000): No database selected
51. greatsql> create sequence MY_FIRST_SEQUENCE
52.     -> start with -10
53.     -> increment by -1
54.     -> minvalue -9
55.     -> maxvalue 100
56.     -> nocycle
57.     -> nocache;
58. 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
来源:https://www.cnblogs.com/greatsql/p/18120493
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！