在设计数据库的表结构时，经常会使用一个自动增长的数字序列作为主键字段（代理主键）。除了作为主键使用之外，自增字段也可以用于记录各个操作发生的先后顺序，因为它具有递增特性。当我们插入一行数据时，数据库会为自增字段生成一个新的数值。

我们今天的主题就是自增字段的实现，下表列出了主流数据库中创建自增字段的几种方法：

接下来我们针对不同的数据库进行详细讨论。
MySQL
AUTO_INCREMENT

MySQL 通过 AUTO_INCREMENT 属性定义自增字段，并且需要遵循以下规则：

每个表只能有一个自增字段，数据类型一般是整数；
自增字段必须创建主键（PRIMARY KEY）或者唯一索引（UNIQUE）；
自增字段必须非空（NOT NULL），MySQL 会自动为自增字段设置非空约束。

以下语句创建了一个表 users，其中 user_id 是一个自增主键字段：

create table users(
user_id INT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(50) NOT NULL,
email VARCHAR(100)
);

接下来我们插入两条数据：

insert into users(user_name, email) values (‘u1’, ‘u1@test.com’);
insert into users(user_name, email) values (‘u2’, ‘u2@test.com’);

select * from users;

  1|u1       |u1@test.com|
  2|u2       |u2@test.com|

在上面的插入语句中，我们没有指定 user_id 的值，此时 MySQL 会自动为该字段生成一个递增序列值。AUTO_INCREMENT 字段的值默认从 1 开始，每次递增也是 1。

如果插入数据时为自增字段指定了 NULL 值或者 0，MySQL 同样会自动生成一个序列值。

insert into users(user_id, user_name, email) values (null, ‘u3’, ‘u3@test.com’);
insert into users(user_id, user_name, email) values (0, ‘u4’, ‘u4@test.com’);

select * from users;

  1|u1       |u1@test.com|
  2|u2       |u2@test.com|
  3|u3       |u3@test.com|
  4|u4       |u4@test.com|

如果插入数据时为自增字段指定了非空也非 0 的值，MySQL 会使用我们提供的值；而且还会将自增序列的起始值值设置为该值，可能导致自增字段值的跳跃。

insert into users(user_id, user_name, email) values (100, ‘u5’, ‘u5@test.com’);
insert into users(user_name, email) values (‘u6’, ‘u6@test.com’);

select * from users;

  1|u1       |u1@test.com|
  2|u2       |u2@test.com|
  3|u3       |u3@test.com|
  4|u4       |u4@test.com|
100|u5       |u5@test.com|
101|u6       |u6@test.com|

上面的第一个插入语句为 user_id 提供了值 100，第二个插入语句使用系统提供的自增序列值，此时跳跃到了 101。

📝MySQL 提供了 LAST_INSERT_ID 函数，用于获取最后一次生成的序列值。

另外，MySQL 也可以使用ALTER TABLE语句设置自增序列的值：

ALTER TABLE users AUTO_INCREMENT = 200;
insert into users(user_name, email) values (‘u7’, ‘u7@test.com’);

select * from users where user_name = ‘u7’;

200|u7       |u7@test.com|

最后我们来看一个问题，当自增序列到达最大值之后怎么办。下面的语句演示了这种情况：

ALTER TABLE users AUTO_INCREMENT = 2147483647;
insert into users(user_name, email) values (‘u8’, ‘u8@test.com’);
insert into users(user_name, email) values (‘u9’, ‘u9@test.com’);
SQL Error [1062] [23000]: Duplicate entry ‘2147483647’ for key ‘users.PRIMARY’

先将 AUTO_INCREMENT 的值设置为 INT 类型的最大值；然后插入两条数据，第二个插入语句出现主键值重复，意味着自增字段到达最大值之后一直保持不变。

如果担心自增字段的值不够用，可以将 INT 类型改成 INT UNSIGNED，最大值可以到达 4294967295（ 2 32 2^{32} 232 - 1）；还不够的话改成 BIGINT，最大值可以到达 9223372036854775807（ 2 63 2^{63} 263 - 1）。

📝MySQL 中的 SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的同义词。

参考文档：MySQL 官方文档 AUTO_INCREMENT。
Oracle

Oracle 数据库提供了两种创建自增字段的方法：

使用标识列（IDENTITY），需要使用 Oracle 12c 以上版本；
使用序列（SEQUENCE）。

标识列

Oracle 12c 提供创建 SQL 标准定义的标识列功能：

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

其中，

GENERATED ALWAYS表示总是为标识列使用 Oracle 生成的值，如果用户指定该字段的值将会出错；
GENERATED BY DEFAULT表示如果用户没有提供值，使用 Oracle 生成的值；如果提供了值，使用用户提供的值；如果指定了 NULL 值将会出错；
GENERATED BY DEFAULT ON NULL表示如果用户没有提供值或者提供了 NULL 值，使用 Oracle 生成的值；否则使用用户提供的值。

Oracle 中的标识列实际上是一个内部创建序列对象，因此 identity_options 与序列的属性类似，主要包括：

START WITH n指定序列的初始值，默认为 1；
INCREMENT BY n指定序列的增量值，默认为 1；
MAXVALUE n和MINVALUE n指定序列的最大值和最小值，默认值为 9999999999999999999999999999 和 1；
CYCLE和NOCYCLE指定是否循环使用序列中的值，默认不循环使用；
CACHE n和NOCACHE指定是否缓存序列值，缓存可以提高性能。默认缓存 20 个。

以下语句创建了一个表 users，其中 user_id 是一个自增主键字段：

create table users(
user_id NUMBER GENERATED BY DEFAULT AS IDENTITY INCREMENT BY 10 START WITH 100 PRIMARY KEY,
user_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100)
);

其中，INCREMENT BY 10 表示每次增量为 10；START WITH 100 表示序列值从 100 开始。

我们测试一下数据插入：

insert into users(user_name, email) values (‘u1’, ‘u1@test.com’);
insert into users(user_name, email) values (‘u2’, ‘u2@test.com’);

select * from users;

 

更多请见：http://www.mark-to-win.com/tutorial/51668.html