不同数据库中的自动递增列选项

最新推荐文章于 2024-01-04 16:07:22 发布

寒冰屋

最新推荐文章于 2024-01-04 16:07:22 发布

阅读量239

点赞数

分类专栏：数据库文章标签：数据库 sqlserver postgresql

原文链接：https://www.codeproject.com/Tips/5336518/Auto-Increment-Column-Options-in-Different-Databas

版权

数据库专栏收录该内容

227 篇文章 13 订阅

订阅专栏

下载源代码 - 1.7 KB

背景

SQLServer数据库IDENTITY(1, 1)列将在每次将新行插入表中时自动生成并填充一个数字列值。在PostgreSQL，Oracle和MySql中，情况有点不同，并且有一些替代方法。在这里，我们将快速浏览一些选项。

PostgreSQL

SERIAL

这可从v8获得：

DROP TABLE IF EXISTS tbl_users;

CREATE TABLE tbl_users (
    id SERIAL,
    name VARCHAR NULL,
    
    CONSTRAINT pk_tbl_users PRIMARY KEY(id) 
);

INSERT INTO tbl_users(name) VALUES ('user1');
INSERT INTO tbl_users(id, name) VALUES (DEFAULT, 'user2');

SELECT * FROM tbl_users;

我们也可以使用SMALLSERIAL, BIGSERIAL。

名字	存储大小	范围
SMALLSERIAL	2字节	1到32,767
SERIAL	4字节	1到2,147,483,647
BIGSERIAL	8字节	1至9,223,372,036,854,775,807

SEQUENCE

这可以从v9.1开始获得，类似于Oracle。

DROP TABLE IF EXISTS tbl_users;
DROP SEQUENCE IF EXISTS seq_tbl_users_id;

CREATE SEQUENCE seq_tbl_users_id;
CREATE TABLE tbl_users (
    id INT NOT NULL DEFAULT nextval('seq_tbl_users_id'),
    name VARCHAR NULL,
    
    CONSTRAINT pk_tbl_users PRIMARY KEY(id) 
);

INSERT INTO tbl_users(name) VALUES ('user1');
INSERT INTO tbl_users(id, name) VALUES (nextval('seq_tbl_users_id'), 'user2');

SELECT * FROM tbl_users;

IDENTITY

这可从v10开始获得。

DROP TABLE IF EXISTS tbl_users;

CREATE TABLE tbl_users (
    id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name VARCHAR NULL,
    
    CONSTRAINT pk_tbl_users PRIMARY KEY(id) 
);

INSERT INTO tbl_users(name) VALUES ('user1');
INSERT INTO tbl_users(id, name) VALUES (DEFAULT, 'user2');

SELECT * FROM tbl_users;

Oracle

SEQUENCE

CREATE SEQUENCE SEQ_USER_ID
MINVALUE 1
MAXVALUE 2147483647
START WITH 1
INCREMENT BY 1
CACHE 10;

CREATE TABLE TBL_USER (
    ID INT NOT NULL,
    NAME VARCHAR(255) NULL,
  
    CONSTRAINT PK_TBL_USER PRIMARY KEY (ID)
);

INSERT INTO TBL_USER(ID, NAME) VALUES (SEQ_USER_ID.NEXTVAL, 'user2');

SELECT * FROM TBL_USER;
SELECT SEQ_USER_ID.NEXTVAL AS ID FROM DUAL;

DROP TABLE TBL_USER;
DROP SEQUENCE SEQ_USER_ID;

IDENTITY

这可从12c开始。

CREATE TABLE TBL_USER (
    ID INT GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 1 INCREMENT BY 1,
    NAME VARCHAR(255) NULL,
  
    CONSTRAINT PK_TBL_USER PRIMARY KEY (ID)
);

INSERT INTO TBL_USER(NAME) VALUES ('user1');
INSERT INTO TBL_USER(NAME) VALUES ('user2');

SELECT * FROM TBL_USER;

DROP TABLE TBL_USER;

SQLServer

IDENTITY

DROP TABLE IF EXISTS TblUser;

CREATE TABLE TblUser (
    Id INT IDENTITY(1, 1),
    [Name] VARCHAR(MAX) NULL,
    
    CONSTRAINT PK_TblUser PRIMARY KEY(Id)
);

INSERT INTO TblUser([Name]) VALUES ('user1');

SELECT * FROM TblUser;

MySQL

AUTO_INCREMENT

这可从v5.6开始获得。

DROP TABLE IF EXISTS tbl_user;

CREATE TABLE tbl_user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NULL,
  
    CONSTRAINT PK_tbl_user PRIMARY KEY (id)
);

INSERT INTO tbl_user(NAME) VALUES ('user1');

SELECT * FROM tbl_user;

SQLite

AUTOINCREMENT

这可从v3.26开始获得。

DROP TABLE IF EXISTS tbl_user;

CREATE TABLE tbl_user
(
    Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name VARCHAR NULL
);

INSERT INTO tbl_user(name) VALUES ('user1');

SELECT * FROM tbl_user;

获取插入的ID

PostgreSQL

INSERT INTO tbl_users(name) VALUES ('user1') RETURNING id;            --postgresql

Oracle

DECLARE                                                               --oracle
    INSERTED_ID INT;
BEGIN
    INSERT INTO TBL_USER(NAME) VALUES ('user1') RETURNING ID INTO INSERTED_ID;
    DBMS_OUTPUT.PUT_LINE(INSERTED_ID);
END;
/

SQLServer

INSERT INTO TblUser([Name]) OUTPUT INSERTED.Id VALUES ('user1');      --sqlserver

MySQL

INSERT INTO tbl_user(NAME) VALUES ('user1');
SELECT LAST_INSERT_ID() AS id;                                        --mysql

SQLite

INSERT INTO tbl_user(name) VALUES ('user1');
SELECT last_insert_rowid() Id;                                        --sqlite

检查数据库版本

以下是一些用于检查工作数据库版本的SQL查询：

SELECT @@VERSION;            /*sql server*/       /*also works with mysql*/
SELECT * FROM V$VERSION;     /*oracle*/
SELECT VERSION();            /*mysql*/            /*also works with postgre*/
SELECT VERSION();            /*postgre sql*/      /*also works with mysql*/