Create TABLE test
(
id INT UNSIGNED NOT NULL PrimaRY KEY AUTO_INCREMENT,
username VARCHAR(15) NOT NULL
)
AUTO_INCREMENT = 100;

在數(shù)據(jù)庫應(yīng)用，我們經(jīng)常要用到唯一編號，以標(biāo)識記錄。在MySQL中可通過數(shù)據(jù)列的AUTO_INCREMENT屬性來自動生成。MySQL支持多種數(shù)據(jù)表，每種數(shù)據(jù)表的自增屬性都有差異，這里將介紹各種數(shù)據(jù)表里的數(shù)據(jù)列自增屬性。

ISAM表

如果把一個NULL插入到一個AUTO_INCREMENT數(shù)據(jù)列里去，MySQL將自動生成下一個序列編號。編號從1開始，并1為基數(shù)遞增。

把0插入AUTO_INCREMENT數(shù)據(jù)列的效果與插入NULL值一樣。但不建議這樣做，還是以插入NULL值為好。

當(dāng)插入記錄時，沒有為AUTO_INCREMENT明確指定值，則等同插入NULL值。

當(dāng)插入記錄時，如果為AUTO_INCREMENT數(shù)據(jù)列明確指定了一個數(shù)值，則會出現(xiàn)兩種情況，情況一，如果插入的值與已有的編號重復(fù)，則會出現(xiàn)出錯信息，因為AUTO_INCREMENT數(shù)據(jù)列的值必須是唯一的；情況二，如果插入的值大于已編號的值，則會把該插入到數(shù)據(jù)列中，并使在下一個編號將從這個新值開始遞增。也就是說，可以跳過一些編號。

如果自增序列的最大值被刪除了，則在插入新記錄時，該值被重用。

如果用Update命令更新自增列，如果列值與已有的值重復(fù)，則會出錯。如果大于已有值，則下一個編號從該值開始遞增。

如果用replace命令基于AUTO_INCREMENT數(shù)據(jù)列里的值來修改數(shù)據(jù)表里的現(xiàn)有記錄，即AUTO_INCREMENT數(shù)據(jù)列出現(xiàn)在了 replace命令的where子句里，相應(yīng)的AUTO_INCREMENT值將不會發(fā)生變化。但如果replace命令是通過其它的PrimaRY KEY or UNIQUE索引來修改現(xiàn)有記錄的(即AUTO_INCREMENT數(shù)據(jù)列沒有出現(xiàn)在replace命令的where子句中)，相應(yīng)的 AUTO_INCREMENT值--如果設(shè)置其為NULL(如沒有對它賦值)的話--就會發(fā)生變化。

last_insert_id()函數(shù)可獲得自增列自動生成的最后一個編號。但該函數(shù)只與服務(wù)器的本次會話過程中生成的值有關(guān)。如果在與服務(wù)器的本次會話中尚未生成AUTO_INCREMENT值，則該函數(shù)返回0。

其它數(shù)據(jù)表的自動編號機(jī)制都以ISAM表中的機(jī)制為基礎(chǔ)。

MyISAM數(shù)據(jù)表

刪除最大編號的記錄后，該編號不可重用。

可在建表時可用“AUTO_INCREMENT=n”選項來指定一個自增的初始值。

可用alter table table_name AUTO_INCREMENT=n命令來重設(shè)自增的起始值。

可使用復(fù)合索引在同一個數(shù)據(jù)表里創(chuàng)建多個相互獨立的自增序列，具體做法是這樣的：為數(shù)據(jù)表創(chuàng)建一個由多個數(shù)據(jù)列組成的PrimaRY KEY or UNIQUE索引，并把AUTO_INCREMENT數(shù)據(jù)列包括在這個索引里作為它的最后一個數(shù)據(jù)列。這樣，這個復(fù)合索引里，前面的那些數(shù)據(jù)列每構(gòu)成一種獨一無二的組合，最末尾的AUTO_INCREMENT數(shù)據(jù)列就會生成一個與該組合相對應(yīng)的序列編號。

HEAP數(shù)據(jù)表

HEAP數(shù)據(jù)表從MySQL4.1開始才允許使用自增列。

自增值可通過Create TABLE語句的 AUTO_INCREMENT=n選項來設(shè)置。

可通過Alter TABLE語句的AUTO_INCREMENT=n選項來修改自增始初值。

編號不可重用。

HEAP數(shù)據(jù)表不支持在一個數(shù)據(jù)表中使用復(fù)合索引來生成多個互不干擾的序列編號。

BDB數(shù)據(jù)表

不可通過Create TABLE or Alter TABLE的AUTO_INCREMENT=n選項來改變自增初始值。

可重用編號。

支持在一個數(shù)據(jù)表里使用復(fù)合索引來生成多個互不干擾的序列編號。

InnDB數(shù)據(jù)表

不可通過Create TABLE or Alter TABLE的AUTO_INCREMENT=n選項來改變自增初始值。

不可重用編號。

不支持在一個數(shù)據(jù)表里使用復(fù)合索引來生成多個互不干擾的序列編號。

在使用AUTO_INCREMENT時，應(yīng)注意以下幾點：

AUTO_INCREMENT是數(shù)據(jù)列的一種屬性，只適用于整數(shù)類型數(shù)據(jù)列。

設(shè)置AUTO_INCREMENT屬性的數(shù)據(jù)列應(yīng)該是一個正數(shù)序列，所以應(yīng)該把該數(shù)據(jù)列聲明為UNSIGNED，這樣序列的編號個可增加一倍。

AUTO_INCREMENT數(shù)據(jù)列必須有唯一索引，以避免序號重復(fù)。

AUTO_INCREMENT數(shù)據(jù)列必須具備NOT NULL屬性。

AUTO_INCREMENT數(shù)據(jù)列序號的最大值受該列的數(shù)據(jù)類型約束，如TINYINT數(shù)據(jù)列的最大編號是127,如加上UNSIGNED，則最大為255。一旦達(dá)到上限，AUTO_INCREMENT就會失效。

當(dāng)進(jìn)行全表刪除時，AUTO_INCREMENT會從1重新開始編號。全表刪除的意思是發(fā)出以下兩條語句時：

delete from table_name;ortruncate table table_name

這是因為進(jìn)行全表操作時，MySQL實際是做了這樣的優(yōu)化操作：先把數(shù)據(jù)表里的所有數(shù)據(jù)和索引刪除，然后重建數(shù)據(jù)表。如果想刪除所有的數(shù)據(jù)行又想保留序列編號信息，可這樣用一個帶where的delete命令以抑制MySQL的優(yōu)化：

delete from table_name where 1;

這將迫使MySQL為每個刪除的數(shù)據(jù)行都做一次條件表達(dá)式的求值操作。

強制MySQL不復(fù)用已經(jīng)使用過的序列值的方法是：另外創(chuàng)建一個專門用來生成AUTO_INCREMENT序列的數(shù)據(jù)表，并做到永遠(yuǎn)不去刪除該表的記錄。當(dāng)需要在主數(shù)據(jù)表里插入一條記錄時，先在那個專門生成序號的表中插入一個NULL值以產(chǎn)生一個編號，然后，在往主數(shù)據(jù)表里插入數(shù)據(jù)時，利用 LAST_Insert_ID()函數(shù)取得這個編號，并把它賦值給主表的存放序列的數(shù)據(jù)列。如：

insert into id set id = NULL;insert into main set main_id = LAST_Insert_ID();

可用alter命令給一個數(shù)據(jù)表增加一個具有AUTO_INCREMENT屬性的數(shù)據(jù)列。MySQL會自動生成所有的編號。

要重新排列現(xiàn)有的序列編號，最簡單的方法是先刪除該列，再重建該，MySQL會重新生連續(xù)的編號序列。

在不用AUTO_INCREMENT的情況下生成序列，可利用帶參數(shù)的LAST_Insert_ID()函數(shù)。如果用一個帶參數(shù)的 LAST_Insert_ID(expr)去插入或修改一個數(shù)據(jù)列，緊接著又調(diào)用不帶參數(shù)的LAST_Insert_ID()函數(shù)，則第二次函數(shù)調(diào)用返回的就是expr的值。下面演示該方法的具體操作：

先創(chuàng)建一個只有一個數(shù)據(jù)行的數(shù)據(jù)表：create table seq_table (id int unsigned not null);insert into seq_table values (0);接著用以下操作檢索出序列號：update seq_table set seq = LAST_Insert_ID( seq + 1 );select LAST_Insert_ID();通過修改seq+1中的常數(shù)值，可生成不同步長的序列，如seq+10可生成步長為10的序列。

該方法可用于計數(shù)器，在數(shù)據(jù)表中插入多行以記錄不同的計數(shù)值。再配合LAST_Insert_ID()函數(shù)的返回值生成不同內(nèi)容的計數(shù)值。這種方法的優(yōu)點是不用事務(wù)或LOCK，UNLOCK表就可生成唯一的序列編號。不會影響其它客戶程序的正常表操作。

下面給大家介紹MySQL數(shù)據(jù)庫修改自動遞增值

alter table tablename auto_increment=num 

其中tablename為表的名稱，num為要設(shè)置的新的自動遞增值，此時再Insert一條數(shù)據(jù)，自動遞增值即為num，不過num必須要大于等于現(xiàn)在已有的自動遞增值，否則SQL語句會執(zhí)行成功，但是實際上不起作用。

最新評論