phpcmsv9蓝色简洁下载网站模板,wordpress搭建电商,如何做网站seo韩小培,wordpress 展示微博不同于Oracle#xff1a;SEQUENCE的区别
前言
在使用Oracle数据库SEQUENCE功能时#xff0c;发现Oracle对边界处理比较奇怪。刚好GreatSQL也支持SEQUENCE#xff0c;就拿来一起比较一下。
先说结论#xff1a;GreatSQL 的使用基本和Oracle基本一致#xff0c;但是对 ST…不同于OracleSEQUENCE的区别
前言
在使用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/NOMAXVALUE最大值CYCLE/NOCYCLE是否回收CACHE/NOCACHEcache性能好但有丢数据的风险
INCREMENT BY 怎么用
INCREMENT BY 的值大于0时为递增序列
INCREMENT BY 的值小于0时为递减序列
何时能使用NOMINVALUE NOMINVALUE
INCREMENT BY的值大于0时递增序列可以用NOMAXVALUEINCREMENT 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
nocacheCYCLE;#多次执行
oracle select seq1.nextval from dual;
#返回值依次为
101-130-120-110100Oracle 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 ???? MINVALUESTART 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 ???? MINVALUEoracle 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 ???? MAXVALUEoracle 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
