SQL 方言参考/数据结构定义/自动递增列

一个小提示：在大多数情况下，自动递增列用作主键列。在 SQL 标准中，这两个概念的结合不是强制性的。

SQL 标准

SQL 标准定义了两种生成自动递增值的方法。首先，作为对精确数值类型的扩展，存在标识列。语法为：“GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY”。其次，将序列与触发器结合使用是标准化的。

CREATE TABLE t1 (col1 DECIMAL GENERATED ALWAYS AS IDENTITY);

DB2

标识列或序列与触发器结合使用 (两种技术的比较)。

CREATE TABLE t1 (col1 INT GENERATED ALWAYS AS IDENTITY);

--  or:

CREATE TABLE t1 (col1 INT);
CREATE SEQUENCE sequence_name;
CREATE TRIGGER insert_trigger
       NO CASCADE BEFORE INSERT ON t1
       REFERENCING NEW AS n
       FOR EACH ROW
  SET n.col1 = NEXTVAL FOR sequence_name;

Firebird

建议使用 序列 与触发器结合使用。从 3.0 版本开始，提供 标识 支持。

SET TERM  ^;
CREATE TABLE t1(col1 INTEGER NOT NULL PRIMARY KEY)^
CREATE SEQUENCE sequence_name^
ALTER  SEQUENCE sequence_name RESTART WITH 0^

CREATE TRIGGER trigger_name FOR t1
BEFORE INSERT
AS
BEGIN
  NEW.col1 = NEXT VALUE FOR sequence_name;
END^

Linter

AUTOINC 列（可能带有范围）或序列与触发器结合使用。

CREATE TABLE t1 (col1 SMALLINT AUTOINC);
CREATE TABLE t2 (col1 INTEGER AUTOINC);
CREATE TABLE t3 (col1 BIGINT AUTOINC);

MonetDB

CREATE SEQUENCE sequence_name AS INT START WITH 1 INCREMENT BY 1;
CREATE TABLE t1 (
  col1 BIGINT PRIMARY KEY DEFAULT NEXT VALUE FOR sequence_name,
  col2 BIGINT AUTO_INCREMENT, 
  col3 BIGINT GENERATED ALWAYS AS IDENTITY (
           START WITH 100 INCREMENT BY 2
           NO MINVALUE MAXVALUE 1000
           CACHE 2 CYCLE)
);
CREATE TABLE ts (
  col1 SERIAL,  /* implies: INTEGER NOT NULL PRIMARY KEY DEFAULT NEXT VALUE FOR "sch"."seq_12345" */
  ...
);
CREATE TABLE tbs (
  col1 BIGSERIAL,  /* implies: BIGINT NOT NULL PRIMARY KEY DEFAULT NEXT VALUE FOR "sch"."seq_23456" */
  ...
);

MSSQL

CREATE TABLE t1 (col1 INT IDENTITY(1,1));

MySQL

CREATE TABLE t1 (col1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT);

OpenLink Virtuoso

IDENTITY (start with 1, increment by 1);
CREATE TABLE t1 (col1 INTEGER IDENTITY);

-- or:

CREATE TABLE t1 (col1 INTEGER IDENTITY (start with 1));

Oracle

CREATE TABLE t1 (col1 NUMBER PRIMARY KEY);
CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trigger_name BEFORE INSERT ON t1 FOR EACH ROW
DECLARE
  max_id NUMBER;
  cur_seq NUMBER;
BEGIN
IF :NEW.col1 IS NULL THEN
  -- normal assignment of the next value in the sequence
  :NEW.col1 := sequence_name.NEXTVAL;
ELSE
  -- or allow the user to specify the value, so must advance the sequence to match
  SELECT GREATEST(COALESCE(MAX(col1), 0), :NEW.col1) INTO max_id FROM t1;
  WHILE cur_seq < max_id LOOP
    SELECT sequence_name.NEXTVAL INTO cur_seq FROM DUAL;
  END LOOP;
END IF;
END;

-- since Oracle 12.1:
CREATE TABLE t1 (col1 NUMBER GENERATED BY DEFAULT AS IDENTITY);

PostgreSQL

create table t1 (col1 serial primary key);

-- since postgres 10:
create table t1 (col1 integer generated by default as identity primary key);

SQLite

两者都创建一个 自动递增列；AUTOINCREMENT 关键字仅阻止重新使用已删除的值。

CREATE TABLE t1 (col1 INTEGER PRIMARY KEY);
CREATE TABLE t1 (col1 INTEGER PRIMARY KEY AUTOINCREMENT);