xugu自增与mysql和oracle的差异

hunter_chuan

已于 2024-04-23 09:12:49 修改

阅读量653

点赞数 13

分类专栏：虚谷数据库文章标签： mysql oracle 数据库

于 2024-04-16 19:15:43 首次发布

本文链接：https://blog.csdn.net/m0_50013822/article/details/137827589

版权

虚谷数据库专栏收录该内容

9 篇文章 3 订阅

订阅专栏

在设计数据库表结构的时候通常会采用自动增长的一个数据作为主键，用于唯一标识。

下面是xugu、mysql和oracle自增长的实现，以及区别。

db	XUGU	MySQL	Oracle
标识列（IDENTITY）	支持	--	支持
序列（SEQUENCE）	支持	--	支持
AUTO_INCREMENT	--	支持	--
自增长多列	支持	--	--
重置自增长列	--	支持	--

一、mysql 自增长

AUTO_INCREMENT

MySQL 通过 AUTO_INCREMENT 属性定义自增字段，
规则：
1、每个表只能有一个自增字段，数据类型一般是整数；
2、自增字段必须创建主键（PRIMARY KEY）或者唯一索引（UNIQUE）；
3、自增字段必须非空（NOT NULL），MySQL 会自动为自增字段设置非空约束。

特点：
1、AUTO_INCREMENT属性可以用来为新行生成一个唯一的标识
2、在mysql中允自增字段”null“和”0“值自增。
3、重置AUTO_INCREMENT列后，When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence isreset so that the next automatically generated value follows sequentially from the largest column value.
当您在AUTO_INCREMENT列中插入任何其他值时，该列将被设置为该值并重置序列，以便下一个自动生成的值按照最大列值的顺序排列。

参考文档：MySQL :: MySQL 8.0 Reference Manual :: 5.6.9 Using AUTO_INCREMENT

测试案例：

--1、生成一个唯一的标识
SQL>CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);
SQL>INSERT INTO animals (name) VALUES('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');
SQL>SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

--2、”null“和”0“值自增
SQL>INSERT INTO animals (id,name) VALUES(0,'groundhog');
SQL>INSERT INTO animals (id,name) VALUES(NULL,'squirrel');


--3、重置序列，自动生成的值按照最大列值的顺序排列
SQL>INSERT INTO animals (id,name) VALUES(100,'rabbit');
SQL>INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SQL>SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

二、oracle 自增长

oracle两种创建自增长字段方式：
序列（SEQUENCE）
标识列（IDENTITY），需要12c以上版本

序列
特点：
1、使用该语句创建一个序列，该序列是一个数据库对象，多个用户可以从中生成唯一的整数。
2、生成序列号时，该序列将递增，与事务提交或回滚无关。
3、序列号是独立于表生成的，因此同一序列可用于一个或多个表。
4、创建序列之后，您可以使用伪列(返回序列的当前值)或伪列(增加序列并返回新值)在SQL语句中访问它的值。
测试案例：

SQL>CREATE SEQUENCE customers_seq
 START WITH     10
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

SQL>SELECT customers_seq.nextval from dual;

SQL>create table seq_tb(
  id NUMBER DEFAULT customers_seq.nextval PRIMARY KEY,
  name VARCHAR2(20) NOT NULL,
);

参考文档：CREATE SEQUENCE (oracle.com)

标识列
特点：
1、每个表只能指定一个标识列。
2、标识列必须是数字数据类型，不能是自定义数据类型。
3、标识列不能指定DEFAULT约束。
4、当指定identity_clause时，将隐式指定NOT NULL约束和NOT deferable约束状态。
5、CREATE TABLE AS SELECT将不会继承列的identity属性。
测试案例：

--1、GENERATED [ALWAYS] AS IDENTITY
SQL>create table t1(
   id  integer  generated  ALWAYS as identity,
   name varchar2(20) not null
);
#id字段只能自动生成,update和insert禁止。

--2、GENERATED BY DEFAULT AS IDENTITY
SQL>create table t1(
   id  integer  generated  BY DEFAULT as identity,
   name varchar2(20) not null
)
#id不能为空，没有为一值约束，变id后，自增字段不会再重新定位起始值。

--3、GENERATED BY DEFAULT ON NULL AS IDENTITY
SQL>create table t1(
   id  integer  generated  BY DEFAULT on null as identity,
   name varchar2(20) not null
)
#id可为空，没有唯一值约束，自增字段不会再重新定位起始值。

参考文档：CREATE TABLE (oracle.com)

三、xugu 自增长

xugu两种创建自增长字段方式：
序列（SEQUENCE）
标识列（IDENTITY）

序列
特点：
1、虚谷数据库系统支持序列值管理功能，序列值主要用于产生整数序列值，其产生整数值按照用户指定规则进行递增或递减。
测试案例：

-- 无可选参数的创建语句
create sequence seq_1;

-- 有可选约束表达式的创建语句
create sequence seq_2 minvalue 1 maxvalue 1000 start with 100 increment by 4;

-- 有可选注释信息的创建语句
create sequence seq_3 comment 'just a test sequence';

-- 有可选约束表达式和可选注释信息的创建语句
create sequence seq_4
  minvalue 10 maxvalue 10000 start with 20
  increment by 10 cache 5
  comment 'another test sequence';

参考文档：序列值创建 - 虚谷文档中心 (xugudb.com)

标识列
1、xugu标识列单表对列数没有限制，数据类型为整型。
2、创建序列，默认创建唯一约束和索引。
3、默认不支持”null“和”0“自增，设置自增列模式def_identity_mode支持。
4、标识列自动维护一个序列，自增长标识列非自动重置。

测试案例：

SQL>Create Table my_auto(
            id Int identity(1,1) Primary Key,
            name Varchar(20)
     );
#xugu数据库由于序列值不会重置，所以insert和update增大了序列值，所以下一次自增到该值，会出现违法唯一约束问题。

参考文档：自动增长 - 虚谷文档中心 (xugudb.com)

四、对比区别

1、xugu和mysql都需要创建主键或唯一索引，oracle未创建。
2、xugu与mysql中的自增长差异，体现在序列上，mysql在insert更大序列值后会重置标识列，xugu数据库不会，从而在mysql端的代码在xugu可能出现违反唯一值约束；mysql单表只允许一个自增字段，虚谷允许多个标识列。
3、xugu与oracel中自增长差异，两者在单机上SEQUENCE基本一致。在标识列上oracle采用的三种语法格式进行控制，xugu采用参数控制，两者都未能同mysql一样重置标识列；oracle也只允许指定一个标识列，虚谷允许多个标识列。