各数据库(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) );
相关推荐
CoderToy 2020-11-16
bianruifeng 2020-11-16
云中舞步 2020-11-12
敏敏张 2020-11-11
暗夜之城 2020-11-11
好记忆也需烂 2020-11-11
Coder技术文摘 2020-09-29
huacuilaifa 2020-10-29
Gexrior 2020-10-22
lpfvip00 2020-10-07
云中舞步 2020-09-11
康慧欣 2020-09-10
emmm00 2020-11-17
世樹 2020-11-11
tufeiax 2020-09-03
疯狂老司机 2020-09-08
王艺强 2020-11-17
ribavnu 2020-11-16