4.7 自增約束

自增約束是一種由系統(tǒng)自動增加并填入字段數(shù)值的約束。如將商品信息表中的商品編號字段設(shè)置為自增約束字段，那么系統(tǒng)默認對添加的第一條商品信息中，設(shè)置商品編號字段值為1；第二條記錄的商品編號字段值為2，以此類推。

自增約束字段由系統(tǒng)自動填入數(shù)據(jù)，在節(jié)省用戶工作量和時間的同時，避免因疏忽而加入錯誤數(shù)據(jù)。本節(jié)介紹自增約束的使用。

4.7.1 自增約束概述

自增約束使用AUTO_INCREMENT修飾符，只適用于INT類型的字段，使該字段的數(shù)值每次增加相同的量，通常每次增加1。如該字段的值可以是1、2、3，可以是2001、2002、2003，而不能是b1、b2、b3。在使用AUTO_INCREMENT時，應(yīng)注意以下幾點。

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

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

（3）AUTO_INCREMENT數(shù)據(jù)列必須有唯一約束，以避免序號重復(fù)。

（4）MySQL表中只能有一個AUTO_INCREMENT字段。

（5）自增字段必須創(chuàng)建索引，如主鍵索引、唯一索引等。

（6）有著外鍵約束的字段不能夠設(shè)置為自增字段。

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

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

（9）當(dāng)進行全表數(shù)據(jù)刪除時，AUTO_INCREMENT會從1重新開始編號。全表數(shù)據(jù)刪除指刪除表中的所有數(shù)據(jù)，保留表的結(jié)構(gòu)及其字段定義。

（10）進行全表數(shù)據(jù)操作時，MySQL實際是做了這樣的優(yōu)化操作：先把數(shù)據(jù)表里的所有數(shù)據(jù)和約束刪除，然后重建數(shù)據(jù)表。

（11）被DELETE語句刪除的自增約束字段值，除非SQL語句中將該字段值重新插入，否則前面空余的字段值不會復(fù)用。

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

MySQL支持多種數(shù)據(jù)表，每種數(shù)據(jù)表的自增屬性都有所不同。對于MyISAM表，如果用UPDATE語句更新自增列，如果列值與已有的值重復(fù)，則會出錯。如果大于已有值，則下一個編號從該值開始遞增。但是對于InnoDB表，UPDATEAUTO_INCREMENT字段會導(dǎo)致發(fā)生報錯。以下列舉幾種常用的表及其自增約束的使用。

1．ISAM表

對于ISAM表，如果把一個NULL插入到一個AUTO_INCREMENT數(shù)據(jù)列里去，MySQL將自動生成下一個序列編號。編號從1開始，并以1為基數(shù)遞增。在該表中使用自增約束，有以下兩種情況。

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

當(dāng)插入記錄時，如果為AUTO_INCREMENT字段明確指定了一個數(shù)值，則會出現(xiàn)兩種情況。

（1）如果插入的值與已有的編號重復(fù)，則會出現(xiàn)出錯信息，因為AUTO_INCREMENT數(shù)據(jù)列的值必須是唯一的。

（2）如果插入的值大于已編號的值，則會把該值插入到數(shù)據(jù)列中，并使下一個編號從這個新值開始遞增。也就是說，可以跳過一些編號。

對有著標(biāo)識列的表數(shù)據(jù)進行操作，有以下幾種情況。

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

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

（3）如果用REPLACE語句修改數(shù)據(jù)表里的現(xiàn)有記錄相應(yīng)的AUTO_INCREMENT值將不會發(fā)生變化。

last_insert_id()函數(shù)可獲得自增列自動生成的最后一個編號。但該函數(shù)只與服務(wù)器的本次會話過程中生成的值有關(guān)。如果在與服務(wù)器的本次會話中尚未生成AUTO_INCREMENT值，則該函數(shù)返回0。其他數(shù)據(jù)表的自動編號機制都以ISAM表中的機制為基礎(chǔ)。

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

在MyISAM數(shù)據(jù)表中，其自動編號機制有以下幾個特點。

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

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

（3）可用ALTER TABLE table_name AUTO_INCREMENT=n命令來重設(shè)自增的起始值。

（4）可使用復(fù)合約束在同一個數(shù)據(jù)表里創(chuàng)建多個相互獨立的自增序列。

使用復(fù)合約束，在同一個表中創(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)的序列編號。

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

在HEAP數(shù)據(jù)表中，其自動編號機制有以下幾個特點。

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

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

（3）編號不可重用。

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

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

在BDB數(shù)據(jù)表中，其自動編號機制有以下幾個特點。

（1）BDB數(shù)據(jù)表不能通過CREATE TABLE OR ALTER TABLE的AUTO_ INCREMENT=n選項來改變自增初始值。

（2）可重用編號。

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

5．InnoDB數(shù)據(jù)表

在InnoDB數(shù)據(jù)表中，其自動編號機制有以下幾個特點。

（1）不可通過CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n選項來改變自增初始值。

（2）不可重用編號。

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

4.7.2 初始值和偏移量

在MySQL Workbench工具下，只能夠創(chuàng)建初始值和遞增偏移量均為1的自增約束，不過使用SQL語句可以設(shè)置自增約束的相關(guān)屬性和變量，修改字段初始值和遞增偏移量。這幾個屬性和變量及其含義如下所示。

（1）AUTO_INCREMENT：自增初始值。

（2）auto_increment_increment：自增值的自增量。

（3）auto_increment_offset：自增值的偏移量。

自增量和偏移量均可以設(shè)置為全局或局部變量，并且在字段的數(shù)據(jù)類型和約束允許的情況下，每個值都可以為1～65 535之間的整數(shù)值。如果將變量設(shè)置為0會使該變量的值為1；如果試圖將這些變量設(shè)置為大于65 535或小于0的值，則會將該值設(shè)置為65 535；如果將auto_increment_increment或auto_increment_offset設(shè)置為非整數(shù)值，則會給出錯誤，并且變量的實際值在這種情況下保持不變。

自增約束字段的數(shù)據(jù)值并不是必須從一開始就嚴格按照指定初始值和偏移量來編號的，如一個字段原有數(shù)據(jù)1，2，1，2，那么同樣可以在此基礎(chǔ)上為字段添加自增約束并設(shè)置初始值為大于2的整數(shù)。

并且若數(shù)據(jù)表中自增字段的數(shù)據(jù)已經(jīng)從1排到了10，也可以在此基礎(chǔ)上修改自增約束的初始值和偏移量，使10以后的數(shù)據(jù)根據(jù)新的自增規(guī)則進行編號。如在自增字段有著1～10這10個數(shù)據(jù)的表中修改自增約束的遞增規(guī)則（修改初始值和偏移量兩個變量的值）為從15開始每次偏移5，那么第11條數(shù)據(jù)的字段值為15、第12條數(shù)據(jù)的字段值為20。

如果修改的初始值比當(dāng)前的數(shù)據(jù)值小，執(zhí)行的SQL不會報錯，但是不會生效！MyISAM和InnoDB均是如此。

如果同時設(shè)置了auto_increment_offset和auto_increment_increment，那么這兩個變量的值最好相同。如果auto_increment_offset的值大于auto_increment_increment的值，auto_increment_offset的值將被忽略。

4.7.3 創(chuàng)建自增約束

創(chuàng)建自增約束可以在表創(chuàng)建或表修改界面（如圖4-1所示）中選中AI（或Auto Increment）復(fù)選框。

在MySQL Workbench工具下創(chuàng)建的自增約束不需要提供字段的初始值和遞增偏移量，默認從1開始每次增加1。

使用SQL語句創(chuàng)建表的時候設(shè)置自增約束，代碼如下。

CREATE TABLE 表名 (字段名 數(shù)據(jù)類型 非空約束類型 AUTO_INCREMENT);

上述代碼創(chuàng)建了有著默認自增約束的字段，也可在創(chuàng)建語句后為該自增約束添加自增初始值，代碼如下。

CREATE TABLE 表名 (字段名 數(shù)據(jù)類型 非空約束類型 AUTO_INCREMENT)
    AUTO_INCREMENT=初始值;

【范例15】

創(chuàng)建表fruitshop.worker，為主鍵id添加默認自增約束，代碼如下。

CREATE TABLE 'fruitshop'.'worker' (
      'id' INT NOT NULL AUTO_INCREMENT,
      'name' VARCHAR(45) NULL,
      'sex' VARCHAR(4) NULL,
      'age' INT NULL,
      PRIMARY KEY ('id'));

【范例16】

創(chuàng)建表fruitshop.worker，為主鍵id添加自增約束初始值為8，代碼如下。

CREATE TABLE 'fruitshop'.'worker' (
      'id' INT NOT NULL AUTO_INCREMENT,
      'name' VARCHAR(45) NULL,
      'sex' VARCHAR(4) NULL,
      'age' INT NULL,
      PRIMARY KEY ('id')
    )
    AUTO_INCREMENT=8;

4.7.4 修改自增約束

修改自增約束包括多種操作：為字段添加自增約束；修改自增約束的初始值；修改自增約束的偏移量。

1．為字段添加自增約束

使用修改表字段類型的方法，通過MODIFY關(guān)鍵字來添加，語法如下。

ALTER TABLE 表名 MODIFY COLUMN 字段名 數(shù)據(jù)類新 非空約束 AUTO_INCREMENT;

【范例17】

為fruitshop.fruits表的主鍵fid字段添加自增約束，代碼如下。

ALTER TABLE 'fruitshop'.'fruits' MODIFY COLUMN fid INT NOT NULL AUTO_ INCREMENT;

2．修改自增約束的初始值

修改自增初始值使用ATLER關(guān)鍵字，針對一個表中的自增約束進行修改。

【范例18】

修改fruitshop.fruits表fid字段的自增約束初始值為7，代碼如下。

ALTER TABLE 'fruitshop'.'fruits' AUTO_INCREMENT=7;

查看fruitshop.fruits表的定義，代碼如下。

SHOW CREATE TABLE 'fruitshop'.'fruits'

上述代碼的執(zhí)行效果如下所示。

+ --------- + --------------------------+
    | Table      | Create Table                 |
    + --------- + --------------------------+
    | fruits      | CREATE TABLE 'fruits' (
      'fid' int(11) NOT NULL AUTO_INCREMENT,
      'fname' varchar(45) NOT NULL,
      'fprice' varchar(45) DEFAULT NULL,
      'ftime' varchar(45) DEFAULT NULL,
      'fwid' int(11) DEFAULT NULL,
      PRIMARY KEY ('fid'),
      KEY 'wid_idx' ('fwid'),
      CONSTRAINT 'fwid_wid' FOREIGN KEY ('fwid') REFERENCES 'workers' ('id') ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
    + --------- + --------------------------+
    1 rows

由上述執(zhí)行結(jié)果可以看到AUTO_INCREMENT=7語句，可見自增約束的初始值已經(jīng)被修改為7。由于fruitshop.fruits表已經(jīng)有數(shù)據(jù)了，因此在添加自增約束時，其初始值并不為1，而是比已有數(shù)據(jù)大的整數(shù)。

向fruitshop.fruits表中添加一條數(shù)據(jù)，僅添加fname字段和fprice字段的數(shù)據(jù)，代碼如下。

INSERT INTO 'fruitshop'.'fruits' ('fname', 'fprice') VALUES ('mango', '15.4');

上述代碼執(zhí)行后查看fruitshop.fruits表中的數(shù)據(jù)，如下所示。

+ ------- + ----------- + ---------- + --------- + -------- +
    | fid      | fname        | fprice      | ftime      | fwid      |
    + ------- + ----------- + ---------- + --------- + -------- +
    | 1        | orange       | 5            | 6.15       | 1          |
    | 2        | apple        | 2.5          | 6.15       | 2          |
    | 3        | banana       | 3.5          | 6.18       | 1          |
    | 4        | watermelon  | 0.7          | 6.15       | 2          |
    | 7        | mango        | 15.4         |             |            |
    + ------ + ----------- + ---------- + --------- + --------- +
    
    5 rows

從上述執(zhí)行效果可以看出，新增的數(shù)據(jù)自增約束列被添加了數(shù)據(jù)7，該字段將以7開始由系統(tǒng)自動添加數(shù)據(jù)。

3．修改自增約束的偏移量

字段的自增偏移量和自增值可以使用SHOW VARIABLES語句來查看，如查看服務(wù)器的自增約束代碼如下。

SHOW VARIABLES LIKE '%auto_increment%';

上述代碼的執(zhí)行結(jié)果如下所示。

+ ------------------------------- + ---------- +
    | Variable_name           | Value   |
    + ------------------------------- + ---------- +
    | auto_increment_increment | 1     |
    | auto_increment_offset     | 1     |
    + ------------------------------- + ---------- +
    2 rows

修改服務(wù)器auto_increment_offset和auto_increment_increment變量的值，可使用SET關(guān)鍵字，語法如下：

SET [SESSION]/[GLOBAL] auto_increment_increment=變量的值;
    SET [SESSION]/[GLOBAL] auto_increment_ offset=變量的值;

使用GLOBAL關(guān)鍵字修改變量的值，如果不退出session，重新連接，則不能生效，而且只能使用如下代碼查詢。

SHOW GLOBAL VARIABLES like '%auto_increment%';

不使用SESSION或GLOBAL也可以修改變量的值，但需要重啟MySQL才能生效。使用SESSION修改的數(shù)據(jù)將直接生效，可使用SHOW VARIABLES語句查看。

【范例19】

fruitshop.work表中有4條數(shù)據(jù)，先為該表添加自增約束，再設(shè)置其自增初始值為10，自增量和自增偏移量為2，為表添加兩條數(shù)據(jù)查看效果，步驟如下。

（1）為fruitshop.work表添加自增約束代碼省略。設(shè)置其自增初始值為10，自增量和自增偏移量為2，代碼如下。

ALTER TABLE 'fruitshop'.'work' AUTO_INCREMENT=10;
    SET SESSION auto_increment_increment=2;
    SET SESSION auto_increment_offset=2;

（2）為表添加兩條數(shù)據(jù)，代碼如下。

INSERT INTO 'fruitshop'.'work' ('wname', 'wsex') VALUES ('張明', '男');
    INSERT INTO 'fruitshop'.'work' ('wname', 'wsex') VALUES ('王麗', '女');

（3）查看fruitshop.work表中的數(shù)據(jù)，代碼省略，其效果如下所示。

+ ------- + ------- + -------- + -------- + ----------- +
    | wid      | wname    | wsex      | wage      | wemail       |
    + ------- + ------- + -------- + -------- + ----------- +
    | 1        | 梁思      | 女         | 22         | li@126.com  |
    | 2        | 何健      | 男         | 21         | jk@126.com  |
    | 3        | 趙龍      | 男         | 26         | zl@126.com  |
    | 4        | 李虎      | 男         | 25         | lh@126.com  |
    | 10       | 張明      | 男         |            |               |
    | 12       | 王麗      | 女         |            |               |
    + ------- + ------- + -------- + -------- + ---------- +
    6 rows

由上述執(zhí)行效果可以看出，系統(tǒng)自動為新添加的兩條語句填寫了wid字段的值，從10開始依次增加2。

4.7.5 刪除自增約束

刪除自增約束可以使用MySQL Workbench工具或使用SQL語句。在MySQL Workbench工具下的操作可參考唯一約束的刪除操作，這里不再詳細介紹。

使用SQL語句刪除自增約束通過CHANGE COLUMN語句來進行。如刪除fruitshop.work表中的自增約束，其刪除語句及其執(zhí)行結(jié)果如下所示。

ALTER TABLE 'fruitshop'.'work' CHANGE COLUMN 'wid' 'wid' INT(11) NOT NULL ;