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

在数据库设计过程中，我们通常会把数据库表中的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数据库有多种方法可实现自增字段的设置，常用的有：

SERIAL，最简单

IDENTITY，是PostgreSQL 10的新增特性

创建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数据库常用的设置自增字段的两种方法：

创建SEQUENCE

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)

);