欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

各数据库(MySQL、PostgreSQL、Oracle、MsSQL)有关自增字段的设置

程序员文章站 2022-03-15 19:33:56
...

在数据库设计过程中,我们通常会把数据库表中的ID字段设置成自增。下面以常用的数据字典表为例,说明如何在各数据库下设置自增字段。

MySQL

MySQL数据库只需要在目标字段上添加AUTO_INCREMENT,并且为表设置AUTO_INCREMENT=x
x:自增开始的数字。

参考示例:

CREATE TABLE `dictionary` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `parent_id` int(10) unsigned NOT NULL COMMENT '父ID',
  `type` varchar(50) NOT NULL COMMENT '元数据类型',
  `item_name` varchar(100) NOT NULL COMMENT '元数据项显示名',
  `item_value` varchar(100) DEFAULT NULL COMMENT '元数据项存储值',
  `comment` varchar(200) DEFAULT NULL COMMENT '备注',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标记',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='数据字典';

PostgreSQL

PostgreSQL数据库有多种方法可实现自增字段的设置,常用的有:

  1. SERIAL,最简单
  2. IDENTITY,是PostgreSQL 10的新增特性
  3. 创建SEQUENCE,更灵活

参考示例:SERIAL

create table dictionary (
   id                   SERIAL not null,
   parent_id            INT4                 not null,
   type                 VARCHAR(50)          not null,
   item_name            VARCHAR(100)         not null,
   item_value           VARCHAR(100)         null,
   comment              VARCHAR(200)         null,
   deleted              INT2                 not null default 0,
   create_time          DATE                 not null default CURRENT_TIMESTAMP,
   constraint PK_dictionary primary key (id)
);

Oracle

Oracle数据库常用的设置自增字段的两种方法:

  1. 创建SEQUENCE
  2. IDENTITY,要求Oracle数据库版本是12c或以上

12c版本示例:

create table "dictionary" (
   "id"                 INTEGER  generated as identity ( start with 1 nocycle noorder)  not null,
   "parent_id"          INTEGER               not null,
   "type"               VARCHAR2(50)          not null,
   "item_name"          VARCHAR2(100)         not null,
   "item_value"         VARCHAR2(100),
   "comment"            VARCHAR2(200),
   "deleted"            SMALLINT             default 0  not null,
   "create_time"        TIMESTAMP            default CURRENT_TIMESTAMP  not null,
   constraint "PK_dictionary" primary key ("id")
);

11g版本示例:

-- 建表
create table "dictionary" (
   "id"                 INTEGER               not null,
   "parent_id"          INTEGER               not null,
   "type"               VARCHAR2(50)          not null,
   "item_name"          VARCHAR2(100)         not null,
   "item_value"         VARCHAR2(100),
   "comment"            VARCHAR2(200),
   "deleted"            SMALLINT             default 0  not null,
   "create_time"        TIMESTAMP            default CURRENT_TIMESTAMP  not null,
   constraint "PK_dictionary" primary key ("id")
);
-- 创建序列
CREATE SEQUENCE DICTIONARY_ID_SEQ
INCREMENT BY 1
START WITH 1;
-- 创建触发器,非必须
CREATE OR REPLACE TRIGGER DICTIONARY_ID_SEQ_TRG
BEFORE INSERT ON "dictionary"
FOR EACH ROW
WHEN (NEW."id" IS NULL)
BEGIN
  SELECT DICTIONARY_ID_SEQ.NEXTVAL
  INTO :NEW."id"
  FROM DUAL;
END;

MsSQL

MsSQL即SQL Server数据库,使用IDENTITY即可。

参考示例:

create table dictionary (
   id                   int                  identity,
   parent_id            int                  not null,
   type                 varchar(50)          not null,
   item_name            varchar(100)         not null,
   item_value           varchar(100)         null,
   comment              varchar(200)         null,
   deleted              smallint             not null default 0,
   create_time          datetime             not null default CURRENT_TIMESTAMP,
   constraint PK_dictionary primary key (id)
);

diboot 简单高效的轻代码开发框架 (求star)