SQL 92 并未定义 AutoID 实现标准,导致不同的数据库使用不同的实现,特此整理之,方便查询使用。
---------------------------------------------------------------
SQLite:
CREATE TABLE foo (
id INTEGER PRIMARY KEY
);
id INTEGER PRIMARY KEY
);
MySQL:
CREATE TABLE foo (
id INT auto_increment,
PRIMARY KEY (id)
);
id INT auto_increment,
PRIMARY KEY (id)
);
PostgreSQL:
CREATE TABLE foo (
id SERIAL PRIMARY KEY -- 实际 SERIAL 创建了一个 Sequence
);
id SERIAL PRIMARY KEY -- 实际 SERIAL 创建了一个 Sequence
);
SQL Server:
CREATE TABLE foo (
id INT PRIMARY KEY IDENTITY(1,1) -- 以1为起点, 以1为步长的自增长列
);
id INT PRIMARY KEY IDENTITY(1,1) -- 以1为起点, 以1为步长的自增长列
);
Oracle:
Oracle 在通过 Sequnece 实现 AutoID,当 Drop table 时,触发器同时被 drop,但是 sequence 依然残余,需要手动清除。
CREATE TABLE foo (
id INT,
name VARCHAR2(100),
PRIMARY KEY (id)
);
CREATE SEQUENCE foo_seq;
CREATE OR REPLACE TRIGGER foo_trig_insert BEFORE INSERT ON foo FOR EACH ROW
BEGIN
IF ( :new.id IS null ) THEN
SELECT foo_seq.nextval INTO :new.id FROM dual;
END IF;
END;
id INT,
name VARCHAR2(100),
PRIMARY KEY (id)
);
CREATE SEQUENCE foo_seq;
CREATE OR REPLACE TRIGGER foo_trig_insert BEFORE INSERT ON foo FOR EACH ROW
BEGIN
IF ( :new.id IS null ) THEN
SELECT foo_seq.nextval INTO :new.id FROM dual;
END IF;
END;