目录
背景
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*/
在线游乐场
参考
PostgreSQL
Oracle
https://www.codeproject.com/Tips/5336518/Auto-Increment-Column-Options-in-Different-Databas