【總結(jié)】數(shù)據(jù)庫自增字段的 3 種實現(xiàn)方式
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué),十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗,目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
在設(shè)計數(shù)據(jù)庫的表結(jié)構(gòu)時,經(jīng)常會使用一個自動增長的數(shù)字序列作為主鍵字段(代理主鍵)。除了作為主鍵使用之外,自增字段也可以用于記錄各個操作發(fā)生的先后順序,因為它具有遞增特性。當(dāng)我們插入一行數(shù)據(jù)時,數(shù)據(jù)庫會為自增字段生成一個新的數(shù)值。
我們今天的主題就是自增字段的實現(xiàn),下表列出了主流數(shù)據(jù)庫中創(chuàng)建自增字段的幾種方法:
接下來我們針對不同的數(shù)據(jù)庫進行詳細討論。
MySQL
AUTO_INCREMENT
MySQL 通過 AUTO_INCREMENT 屬性定義自增字段,并且需要遵循以下規(guī)則:
每個表只能有一個自增字段,數(shù)據(jù)類型一般是整數(shù);
自增字段必須創(chuàng)建主鍵(PRIMARY KEY)或者唯一索引(UNIQUE);
自增字段必須非空(NOT NULL),MySQL 會自動為自增字段設(shè)置非空約束。
以下語句創(chuàng)建了一個表 users,其中 user_id 是一個自增主鍵字段:
create table users(
user_id INT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(50) NOT NULL,
email VARCHAR(100)
);
接下來我們插入兩條數(shù)據(jù):
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;
user_id | user_name |
---|
1|u1 |u1@test.com|
2|u2 |u2@test.com|
在上面的插入語句中,我們沒有指定 user_id 的值,此時 MySQL 會自動為該字段生成一個遞增序列值。AUTO_INCREMENT 字段的值默認從 1 開始,每次遞增也是 1。
如果插入數(shù)據(jù)時為自增字段指定了 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;
user_id | user_name |
---|
1|u1 |u1@test.com|
2|u2 |u2@test.com|
3|u3 |u3@test.com|
4|u4 |u4@test.com|
如果插入數(shù)據(jù)時為自增字段指定了非空也非 0 的值,MySQL 會使用我們提供的值;而且還會將自增序列的起始值值設(shè)置為該值,可能導(dǎo)致自增字段值的跳躍。
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;
user_id | user_name |
---|
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,第二個插入語句使用系統(tǒng)提供的自增序列值,此時跳躍到了 101。
??MySQL 提供了 LAST_INSERT_ID 函數(shù),用于獲取最后一次生成的序列值。
另外,MySQL 也可以使用ALTER TABLE語句設(shè)置自增序列的值:
ALTER TABLE users AUTO_INCREMENT = 200;
insert into users(user_name, email) values (‘u7’, ‘u7@test.com’);
select * from users where user_name = ‘u7’;
user_id | user_name |
---|
200|u7 |u7@test.com|
最后我們來看一個問題,當(dāng)自增序列到達最大值之后怎么辦。下面的語句演示了這種情況:
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 的值設(shè)置為 INT 類型的最大值;然后插入兩條數(shù)據(jù),第二個插入語句出現(xiàn)主鍵值重復(fù),意味著自增字段到達最大值之后一直保持不變。
如果擔(dān)心自增字段的值不夠用,可以將 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 數(shù)據(jù)庫提供了兩種創(chuàng)建自增字段的方法:
使用標識列(IDENTITY),需要使用 Oracle 12c 以上版本;
使用序列(SEQUENCE)。
標識列
Oracle 12c 提供創(chuàng)建 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 中的標識列實際上是一個內(nèi)部創(chuàng)建序列對象,因此 identity_options 與序列的屬性類似,主要包括:
START WITH n指定序列的初始值,默認為 1;
INCREMENT BY n指定序列的增量值,默認為 1;
MAXVALUE n和MINVALUE n指定序列的最大值和最小值,默認值為 9999999999999999999999999999 和 1;
CYCLE和NOCYCLE指定是否循環(huán)使用序列中的值,默認不循環(huán)使用;
CACHE n和NOCACHE指定是否緩存序列值,緩存可以提高性能。默認緩存 20 個。
以下語句創(chuàng)建了一個表 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 開始。
我們測試一下數(shù)據(jù)插入:
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;
USER_ID | USER_NAME |
---|
100|u1 |u1@test.com|
110|u2 |u2@test.com|
我們沒有使用GENERATED BY DEFAULT ON NULL選項,如果插入 NULL 值將會出錯:
insert into users(user_id, user_name, email) values (null, ‘u3’, ‘u3@test.com’);
SQL Error [1400] [23000]: ORA-01400: cannot insert NULL into (“TONY”.“USERS”.“USER_ID”)
不過,我們可以為 user_id 指定非空的值:
insert into users(user_id, user_name, email) values (0, ‘u4’, ‘u4@test.com’);
select * from users;
USER_ID | USER_NAME |
---|
100|u1 |u1@test.com|
110|u2 |u2@test.com|
0|u4 |u4@test.com|
Oracle 標識列需要遵循以下限制:
每個表只能有一個標識列;
標識列的數(shù)據(jù)類型必須是數(shù)字類型,不能使用自定義類型;
CREATE TABLE AS SELECT語句不會繼承標識列;
標識列不能指定 DEFAULT 約束。
參考文檔:Oracle 官方文檔 CREATE TABLE 語句。
序列
序列(Sequence)是數(shù)據(jù)庫中的一種對象,用于生成一系列遞增或遞減的數(shù)字。序列使用CREATE SEQUENCE語句創(chuàng)建:
CREATE SEQUENCE seq_users;
以上語句使用默認選項創(chuàng)建了一個序列 seq_users,等價于下面的語句:
CREATE SEQUENCE seq_users
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOMINVALUE
CACHE 20
NOCYCLE;
Oracle 序列的數(shù)據(jù)類型為 NUMBER,包含一個最小值,一個最大值,一個起始值,一個增量值,緩存選項以及一個循環(huán)使用選項。這些參數(shù)的作用可以參考上面的標識列。
創(chuàng)建之后,我們可以使用 NEXTVAL 和 CURRVAL 偽列獲取序列的值:
SELECT seq_users.nextval FROM dual;
NEXTVAL |
---|
1|
SELECT seq_users.currval FROM dual;
CURRVAL |
---|
1|
NEXTVAL 用于從序列中獲取下一個值,CURRVAL 返回了當(dāng)前會話最后一次獲取的序列值。
利用序列,我們可以為表中的字段生成不重復(fù)的數(shù)值:
create table users(
user_id NUMBER PRIMARY KEY,
user_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100)
);
insert into users(user_id, user_name, email) values (seq_users.nextval, ‘u1’, ‘u1@test.com’);
insert into users(user_id, user_name, email) values (seq_users.nextval, ‘u2’, ‘u2@test.com’);
select * from users;
USER_ID | USER_NAME |
---|
2|u1 |u1@test.com|
3|u2 |u2@test.com|
在上面的示例中,我們手動為 user_id 字段指定了 seq_users.nextval 值。如果想要實現(xiàn)自增字段的效果,可以利用觸發(fā)器實現(xiàn):
CREATE OR REPLACE TRIGGER tri_user_insert
BEFORE INSERT ON users
FOR EACH ROW
DECLARE
BEGIN
IF (:NEW.user_id IS NULL) THEN
SELECT seq_users.nextval INTO :NEW.user_id FROM dual;
END IF;
END;
該觸發(fā)器在插入數(shù)據(jù)之前判斷 user_id 是否為空,如果為空就生成一個新的序列號。我們再插入一些數(shù)據(jù):
insert into users(user_id, user_name, email) values (null, ‘u3’, ‘u3@test.com’);
insert into users(user_name, email) values (‘u4’, ‘u4@test.com’);
select * from users;
USER_ID | USER_NAME |
---|
2|u1 |u1@test.com|
3|u2 |u2@test.com|
4|u3 |u3@test.com|
5|u4 |u4@test.com|
上面兩個插入語句都沒有為 user_id 提供數(shù)據(jù),而是由觸發(fā)器自動生成一個數(shù)字編號。
另一個更簡單的方式就是將字段的默認值設(shè)置為序列的值:
create table users(
user_id NUMBER DEFAULT seq_users.nextval PRIMARY KEY,
user_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100)
);
實際上,Oracle 中的標識列就是采用這種方法實現(xiàn)的,只不過增加了一些額外的限制而已。
Oracle 提供了ALTER SEQUENCE語句,可以修改序列的屬性:
ALTER SEQUENCE seq_users
INCREMENT BY 2
MAXVALUE 10000
CYCLE;
以上語句將序列 seq_users 的增量修改為 2,最大值修改為 10000,并且再到達最大值之后再次從最小值開始循環(huán)。不過,Oracle 序列不能修改起始值(START WITH),只能使用DROP SEQUENCE seq_name;語句刪除序列再重建創(chuàng)建。
參考文檔:Oracle 官方文檔 CREATE SEQUENCE 語句。
SQL Server
Microsoft SQL Server 提供了兩種創(chuàng)建自增字段的方法:
使用標識列(IDENTITY);
使用序列(SEQUENCE)。
標識列
SQL Server 支持為字段指定 IDENTITY(start, increment) 屬性的方法定義一個標識列,start 表示序列的起始值,increment 表示每次的增量值。例如:
create table users(
user_id int identity primary key,
user_name varchar(50) not null,
email varchar(100)
);
其中,user_id 是一個 INTEGER 類型的標識列;系統(tǒng)生成的序列值默認從 1 開始,每次遞增也是 1。SQL Server 中每個表只能定義一個標識列。
我們插入一些測試數(shù)據(jù):
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;
user_id | user_name |
---|
1|u1 |u1@test.com|
2|u2 |u2@test.com|
以上語句通過標識列自動生成了兩個用戶編號。我們可以利用獲取最后一次插入的標識列的值:
select @@identity;
需要注意的是,不能為標識列指定 NULL 值;默認也不能為標識列手動指定值。
insert into users(user_id, user_name, email) values (null, ‘u3’, ‘u3@test.com’);
SQL Error [339] [S0001]: DEFAULT or NULL are not allowed as explicit identity values.
insert into users(user_id, user_name, email) values (0, ‘u4’, ‘u4@test.com’);
SQL Error [544] [S0001]: Cannot insert explicit value for identity column in table ‘users’ when IDENTITY_INSERT is set to OFF.
第一個語句為 user_id 指定了 NULL 值;第二個語句的錯誤在于為 user_id 指定了明確的值,不過可以通過設(shè)置表的 IDENTITY_INSERT 屬性修改默認行為。
參考文檔:SQL Server 官方文檔 CREATE TABLE 語句。
序列
SQL Server 提供了和 Oracle 類似的序列對象,用于生成一個遞增或遞減的數(shù)字序列。創(chuàng)建序列的完整語法如下:
CREATE SEQUENCE sequence_name
[ AS integer_type ]
[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ];
其中,
sequence_name 是序列名;
AS 定義了序列的類型。默認為 BIGINT,也可以是 TINYINT、SMALLINT、INT 或者不帶小數(shù)位的 DECIMAL 和 NUMERIC;
START WITH 定義了序列的起始值,默認為 integer_type 類型的最小值;
INCREMENT BY 指定了序列的增量值,可以是負數(shù),默認為 1;
MINVALUE 和 MAXVALUE 分別定義序列的最小值和最大值,默認為 integer_type 類型的最小值和最大值;
CYCLE 表示循環(huán)使用序列的值,默認為 NO CYCLE;
CACHE 表示緩存的序列值個數(shù),可以提高訪問性能。默認不緩存。
以下語句使用默認值創(chuàng)建一個序列 seq_users:
create sequence seq_users;
使用 NEXT VALUE FOR 函數(shù)獲取一個序列的值:
select next value for seq_users;
|
--------------------|
-9223372036854775808|
返回的是 INTEGR 類型的最小值。
我們可以將字段的默認值設(shè)置為序列的 NEXT VALUE FOR 函數(shù)值,實現(xiàn)自增效果:
create table users(
user_id bigint default next value for seq_users 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;
user_id | user_name | |
---|---|---|
-9223372036854775806 | u1 | u1@test.com |
-9223372036854775805 | u2 | u2@test.com |
ALTER SEQUENCE語句可以修改序列的屬性,參數(shù)與CREATE SEQUENCE類似,除了 integer_type 之外的參數(shù)都可以修改。例如:
alter sequence seq_users restart with 1;
insert into users(user_name, email) values (‘u3’, ‘u3@test.com’);
select * from users;
user_id | user_name | |
---|---|---|
-9223372036854775807 | u1 | u1@test.com |
-9223372036854775806 | u2 | u2@test.com |
1|u3 |u3@test.com|
參考文檔:SQL Server 官方文檔-序列。
PostgreSQL
PostgreSQL 提供了多種方法實現(xiàn)自增字段,包括:
標識列(IDENTITY),PostgreSQL 10 以及更高版本;
序列(SEQUENCE);
SERIAL。
標識列
PostgreSQL 實現(xiàn)了 SQL 標準中的標識列,語法與 Oracle 幾乎相同:
column_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]
其中,
data_type 可以是 SMALLINT、INT或者 BIGINT 等整數(shù)類型;
GENERATED ALWAYS表示總是使用 PostgreSQL 生成的值,如果用戶指定該字段的值將會出錯,除非指定了 OVERRIDING SYSTEM VALUE 選項;
GENERATED BY DEFAULT表示如果用戶沒有提供值,使用 PostgreSQL 生成的值;如果提供了值,使用用戶的值;
sequence_option 用于指定序列對象的選項。PostgreSQL 標識列實際上使用一個內(nèi)部的序列對象來實現(xiàn),具體選項參考下文中的序列。
以下語句創(chuàng)建了一個表 users,其中 user_id 是一個標識列:
create table users(
user_id int generated always as identity primary key,
user_name varchar(50) not null,
email varchar(100)
);
此時,PostgreSQL 自動創(chuàng)建了一個序列對象 users_user_id_seq。我們測試一下數(shù)據(jù)插入:
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;
user_id | user_name |
---|
1|u1 |u1@test.com|
2|u2 |u2@test.com|
標識列默認從 1 開始,每次遞增也是 1。
如果我們?yōu)?user_id 指定明確的值:
insert into users(user_id, user_name, email) values (3, ‘u3’, ‘u3@test.com’);
SQL Error [428C9]: ERROR: cannot insert into column “user_id”
Detail: Column “user_id” is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.
該語句執(zhí)行錯誤,不過我們可以使用INSERT語句的 OVERRIDING SYSTEM VALUE 選項覆蓋系統(tǒng)提供的值。
參考文檔:PostgreSQL 官方文檔 CREATE TABLE 語句。
序列
與 Oracle 和 SQL Server 類似,PostgreSQL 也實現(xiàn)了 SQL 標準中的序列對象。創(chuàng)建序列的語法如下:
CREATE SEQUENCE [ IF NOT EXISTS ] name
[ AS data_type ]
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ]
[ CACHE cache ]
[ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]
其中,
name 是序列名;
AS 定義了序列的類型。默認為 BIGINT,也可以是 SMALLINT或者 INTEGER;
INCREMENT BY 指定了序列的增量值,可以是負數(shù),默認為 1;
MINVALUE 和 MAXVALUE 分別定義序列的最小值和最大值,默認為 integer_type 類型的最小值和最大值;
START WITH 定義了序列的起始值,默認為 integer_type 類型的最小值;
CACHE 表示緩存的序列值個數(shù),可以提高訪問性能。默認不緩存;
CYCLE 表示循環(huán)使用序列的值,默認為 NO CYCLE;
OWNED BY 用于將序列與指定表的字段關(guān)聯(lián),此時刪除該字段會級聯(lián)刪除序列;默認為 NONE。
以下語句使用默認值創(chuàng)建一個序列 seq_users:
create sequence seq_users;
該語句創(chuàng)建了一個從 1 開始,增量為 1,最小值為 1,最大值為 ( 2 63 2^{63} 263 - 1) 的非循環(huán)序列。
PostgreSQL 使用 nextval 和 currval 函數(shù)獲取一個序列的值:
select nextval(‘seq_users’);
nextval |
---|
1|
select currval(‘seq_users’);
currval |
---|
1|
我們可以將字段的默認值設(shè)置為序列的 nextval 函數(shù)值,實現(xiàn)自增效果:
create table users(
user_id bigint default nextval(‘seq_users’) 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;
user_id | user_name |
---|
2|u1 |u1@test.com|
3|u2 |u2@test.com|
ALTER SEQUENCE語句可以修改序列的屬性,參數(shù)與CREATE SEQUENCE類似。例如:
alter sequence seq_users restart with 100;
insert into users(user_name, email) values (‘u3’, ‘u3@test.com’);
select * from users;
user_id | user_name |
---|
2|u1 |u1@test.com|
3|u2 |u2@test.com|
100|u3 |u3@test.com|
除此之外,使用 setval 函數(shù)也可以修改序列的值。
參考文檔:PostgreSQL 官方文檔 CREATE SEQUENCE 語句。
SERIAL
SERIAL 與 PostgreSQL 標識列類似,實際上是一個內(nèi)部的序列對象。例如:
create table users(
user_id serial primary key,
user_name varchar(50) not null,
email varchar(100)
);
等價于:
CREATE SEQUENCE users_user_id_seq AS integer;
create table users(
user_id integer NOT NULL DEFAULT nextval(‘users_user_id_seq’) primary key,
user_name varchar(50) not null,
email varchar(100)
);
ALTER SEQUENCE users_user_id_seq OWNED BY users.user_id;
PostgreSQL 首先創(chuàng)建一個序列對象,并且將該序列的 nextval 值設(shè)置為字段 user_id 的默認值;然后為 user_id 字段設(shè)置 NOT NULL 約束;最后將該序列的屬主設(shè)置為 user_id 字段,因此刪除該字段會級聯(lián)刪除序列對象。
然后插入一些測試數(shù)據(jù):
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;
user_id | user_name |
---|
1|u1 |u1@test.com|
2|u2 |u2@test.com|
除了 serial 之外,PostgreSQL 還提供了 smallserial 和 bigserial,分別對應(yīng) smallint 和 bigint 數(shù)據(jù)類型。
參考文檔:PostgreSQL 官方文檔 SERIAL 類型。
SQLite
簡單來說,在 SQLite 中不推薦使用 AUTOINCREMENT 字段。因為 SQLite 實現(xiàn)了一個隱式的自增字段 ROWID,很少有必要再顯式指定自增字段。
ROWID
默認情況下,CREATE TABLE語句創(chuàng)建的表中包含一個隱式的自增字段 rowid;它是一個 64 位的有符號整數(shù),用于唯一標識每一行數(shù)據(jù)。
首先,創(chuàng)建一個 users 表:
create table users(
user_name text not null,
email text
);
然后插入一些數(shù)據(jù):
insert into users values (‘u1’, ‘u1@test.com’), (‘u2’, ‘u2@test.com’);
select rowid, user_name, email
from users;
rowid | user_name |
---|
1|u1 |u1@test.com|
2|u2 |u2@test.com|
從上面的查詢結(jié)果可以看出,users 表包含了一個 rowid 字段,并且自動插入了從 1 開始遞增的數(shù)字。
?? SQLite 中的 _rowid_ 和 oid 都是 rowid 的同義詞。
如果在創(chuàng)建表時指定了 INTEGER 類型的主鍵字段,該字段實際上就是 rowid 的同義詞。例如:
drop table users;
create table users(
user_id integer primary key,
user_name text not null,
email text
);
其中,user_id 是 INTGER 類型的主鍵。此時我們插入一些數(shù)據(jù):
insert into users(user_name, email) values (‘u1’, ‘u1@test.com’);
insert into users(user_id, user_name, email) values (null, ‘u2’, ‘u2@test.com’);
select rowid, user_id, user_name, email
from users;
user_id | user_id | user_name |
---|
1| 1|u1 |u1@test.com|
2| 2|u2 |u2@test.com|
第一個插入語句沒有指定 user_id 的值,第二個語句為 user_id 指定了 NULL 值;這兩種情況下,SQLite 都會基于表中最大的 rowid 值生成一個遞增數(shù)字。
rowid 最大的值為 9223372036854775807?( 2 63 2^{63} 263 - 1);到達最大值之后,SQLite 會嘗試復(fù)用已經(jīng)被刪除的數(shù)字;如果沒有找到,將會提示 SQLITE_FULL 錯誤。
insert into users(user_id, user_name, email) values (9223372036854775807, ‘u3’, ‘u3@test.com’);
insert into users(user_name, email) values (‘u4’, ‘u4@test.com’);
select user_id, user_name, email
from users;
user_id | user_name |
---|
1|u1 |u1@test.com|
2|u2 |u2@test.com|
4461153425269426579|u4 |u4@test.com|
9223372036854775807|u3 |u3@test.com|
最后一個插入語句生成了一個未占用的數(shù)字作為 user_id 的值。
參考文檔:SQLite 官方文檔 CREATE TABLE 語句。
AUTOINCREMENT
SQLite 不推薦使用 AUTOINCREMENT 字段,因為大部分情況下都不需要,而且這種字段會消耗更多的 CPU、內(nèi)存、磁盤以及 I/O。
AUTOINCREMENT 字段與系統(tǒng) rowid 字段的唯一區(qū)別在于:AUTOINCREMENT 字段到達最大值之后不會重復(fù)生成未占用的數(shù)字,而是直接報錯。例如:
drop table users;
create table users(
user_id integer primary key autoincrement,
user_name text not null,
email text
);
insert into users(user_name, email) values (‘u1’, ‘u1@test.com’);
insert into users(user_id, user_name, email) values (null, ‘u2’, ‘u2@test.com’);
select rowid, user_id, user_name, email
from users;
user_id | user_id | user_name |
---|
1| 1|u1 |u1@test.com|
2| 2|u2 |u2@test.com|
其中,user_id 字段是自增主鍵。我們來看一下自增字段到達最大值之后的情況:
insert into users(user_id, user_name, email) values (9223372036854775807, ‘u3’, ‘u3@test.com’);
insert into users(user_name, email) values (‘u4’, ‘u4@test.com’);
SQL Error [13]: [SQLITE_FULL] Insertion failed because database is full (database or disk is full)
最后一個插入語句執(zhí)行失敗,提示數(shù)據(jù)庫或者磁盤已滿。
參考文檔:SQLite 官方文檔 Autoincrement 。