Oracle实现主键字段自增

比眉伴天荒 2023-09-26 09:09 70阅读 0赞

**Oracle实现主键自增有4种方式：**

1.  **Identity Columns新特性自增（Oracle版本≥12c）**
2.  **创建自增序列，创建表时，给主键字段默认使用自增序列**
3.  **创建自增序列，使用触发器使主键自增**
4.  **创建自增序列，插入语句（insert）时，使用自增序列代替值**

**数据库表名称为：userinfo**（不能使用user，user为Oracle关键字）

## 方式一：**Identity Columns新特性自增** ##

该种方式要求Oracle版本≥12c

在自增字段后使用以下2种语句的1种：

1.  **generated by default as IDENTITY**
2.  **generated always as identity**

**建表语句：**

CREATE TABLE userinfo (
    	id number(11) generated by default as IDENTITY,--使用自增功能
    	name varchar2(20) ,
      	age number(3)
    )

**测试：**

INSERT INTO USERINFO (name,age) VALUES('张三',18)

**结果：**

![4e08e9c8c0904cb2ad03387d7759441b.png][]

**该种方式很简单，底层原理其实和下面3种方式相似，只是Oracle帮我们省去了多余的操作。**

以下是剩余的3种方式，都用共同的特点（先创建自增序列）：

## 准备工作： ##

**必须先创建自增序列：**

--设置自增序列，名称为"seq_userinfo"，名字任意命名
    create sequence seq_userinfo
     increment by 1		--每次+1	
     start with 1		--从1开始
     nomaxvalue			--不限最大值
     nominvalue			--不限最小值
     cache 20;			--设置取值缓存数为20

sequence各个参数详解，可以参考这边文章：

[Oracle中sequence（序列）详解\_睡竹的博客-CSDN博客Oracle中sequence（序列）详解cache的作用及优缺点![favicon32.ico][]https://blog.csdn.net/weixin\_42675423/article/details/127101723][Oracle_sequence_-CSDN_Oracle_sequence_cache_favicon32.ico_https_blog.csdn.net_weixin_42675423_article_details_127101723]

## 方式二：创建自增序列，创建表时，给主键字段默认使用自增序列 ##

**建表语句，设置自增字段默认使用自增序列（推荐该方式）：**

--创建userinfo表
    CREATE TABLE userinfo (
      id number(11)  DEFAULT seq_userinfo.nextval, --"seq_userinfo"为自增序列名称
      name varchar2(20) ,
      age number(3)
    );

测试：我使用的是DBeaver软件：

如图：

![980321c79c7c4518925c17d709dae65b.png][]

**注意：**新增了一条数据，点击保存，该软件并不会立刻自动显示生成的ID，我们**需要关闭当前页签，再打开就可以看到自增的ID**了

如下：

![ae05d79dc0e94150abf5db3a96e5ed51.png][]

## 方式三：创建自增序列，使用触发器使主键自增 ##

**建表语句：**

--创建userinfo表
    CREATE TABLE userinfo (
      id number(11)      not null,
      name varchar2(20) ,
      age number(3)
    );

**创建触发器（只需要注意注释的位置）：**

--创建触发器，名称为"deptinfo_TRIGGER"，名字任意命名
    create or replace trigger userinfo_TRIGGER
    before insert on userinfo	--"userinfo"为表名称
    for each row
    begin
    select seq_userinfo.nextval into :new.id from dual;	--1、"seq_userinfo"为自增序列名称 2、这里的id是你需要自增的序列
    end userinfo_TRIGGER;	--"userinfo_TRIGGER"为触发器名称

测试：跟方式1测试一样，会存在不显示ID问题：

如图：

![6c152ae957024258b2ac13b62c4d27e4.png][]

重新打开页签：

如下：

![4462f0e5252c485ea2a72b128a3b2a36.png][]

## 方式四：创建自增序列，插入语句（insert）时，使用自增序列代替值 ##

**建表语句：**

--创建userinfo表
    CREATE TABLE userinfo (
      id number(11)      not null,
      name varchar2(20) ,
      age number(3)
    );

**在插入语句中，自增的字段（ID），使用自增序列去代替**

如下：

> **INSERT INTO userinfo(id,name,age) VALUES(seq\_userinfo.nextval,'睡竹',18);**

**注意：方式三与MySQL、微软的SQL server的自增方式是一样的！**

运行结果：

![bc700a8df2134eb79fbf0e51edb993e3.png][]

## 总结： ##

对后台系统开发来说：

方式1和方式2最方便

方式3还需要新加一个触发器，不推荐

方式4类似于方式2，但是对Java程序而言，mybatis的xml需要显式的指定触发器，不够友好

**附上删除触发器和自增序列的语句：**

--删除自增序列,"seq_userinfo"为自增序列的名称
    drop sequence seq_userinfo
    --删除触发器，"userinfo_TRIGGER"为触发器名称
    drop trigger userinfo_TRIGGER

[4e08e9c8c0904cb2ad03387d7759441b.png]: https://img-blog.csdnimg.cn/4e08e9c8c0904cb2ad03387d7759441b.png
[favicon32.ico]: https://g.csdnimg.cn/static/logo/favicon32.ico
[Oracle_sequence_-CSDN_Oracle_sequence_cache_favicon32.ico_https_blog.csdn.net_weixin_42675423_article_details_127101723]: https://blog.csdn.net/weixin_42675423/article/details/127101723
[980321c79c7c4518925c17d709dae65b.png]: https://img-blog.csdnimg.cn/980321c79c7c4518925c17d709dae65b.png
[ae05d79dc0e94150abf5db3a96e5ed51.png]: https://img-blog.csdnimg.cn/ae05d79dc0e94150abf5db3a96e5ed51.png
[6c152ae957024258b2ac13b62c4d27e4.png]: https://img-blog.csdnimg.cn/6c152ae957024258b2ac13b62c4d27e4.png
[4462f0e5252c485ea2a72b128a3b2a36.png]: https://img-blog.csdnimg.cn/4462f0e5252c485ea2a72b128a3b2a36.png
[bc700a8df2134eb79fbf0e51edb993e3.png]: https://img-blog.csdnimg.cn/bc700a8df2134eb79fbf0e51edb993e3.png