MySQLAUTO_INCREMENT的用法與注意事項(xiàng)
此文章有對(duì)MySQLAUTO_INCREMENT的實(shí)際用法的描述，也有對(duì)其相關(guān)性能的介紹與MySQL(和PHP搭配之最佳組合)中正確通過(guò)數(shù)據(jù)列的AUTO_INCREMENT屬性來(lái)自動(dòng)生成的實(shí)際操作步驟。
用法:
- CREATE TABLE test
- (
- id INT UNSIGNED NOT NULL Prima(最完善的虛擬主機(jī)管理系統(tǒng))RY KEY AUTO_INCREMENT,
- username VARCHAR(15) NOT NULL
- )
- AUTO_INCREMENT = 100;
在數(shù)據(jù)庫(kù)應(yīng)用，我們經(jīng)常要用到唯一編號(hào)，以標(biāo)識(shí)記錄。在MySQL(和PHP搭配之最佳組合)中可通過(guò)數(shù)據(jù)列的AUTO_INCREMENT屬性來(lái)自動(dòng)生成。MySQL(和PHP搭配之最佳組合)支持多種數(shù)據(jù)表，每種數(shù)據(jù)表的自增屬性都有差異，這里將介紹各種數(shù)據(jù)表里的數(shù)據(jù)列自增屬性。
ISAM表
如果把一個(gè)NULL插入到一個(gè)MySQLAUTO_INCREMENT數(shù)據(jù)列里去，MySQL(和PHP搭配之最佳組合)將自動(dòng)生成下一個(gè)序列編號(hào)。編號(hào)從1開(kāi)始，并1為基數(shù)遞增。
把0插入AUTO_INCREMENT數(shù)據(jù)列的效果與插入NULL值一樣。但不建議這樣做，還是以插入NULL值為好。
當(dāng)插入記錄時(shí)，沒(méi)有為AUTO_INCREMENT明確指定值，則等同插入NULL值。
當(dāng)插入記錄時(shí)，如果為AUTO_INCREMENT數(shù)據(jù)列明確指定了一個(gè)數(shù)值，則會(huì)出現(xiàn)兩種情況，情況一，如果插入的值與已有的編號(hào)重復(fù)，則會(huì)出現(xiàn)出錯(cuò)信息，因?yàn)锳UTO_INCREMENT數(shù)據(jù)列的值必須是唯一的；情況二，如果插入的值大于已編號(hào)的值，則會(huì)把該插入到數(shù)據(jù)列中，并使在下一個(gè)編號(hào)將從這個(gè)新值開(kāi)始遞增。也就是說(shuō)，可以跳過(guò)一些編號(hào)。
如果自增序列的最大值被刪除了，則在插入新記錄時(shí)，該值被重用。
如果用UPDATE命令更新自增列，如果列值與已有的值重復(fù)，則會(huì)出錯(cuò)。如果大于已有值，則下一個(gè)編號(hào)從該值開(kāi)始遞增。
如果用replace命令基于AUTO_INCREMENT數(shù)據(jù)列里的值來(lái)修改數(shù)據(jù)表里的現(xiàn)有記錄，即AUTO_INCREMENT數(shù)據(jù)列出現(xiàn)在了replace命令的where子句里，相應(yīng)的AUTO_INCREMENT值將不會(huì)發(fā)生變化。但如果replace命令是通過(guò)其它的Prima(最完善的虛擬主機(jī)管理系統(tǒng))RY KEY OR UNIQUE索引來(lái)修改現(xiàn)有記錄的(即AUTO_INCREMENT數(shù)據(jù)列沒(méi)有出現(xiàn)在replace命令的where子句中)，相應(yīng)的AUTO_INCREMENT值--如果設(shè)置其為NULL(如沒(méi)有對(duì)它賦值)的話--就會(huì)發(fā)生變化。
last_insert_id()函數(shù)可獲得自增列自動(dòng)生成的最后一個(gè)編號(hào)。但該函數(shù)只與服務(wù)器的本次會(huì)話過(guò)程中生成的值有關(guān)。如果在與服務(wù)器的本次會(huì)話中尚未生成MySQLAUTO_INCREMENT值，則該函數(shù)返回0。
其它數(shù)據(jù)表的自動(dòng)編號(hào)機(jī)制都以ISAM表中的機(jī)制為基礎(chǔ)。
MyISAM數(shù)據(jù)表
刪除最大編號(hào)的記錄后，該編號(hào)不可重用。
可在建表時(shí)可用“AUTO_INCREMENT=n”選項(xiàng)來(lái)指定一個(gè)自增的初始值。
可用alter table table_name AUTO_INCREMENT=n命令來(lái)重設(shè)自增的起始值。
可使用復(fù)合索引在同一個(gè)數(shù)據(jù)表里創(chuàng)建多個(gè)相互獨(dú)立的自增序列，具體做法是這樣的：為數(shù)據(jù)表創(chuàng)建一個(gè)由多個(gè)數(shù)據(jù)列組成的Prima(最完善的虛擬主機(jī)管理系統(tǒng))RY KEY OR UNIQUE索引，并把AUTO_INCREMENT數(shù)據(jù)列包括在這個(gè)索引里作為它的最后一個(gè)數(shù)據(jù)列。這樣，這個(gè)復(fù)合索引里，前面的那些數(shù)據(jù)列每構(gòu)成一種獨(dú)一無(wú)二的組合，最末尾的MySQLAUTO_INCREMENT數(shù)據(jù)列就會(huì)生成一個(gè)與該組合相對(duì)應(yīng)的序列編號(hào)。
HEAP數(shù)據(jù)表
HEAP數(shù)據(jù)表從MySQL(和PHP搭配之最佳組合)4.1開(kāi)始才允許使用自增列。
自增值可通過(guò)CREATE TABLE語(yǔ)句的 AUTO_INCREMENT=n選項(xiàng)來(lái)設(shè)置。
可通過(guò)ALTER TABLE語(yǔ)句的AUTO_INCREMENT=n選項(xiàng)來(lái)修改自增始初值。
編號(hào)不可重用。
HEAP數(shù)據(jù)表不支持在一個(gè)數(shù)據(jù)表中使用復(fù)合索引來(lái)生成多個(gè)互不干擾的序列編號(hào)。
BDB數(shù)據(jù)表
不可通過(guò)CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n選項(xiàng)來(lái)改變自增初始值。
可重用編號(hào)。
支持在一個(gè)數(shù)據(jù)表里使用復(fù)合索引來(lái)生成多個(gè)互不干擾的序列編號(hào)。
InnDB數(shù)據(jù)表
不可通過(guò)CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n選項(xiàng)來(lái)改變自增初始值。
不可重用編號(hào)。
不支持在一個(gè)數(shù)據(jù)表里使用復(fù)合索引來(lái)生成多個(gè)互不干擾的序列編號(hào)。
在使用AUTO_INCREMENT時(shí)，應(yīng)注意以下幾點(diǎn)：
AUTO_INCREMENT是數(shù)據(jù)列的一種屬性，只適用于整數(shù)類型數(shù)據(jù)列。
設(shè)置AUTO_INCREMENT屬性的數(shù)據(jù)列應(yīng)該是一個(gè)正數(shù)序列，所以應(yīng)該把該數(shù)據(jù)列聲明為UNSIGNED，這樣序列的編號(hào)個(gè)可增加一倍。
AUTO_INCREMENT數(shù)據(jù)列必須有唯一索引，以避免序號(hào)重復(fù)。
AUTO_INCREMENT數(shù)據(jù)列必須具備NOT NULL屬性。
AUTO_INCREMENT數(shù)據(jù)列序號(hào)的最大值受該列的數(shù)據(jù)類型約束，如TINYINT數(shù)據(jù)列的最大編號(hào)是127,如加上UNSIGNED，則最大為255。一旦達(dá)到上限，AUTO_INCREMENT就會(huì)失效。
當(dāng)進(jìn)行全表刪除時(shí)，MySQLAUTO_INCREMENT會(huì)從1重新開(kāi)始編號(hào)。全表刪除的意思是發(fā)出以下兩條語(yǔ)句時(shí)：
- delete from table_name;ortruncate table table_name
這是因?yàn)檫M(jìn)行全表操作時(shí)，MySQL(和PHP搭配之最佳組合)實(shí)際是做了這樣的優(yōu)化操作：先把數(shù)據(jù)表里的所有數(shù)據(jù)和索引刪除，然后重建數(shù)據(jù)表。如果想刪除所有的數(shù)據(jù)行又想保留序列編號(hào)信息，可這樣用一個(gè)帶where的delete命令以抑制MySQL(和PHP搭配之最佳組合)的優(yōu)化：
- delete from table_name where 1;
這將迫使MySQL(和PHP搭配之最佳組合)為每個(gè)刪除的數(shù)據(jù)行都做一次條件表達(dá)式的求值操作。
強(qiáng)制MySQL(和PHP搭配之最佳組合)不復(fù)用已經(jīng)使用過(guò)的序列值的方法是：另外創(chuàng)建一個(gè)專門(mén)用來(lái)生成AUTO_INCREMENT序列的數(shù)據(jù)表，并做到永遠(yuǎn)不去刪除該表的記錄。當(dāng)需要在主數(shù)據(jù)表里插入一條記錄時(shí)，先在那個(gè)專門(mén)生成序號(hào)的表中插入一個(gè)NULL值以產(chǎn)生一個(gè)編號(hào)，然后，在往主數(shù)據(jù)表里插入數(shù)據(jù)時(shí)，利用LAST_INSERT_ID()函數(shù)取得這個(gè)編號(hào)，并把它賦值給主表的存放序列的數(shù)據(jù)列。如：
- insert into id set id = NULL;insert into main set main_id = LAST_INSERT_ID();
可用alter命令給一個(gè)數(shù)據(jù)表增加一個(gè)具有MySQLAUTO_INCREMENT屬性的數(shù)據(jù)列。MySQL(和PHP搭配之最佳組合)會(huì)自動(dòng)生成所有的編號(hào)。
要重新排列現(xiàn)有的序列編號(hào)，最簡(jiǎn)單的方法是先刪除該列，再重建該，MySQL(和PHP搭配之最佳組合)會(huì)重新生連續(xù)的編號(hào)序列。
在不用AUTO_INCREMENT的情況下生成序列，可利用帶參數(shù)的LAST_INSERT_ID()函數(shù)。如果用一個(gè)帶參數(shù)的LAST_INSERT_ID(expr)去插入或修改一個(gè)數(shù)據(jù)列，緊接著又調(diào)用不帶參數(shù)的LAST_INSERT_ID()函數(shù)，則第二次函數(shù)調(diào)用返回的就是expr的值。下面演示該方法的具體操作：
先創(chuàng)建一個(gè)只有一個(gè)數(shù)據(jù)行的數(shù)據(jù)表：
- create table seq_table (id int unsigned not null);insert into seq_table values (0);
接著用以下操作檢索出序列號(hào)：update seq_table set seq = LAST_INSERT_ID( seq + 1 );select LAST_INSERT_ID();通過(guò)修改seq+1中的常數(shù)值，可生成不同步長(zhǎng)的序列，如seq+10可生成步長(zhǎng)為10的序列。
該方法可用于計(jì)數(shù)器，在數(shù)據(jù)表中插入多行以記錄不同的計(jì)數(shù)值。再配合LAST_INSERT_ID()函數(shù)的返回值生成不同內(nèi)容的計(jì)數(shù)值。這種方法的優(yōu)點(diǎn)是不用事務(wù)或LOCK，UNLOCK表就可生成唯一的序列編號(hào)。不會(huì)影響其它客戶程序的正常表操作。
【編輯推薦】
