自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

數(shù)據(jù)庫(kù)自增字段的三種實(shí)現(xiàn)方式

作者：不剪發(fā)的Tony老師
數(shù)據(jù)庫(kù)
在設(shè)計(jì)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)時(shí)，經(jīng)常會(huì)使用一個(gè)自動(dòng)增長(zhǎng)的數(shù)字序列作為主鍵字段（代理主鍵）。

在設(shè)計(jì)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)時(shí)，經(jīng)常會(huì)使用一個(gè)自動(dòng)增長(zhǎng)的數(shù)字序列作為主鍵字段（代理主鍵）。

除了作為主鍵使用之外，自增字段也可以用于記錄各個(gè)操作發(fā)生的先后順序，因?yàn)樗哂羞f增特性。當(dāng)我們插入一行數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)會(huì)為自增字段生成一個(gè)新的數(shù)值。

我們今天的主題就是自增字段的實(shí)現(xiàn)，下表列出了主流數(shù)據(jù)庫(kù)中創(chuàng)建自增字段的幾種方法：

接下來(lái)我們針對(duì)不同的數(shù)據(jù)庫(kù)進(jìn)行詳細(xì)討論。

MySQL

AUTO_INCREMENT

MySQL 通過(guò) AUTO_INCREMENT 屬性定義自增字段，并且需要遵循以下規(guī)則：

  • 每個(gè)表只能有一個(gè)自增字段，數(shù)據(jù)類(lèi)型一般是整數(shù)；
  • 自增字段必須創(chuàng)建主鍵（PRIMARY KEY）或者唯一索引（UNIQUE）；
  • 自增字段必須非空（NOT NULL），MySQL 會(huì)自動(dòng)為自增字段設(shè)置非空約束。

以下語(yǔ)句創(chuàng)建了一個(gè)表 users，其中 user_id 是一個(gè)自增主鍵字段：

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

接下來(lái)我們插入兩條數(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|email      |
-------|---------|-----------|
      1|u1       |u1@test.com|
      2|u2       |u2@test.com|

在上面的插入語(yǔ)句中，我們沒(méi)有指定 user_id 的值，此時(shí) MySQL 會(huì)自動(dòng)為該字段生成一個(gè)遞增序列值。AUTO_INCREMENT 字段的值默認(rèn)從 1 開(kāi)始，每次遞增也是 1。

如果插入數(shù)據(jù)時(shí)為自增字段指定了 NULL 值或者 0，MySQL 同樣會(huì)自動(dòng)生成一個(gè)序列值。

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|email      |
-------|---------|-----------|
      1|u1       |u1@test.com|
      2|u2       |u2@test.com|
      3|u3       |u3@test.com|
      4|u4       |u4@test.com|

如果插入數(shù)據(jù)時(shí)為自增字段指定了非空也非 0 的值，MySQL 會(huì)使用我們提供的值；而且還會(huì)將自增序列的起始值值設(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|email      |
-------|---------|-----------|
      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|

上面的第一個(gè)插入語(yǔ)句為 user_id 提供了值 100，第二個(gè)插入語(yǔ)句使用系統(tǒng)提供的自增序列值，此時(shí)跳躍到了 101。

MySQL 提供了 LAST_INSERT_ID 函數(shù)，用于獲取最后一次生成的序列值。

另外，MySQL 也可以使用`ALTER TABLE`語(yǔ)句設(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|email      |
-------|---------|-----------|
    200|u7       |u7@test.com|

最后我們來(lái)看一個(gè)問(wèn)題，當(dāng)自增序列到達(dá)最大值之后怎么辦。下面的語(yǔ)句演示了這種情況：

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 類(lèi)型的最大值；然后插入兩條數(shù)據(jù)，第二個(gè)插入語(yǔ)句出現(xiàn)主鍵值重復(fù)，意味著自增字段到達(dá)最大值之后一直保持不變。

如果擔(dān)心自增字段的值不夠用，可以將 INT 類(lèi)型改成 INT UNSIGNED，最大值可以到達(dá) 4294967295；還不夠的話改成 BIGINT，最大值可以到達(dá) 9223372036854775807。

MySQL 中的 SERIAL 關(guān)鍵字是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的同義詞。

Oracle

Oracle 數(shù)據(jù)庫(kù)提供了兩種創(chuàng)建自增字段的方法：

  • 使用標(biāo)識(shí)列（IDENTITY），需要使用 Oracle 12c 以上版本；
  • 使用序列（SEQUENCE）。

標(biāo)識(shí)列

Oracle 12c 提供創(chuàng)建 SQL 標(biāo)準(zhǔn)定義的標(biāo)識(shí)列功能：

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

其中，

  •  GENERATED ALWAYS表示總是為標(biāo)識(shí)列使用 Oracle 生成的值，如果用戶(hù)指定該字段的值將會(huì)出錯(cuò)；
  •  GENERATED BY DEFAULT表示如果用戶(hù)沒(méi)有提供值，使用 Oracle 生成的值；如果提供了值，使用用戶(hù)提供的值；如果指定了 NULL 值將會(huì)出錯(cuò)；
  •  GENERATED BY DEFAULT ON NULL表示如果用戶(hù)沒(méi)有提供值或者提供了 NULL 值，使用 Oracle 生成的值；否則使用用戶(hù)提供的值。

Oracle 中的標(biāo)識(shí)列實(shí)際上是一個(gè)內(nèi)部創(chuàng)建序列對(duì)象，因此 identity_options 與序列的屬性類(lèi)似，主要包括：

  • START WITH n指定序列的初始值，默認(rèn)為 1；
  • INCREMENT BY n指定序列的增量值，默認(rèn)為 1；
  • MAXVALUE n和MINVALUE n指定序列的最大值和最小值，默認(rèn)值為 9999999999999999999999999999 和 1；
  • CYCLE和NOCYCLE指定是否循環(huán)使用序列中的值，默認(rèn)不循環(huán)使用；
  • CACHE n和NOCACHE指定是否緩存序列值，緩存可以提高性能。默認(rèn)緩存 20 個(gè)。

以下語(yǔ)句創(chuàng)建了一個(gè)表 users，其中 user_id 是一個(gè)自增主鍵字段：

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 開(kāi)始。

我們測(cè)試一下數(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|EMAIL      |
-------|---------|-----------|
    100|u1       |u1@test.com|
    110|u2       |u2@test.com|

我們沒(méi)有使用`GENERATED BY DEFAULT ON NULL`選項(xiàng)，如果插入 NULL 值將會(huì)出錯(cuò)：

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")

不過(guò)，我們可以為 user_id 指定非空的值：

insert into users(user_id, user_name, email) values (0, 'u4', 'u4@test.com');


select * from users;
USER_ID|USER_NAME|EMAIL      |
-------|---------|-----------|
    100|u1       |u1@test.com|
    110|u2       |u2@test.com|
      0|u4       |u4@test.com|

Oracle 標(biāo)識(shí)列需要遵循以下限制：

  • 每個(gè)表只能有一個(gè)標(biāo)識(shí)列；
  • 標(biāo)識(shí)列的數(shù)據(jù)類(lèi)型必須是數(shù)字類(lèi)型，不能使用自定義類(lèi)型；
  • CREATE TABLE AS SELECT語(yǔ)句不會(huì)繼承標(biāo)識(shí)列；
  • 標(biāo)識(shí)列不能指定 DEFAULT 約束。

序列

序列（Sequence）是數(shù)據(jù)庫(kù)中的一種對(duì)象，用于生成一系列遞增或遞減的數(shù)字。序列使用CREATE SEQUENCE語(yǔ)句創(chuàng)建：

CREATE SEQUENCE seq_users;

以上語(yǔ)句使用默認(rèn)選項(xiàng)創(chuàng)建了一個(gè)序列 seq_users，等價(jià)于下面的語(yǔ)句：

CREATE SEQUENCE seq_users
  START WITH 1
  INCREMENT BY 1
  NOMAXVALUE
  NOMINVALUE
  CACHE 20
  NOCYCLE;

Oracle 序列的數(shù)據(jù)類(lèi)型為 NUMBER，包含一個(gè)最小值，一個(gè)最大值，一個(gè)起始值，一個(gè)增量值，緩存選項(xiàng)以及一個(gè)循環(huán)使用選項(xiàng)。這些參數(shù)的作用可以參考上面的標(biāo)識(shí)列。

創(chuàng)建之后，我們可以使用 NEXTVAL 和 CURRVAL 偽列獲取序列的值：

SELECT seq_users.nextval FROM dual;
NEXTVAL|
-------|
      1|


SELECT seq_users.currval FROM dual;
CURRVAL|
-------|
      1|

NEXTVAL 用于從序列中獲取下一個(gè)值，CURRVAL 返回了當(dāng)前會(huì)話最后一次獲取的序列值。

利用序列，我們可以為表中的字段生成不重復(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|EMAIL      |
-------|---------|-----------|
      2|u1       |u1@test.com|
      3|u2       |u2@test.com|

在上面的示例中，我們手動(dòng)為 user_id 字段指定了 seq_users.nextval 值。如果想要實(shí)現(xiàn)自增字段的效果，可以利用觸發(fā)器實(shí)現(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 是否為空，如果為空就生成一個(gè)新的序列號(hào)。我們?cè)俨迦胍恍?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|EMAIL      |
-------|---------|-----------|
      2|u1       |u1@test.com|
      3|u2       |u2@test.com|
      4|u3       |u3@test.com|
      5|u4       |u4@test.com|

上面兩個(gè)插入語(yǔ)句都沒(méi)有為 user_id 提供數(shù)據(jù)，而是由觸發(fā)器自動(dòng)生成一個(gè)數(shù)字編號(hào)。

另一個(gè)更簡(jiǎn)單的方式就是將字段的默認(rèn)值設(shè)置為序列的值：

create table users(
  user_id NUMBER DEFAULT seq_users.nextval PRIMARY KEY,
  user_name VARCHAR2(50) NOT NULL,
  email VARCHAR2(100)
);

實(shí)際上，Oracle 中的標(biāo)識(shí)列就是采用這種方法實(shí)現(xiàn)的，只不過(guò)增加了一些額外的限制而已。

Oracle 提供了ALTER SEQUENCE語(yǔ)句，可以修改序列的屬性：

ALTER SEQUENCE seq_users
  INCREMENT BY 2
  MAXVALUE 10000
  CYCLE;

以上語(yǔ)句將序列 seq_users 的增量修改為 2，最大值修改為 10000，并且再到達(dá)最大值之后再次從最小值開(kāi)始循環(huán)。不過(guò)，Oracle 序列不能修改起始值（START WITH），只能使用DROP SEQUENCE seq_name;語(yǔ)句刪除序列再重建創(chuàng)建。

SQL Server

Microsoft SQL Server 提供了兩種創(chuàng)建自增字段的方法：

  • 使用標(biāo)識(shí)列（IDENTITY）；
  • 使用序列（SEQUENCE）。

標(biāo)識(shí)列

SQL Server 支持為字段指定 IDENTITY(start, increment) 屬性的方法定義一個(gè)標(biāo)識(shí)列，start 表示序列的起始值，increment 表示每次的增量值。例如：

create table users(
  user_id int identity primary key,
  user_name varchar(50) not null,
  email varchar(100)
);

其中，user_id 是一個(gè) INTEGER 類(lèi)型的標(biāo)識(shí)列；系統(tǒng)生成的序列值默認(rèn)從 1 開(kāi)始，每次遞增也是 1。SQL Server 中每個(gè)表只能定義一個(gè)標(biāo)識(shí)列。

我們插入一些測(cè)試數(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|email      |
-------|---------|-----------|
      1|u1       |u1@test.com|
      2|u2       |u2@test.com|

以上語(yǔ)句通過(guò)標(biāo)識(shí)列自動(dòng)生成了兩個(gè)用戶(hù)編號(hào)。我們可以利用獲取最后一次插入的標(biāo)識(shí)列的值：

select @@identity;

需要注意的是，不能為標(biāo)識(shí)列指定 NULL 值；默認(rèn)也不能為標(biāo)識(shí)列手動(dòng)指定值。

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.

第一個(gè)語(yǔ)句為 user_id 指定了 NULL 值；第二個(gè)語(yǔ)句的錯(cuò)誤在于為 user_id 指定了明確的值，不過(guò)可以通過(guò)設(shè)置表的 IDENTITY_INSERT  屬性修改默認(rèn)行為。

序列

SQL Server 提供了和 Oracle 類(lèi)似的序列對(duì)象，用于生成一個(gè)遞增或遞減的數(shù)字序列。創(chuàng)建序列的完整語(yǔ)法如下：

CREATE SEQUENCE sequence_name  
    [ AS integer_type ]  
    [ START WITH <constant>]  
    [ INCREMENT BY <constant>]  
    [ { MINVALUE [ <constant>] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ <constant>] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ <constant>] } | { NO CACHE } ];

其中，

  • sequence_name 是序列名；
  • AS 定義了序列的類(lèi)型。默認(rèn)為 BIGINT，也可以是 TINYINT、SMALLINT、INT 或者不帶小數(shù)位的 DECIMAL 和 NUMERIC；
  • START WITH 定義了序列的起始值，默認(rèn)為 integer_type 類(lèi)型的最小值；
  • INCREMENT BY 指定了序列的增量值，可以是負(fù)數(shù)，默認(rèn)為 1；
  • MINVALUE 和 MAXVALUE 分別定義序列的最小值和最大值，默認(rèn)為 integer_type 類(lèi)型的最小值和最大值；
  • CYCLE 表示循環(huán)使用序列的值，默認(rèn)為 NO CYCLE；
  • CACHE 表示緩存的序列值個(gè)數(shù)，可以提高訪問(wèn)性能。默認(rèn)不緩存。

以下語(yǔ)句使用默認(rèn)值創(chuàng)建一個(gè)序列 seq_users：

create sequence seq_users;

使用 NEXT VALUE FOR 函數(shù)獲取一個(gè)序列的值：

select next value for seq_users;
                    |
--------------------|
-9223372036854775808|

返回的是 INTEGR 類(lèi)型的最小值。

我們可以將字段的默認(rèn)值設(shè)置為序列的 NEXT VALUE FOR 函數(shù)值，實(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|email      |
--------------------|---------|-----------|
-9223372036854775806|u1       |u1@test.com|
-9223372036854775805|u2       |u2@test.com|

ALTER SEQUENCE語(yǔ)句可以修改序列的屬性，參數(shù)與CREATE SEQUENCE類(lèi)似，除了 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|email      |
--------------------|---------|-----------|
-9223372036854775807|u1       |u1@test.com|
-9223372036854775806|u2       |u2@test.com|
                   1|u3       |u3@test.com|

PostgreSQL

PostgreSQL 提供了多種方法實(shí)現(xiàn)自增字段，包括：

  • 標(biāo)識(shí)列（IDENTITY），PostgreSQL 10 以及更高版本；
  • 序列（SEQUENCE）；
  • SERIAL。

標(biāo)識(shí)列

PostgreSQL 實(shí)現(xiàn)了 SQL 標(biāo)準(zhǔn)中的標(biāo)識(shí)列，語(yǔ)法與 Oracle 幾乎相同：

column_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

其中，

  • data_type 可以是 SMALLINT、INT或者 BIGINT 等整數(shù)類(lèi)型；
  • GENERATED ALWAYS表示總是使用 PostgreSQL 生成的值，如果用戶(hù)指定該字段的值將會(huì)出錯(cuò)，除非指定了 OVERRIDING SYSTEM VALUE 選項(xiàng)；
  • GENERATED BY DEFAULT表示如果用戶(hù)沒(méi)有提供值，使用 PostgreSQL 生成的值；如果提供了值，使用用戶(hù)的值；
  • sequence_option 用于指定序列對(duì)象的選項(xiàng)。PostgreSQL 標(biāo)識(shí)列實(shí)際上使用一個(gè)內(nèi)部的序列對(duì)象來(lái)實(shí)現(xiàn)，具體選項(xiàng)參考下文中的序列。

以下語(yǔ)句創(chuàng)建了一個(gè)表 users，其中 user_id 是一個(gè)標(biāo)識(shí)列：

create table users(
  user_id int generated always as identity primary key,
  user_name varchar(50) not null,
  email varchar(100)
);

此時(shí)，PostgreSQL 自動(dòng)創(chuàng)建了一個(gè)序列對(duì)象 users_user_id_seq。我們測(cè)試一下數(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|email      |
-------|---------|-----------|
      1|u1       |u1@test.com|
      2|u2       |u2@test.com|

標(biāo)識(shí)列默認(rèn)從 1 開(kāi)始，每次遞增也是 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.

該語(yǔ)句執(zhí)行錯(cuò)誤，不過(guò)我們可以使用`INSERT`語(yǔ)句的 OVERRIDING SYSTEM VALUE 選項(xiàng)覆蓋系統(tǒng)提供的值。

序列

與 Oracle 和 SQL Server 類(lèi)似，PostgreSQL 也實(shí)現(xiàn)了 SQL 標(biāo)準(zhǔn)中的序列對(duì)象。創(chuàng)建序列的語(yǔ)法如下：

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 定義了序列的類(lèi)型。默認(rèn)為 BIGINT，也可以是 SMALLINT或者 INTEGER；
  • INCREMENT BY 指定了序列的增量值，可以是負(fù)數(shù)，默認(rèn)為 1；
  • MINVALUE 和 MAXVALUE 分別定義序列的最小值和最大值，默認(rèn)為 integer_type 類(lèi)型的最小值和最大值；
  • START WITH 定義了序列的起始值，默認(rèn)為 integer_type 類(lèi)型的最小值；
  • CACHE 表示緩存的序列值個(gè)數(shù)，可以提高訪問(wèn)性能。默認(rèn)不緩存；
  • CYCLE 表示循環(huán)使用序列的值，默認(rèn)為 NO CYCLE；
  • OWNED BY 用于將序列與指定表的字段關(guān)聯(lián)，此時(shí)刪除該字段會(huì)級(jí)聯(lián)刪除序列；默認(rèn)為 NONE。

以下語(yǔ)句使用默認(rèn)值創(chuàng)建一個(gè)序列 seq_users：

create sequence seq_users;

該語(yǔ)句創(chuàng)建了一個(gè)從 1 開(kāi)始，增量為 1，最小值為 1，最大值為 9,223,372,036,854,775,807 的非循環(huán)序列。

PostgreSQL 使用 nextval 和 currval 函數(shù)獲取一個(gè)序列的值：

select nextval('seq_users');
nextval|
-------|
      1|




select currval('seq_users');
currval|
-------|
      1|

我們可以將字段的默認(rèn)值設(shè)置為序列的 nextval  函數(shù)值，實(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|email      |
-------|---------|-----------|
      2|u1       |u1@test.com|
      3|u2       |u2@test.com|

ALTER SEQUENCE語(yǔ)句可以修改序列的屬性，參數(shù)與CREATE SEQUENCE類(lèi)似。例如：

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|email      |
-------|---------|-----------|
      2|u1       |u1@test.com|
      3|u2       |u2@test.com|
    100|u3       |u3@test.com|

除此之外，使用 setval 函數(shù)也可以修改序列的值。

SERIAL

SERIAL 與 PostgreSQL 標(biāo)識(shí)列類(lèi)似，實(shí)際上是一個(gè)內(nèi)部的序列對(duì)象。例如：

create table users(
  user_id serial primary key,
  user_name varchar(50) not null,
  email varchar(100)
);

等價(jià)于：

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)建一個(gè)序列對(duì)象，并且將該序列的 nextval 值設(shè)置為字段 user_id 的默認(rèn)值；然后為 user_id 字段設(shè)置 NOT NULL 約束；最后將該序列的屬主設(shè)置為 user_id 字段，因此刪除該字段會(huì)級(jí)聯(lián)刪除序列對(duì)象。

然后插入一些測(cè)試數(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|email      |
-------|---------|-----------|
      1|u1       |u1@test.com|
      2|u2       |u2@test.com|

除了 serial 之外，PostgreSQL 還提供了 smallserial 和 bigserial，分別對(duì)應(yīng) smallint 和 bigint 數(shù)據(jù)類(lèi)型。

SQLite

簡(jiǎn)單來(lái)說(shuō)，在 SQLite 中不推薦使用 AUTOINCREMENT 字段。因?yàn)?SQLite 實(shí)現(xiàn)了一個(gè)隱式的自增字段 ROWID，很少有必要再顯式指定自增字段。

ROWID

默認(rèn)情況下，CREATE TABLE語(yǔ)句創(chuàng)建的表中包含一個(gè)隱式的自增字段 rowid；它是一個(gè) 64 位的有符號(hào)整數(shù)，用于唯一標(biāo)識(shí)每一行數(shù)據(jù)。

首先，創(chuàng)建一個(gè) 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|email      |
-----|---------|-----------|
    1|u1       |u1@test.com|
    2|u2       |u2@test.com|

從上面的查詢(xún)結(jié)果可以看出，users 表包含了一個(gè) rowid 字段，并且自動(dòng)插入了從 1 開(kāi)始遞增的數(shù)字。

SQLite 中的 _rowid_ 和 oid 都是 rowid 的同義詞。

如果在創(chuàng)建表時(shí)指定了 INTEGER 類(lèi)型的主鍵字段，該字段實(shí)際上就是 rowid 的同義詞。例如：

drop table users;


create table users(
  user_id integer primary key,
  user_name text not null,
  email text 
);

其中，user_id 是 INTGER 類(lèi)型的主鍵。此時(shí)我們插入一些數(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|email      |
-------|-------|---------|-----------|
      1|      1|u1       |u1@test.com|
      2|      2|u2       |u2@test.com|

第一個(gè)插入語(yǔ)句沒(méi)有指定 user_id 的值，第二個(gè)語(yǔ)句為 user_id 指定了 NULL 值；這兩種情況下，SQLite 都會(huì)基于表中最大的 rowid 值生成一個(gè)遞增數(shù)字。

rowid 最大的值為 9223372036854775807?；到達(dá)最大值之后，SQLite 會(huì)嘗試復(fù)用已經(jīng)被刪除的數(shù)字；如果沒(méi)有找到，將會(huì)提示 SQLITE_FULL 錯(cuò)誤。

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|email      |
-------------------|---------|-----------|
                  1|u1       |u1@test.com|
                  2|u2       |u2@test.com|
4461153425269426579|u4       |u4@test.com|
9223372036854775807|u3       |u3@test.com|

最后一個(gè)插入語(yǔ)句生成了一個(gè)未占用的數(shù)字作為 user_id 的值。

AUTOINCREMENT

SQLite 不推薦使用 AUTOINCREMENT 字段，因?yàn)榇蟛糠智闆r下都不需要，而且這種字段會(huì)消耗更多的 CPU、內(nèi)存、磁盤(pán)以及 I/O。

AUTOINCREMENT 字段與系統(tǒng) rowid 字段的唯一區(qū)別在于：AUTOINCREMENT 字段到達(dá)最大值之后不會(huì)重復(fù)生成未占用的數(shù)字，而是直接報(bào)錯(cuò)。例如：

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|email      |
-------|-------|---------|-----------|
      1|      1|u1       |u1@test.com|
      2|      2|u2       |u2@test.com|

其中，user_id 字段是自增主鍵。我們來(lái)看一下自增字段到達(dá)最大值之后的情況：

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)

最后一個(gè)插入語(yǔ)句執(zhí)行失敗，提示數(shù)據(jù)庫(kù)或者磁盤(pán)已滿(mǎn)。

責(zé)任編輯：華軒 來(lái)源： SQL編程思想
數(shù)據(jù)庫(kù)自增字段MySQL
相關(guān)推薦

2010-10-26 13:33:08

Oracle自動(dòng)備份

2011-08-18 18:34:00

Oracle數(shù)據(jù)庫(kù)創(chuàng)建自增字段

2014-12-31 17:42:47

LBSAndroid地圖

2021-06-24 08:52:19

單點(diǎn)登錄代碼前端

2021-11-05 21:33:28

Redis數(shù)據(jù)高并發(fā)

2010-03-12 17:52:35

Python輸入方式

2019-12-09 10:03:40

MySQLSQL數(shù)據(jù)庫(kù)

2018-04-02 14:29:18

Java多線程方式

2023-12-04 09:31:13

CSS卡片

2024-07-01 12:42:58

2010-06-18 13:47:21

SQL Server數(shù)

2010-11-19 14:51:09

Oracle數(shù)據(jù)庫(kù)關(guān)閉

2011-05-26 13:16:37

Oracle數(shù)據(jù)庫(kù)備份

2010-07-29 09:56:45

Flex數(shù)據(jù)庫(kù)

2017-07-14 15:07:23

2013-06-17 17:08:47

Windows PhoWP開(kāi)發(fā)共享數(shù)據(jù)方式

2011-05-20 17:08:32

2024-07-08 09:03:31

2022-11-03 08:44:24

代理模式Java設(shè)計(jì)模式

2012-07-17 09:16:16

SpringSSH
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)