MySQL數(shù)據(jù)庫為列提供了一種自增屬性，當(dāng)列被定義為自增時(shí)。Insert語句對(duì)該列即使不提供值，MySQL也會(huì)自動(dòng)為該列生成遞增的唯一標(biāo)識(shí)，因此這個(gè)特性廣泛用于主鍵的自動(dòng)生成。

一、自增列的用法

自增列具有自動(dòng)生成序列值，整型，單調(diào)遞增這些特點(diǎn)，非常適合作為索引組織表的主鍵，新插入的數(shù)據(jù)會(huì)附加在已有的數(shù)據(jù)后面，不會(huì)出現(xiàn)頁分 裂現(xiàn)象，且整型的主鍵查找效率非常高。

1.1 基本用法

在創(chuàng)建表時(shí)，只需在某個(gè)整型列（tinyint,smallint, mediumint, int, bigint）上指定auto_increment，即可打開自增屬性。

一張表中只能指定一個(gè)自增列，且必須建立索引，示例中 id列沒有指定為索引列，建表報(bào)錯(cuò)（must be defined as a key）：

create table t2(
id int auto_increment,
name varchar(32));

指定自增列為主鍵，創(chuàng)建成功：

create table t (
id int primary key auto_increment,
name varchar(32));

插入數(shù)據(jù)時(shí)，即使insert語句未包含自增列，MySQL也會(huì)自動(dòng)為該列生成值：

insert into t(name) values('Vincent'); -- 未指定id列
select * from t;

如果指定了0或null，同樣也可以生成自增值：

insert into t values (0, 'Victor'), (null, 'Grace');  -- 指定0和null
select * from t;

注意：由于0會(huì)觸發(fā)自增，如果ID列本來保存的數(shù)據(jù)就包含0，那么在數(shù)據(jù)導(dǎo)出和導(dǎo)入過程中，數(shù)字0可能會(huì)因此觸發(fā)自增而被修改，導(dǎo)致數(shù)據(jù)不一致。 這種情況可以打開sql_mode參數(shù)中的no_auto_value_on_zero選項(xiàng)（可以在會(huì)話和全局修改），打開該選項(xiàng)后，只有null可以觸發(fā)自增，0不再觸發(fā)。

set sql_mode=concat(@@session.sql_mode, ',no_auto_value_on_zero');
insert into t values(0, 'Tom');
select * from t;

1.2 自增列特性

自增列除了讓其自動(dòng)生成值之外，也可以顯式賦值，使用中注意以下幾點(diǎn)：

• 顯式賦值可能導(dǎo)致大量值被浪費(fèi)
• 事務(wù)回滾不會(huì)回滾已使用的自增值
• truncate table清除數(shù)據(jù)的同時(shí)也會(huì)讓自增值初始化
• alter table … auto_increment=1 可以讓自增值恢復(fù)到最大可用值，消除間隙（并不會(huì)設(shè)定成1）

自增列可以顯式賦值，但如果指定的值超過目前auto_increment的最大值，則MySQL會(huì)從你指定的值之后開始繼續(xù)遞增，即使前面有可用的值也不再使用，示例中顯式指定id列為1000，那么下一條數(shù)據(jù)會(huì)從1001開始自增。

insert into t values(1000, 'Jerry');
insert into t values(null, 'Spike');

事務(wù)中如果使用了自增值，即使回滾，自增值也不會(huì)恢復(fù)，示例中的事務(wù)消耗了2個(gè)自增值(1002, 1003)，然后事務(wù)回滾了，但是下一條insert語句自增值是從1004開始的：

begin;
insert into t values(null, 'Spike'); 
insert into t values(null, 'Spike');  
rollback;  -- 事務(wù)回滾
insert into t values(null, 'Tyke'); -- 自增值不會(huì)回滾
select * from t;

示例刪除了id為1000及以上的數(shù)據(jù)后，使用alter table … auto_increment=1使自增值恢復(fù)到當(dāng)前數(shù)據(jù)的最大值：

delete from t where id>=1000;
alter table t auto_increment=1;
insert into t values(null, 'Jerry');
select * from t;

1.3 通過last_insert_id()獲取自增值

MySQL提供了函數(shù)last_insert_id()，用于獲取上一個(gè)成功執(zhí)行的insert語句所生成的第一個(gè)自增值：

truncate table t;
insert into t values(null,'Vincent');
select last_insert_id();

單一insert語句如果插入多行，獲取的是語句中第一個(gè)產(chǎn)生的自增值，而不是最后一個(gè)，下面insert語句插入了2條記錄，但last_insert_id()返回的是2而不是3（雖然表中id增長(zhǎng)到3）：

insert into t values(null,'Victor'),(null,'Grace');
select last_insert_id();
select * from t;

如果在事務(wù)中手動(dòng)回滾，last_insert_id()的值也是不會(huì)回滾的，其代表的是曾經(jīng)成功插入的自增值，而不判斷事務(wù)是否最終提交（有一定誤導(dǎo)性，不能用作判斷實(shí)際插入的值）：

begin;
insert into t values(null,'Vincent');
insert into t values(null,'Vincent');  -- 成功插入，last_insert_id()為5
rollback;  -- 事務(wù)回滾
select last_insert_id();  -- last_insert_id()依然是5，但數(shù)據(jù)實(shí)際未插入

last_insert_id(expr)還有個(gè)可選的參數(shù)，如果提供參數(shù)expr，則會(huì)返回該值，并將expr記錄為下一個(gè)last_insert_id()的返回值：

select last_insert_id(100);
select last_insert_id();

二、自增計(jì)數(shù)器

在MySQL8.0之前，對(duì)于auto_increment的值會(huì)在內(nèi)存中維護(hù)一個(gè)計(jì)數(shù)器（不保存在磁盤上），在服務(wù)器啟動(dòng)時(shí)會(huì)對(duì)每張表執(zhí)行類似select max(auto_column) from t for update;語句獲取當(dāng)前表中的最大自增值，用于初始化這個(gè)計(jì)數(shù)器。

MySQL8.0以后，這個(gè)計(jì)數(shù)器的值會(huì)在每次變更時(shí)寫入重做日志和數(shù)據(jù)字典（保存到磁盤上）。服務(wù)器重啟時(shí)直接讀取數(shù)據(jù)字典即可，不必再通過查詢表初始化。

三、自增值生成模式

上面的都是單線程下自增值的生成示例，但在并發(fā)時(shí)，多個(gè)事務(wù)可能會(huì)同時(shí)向表中插入數(shù)據(jù)，事務(wù)之間存在爭(zhēng)用。MySQL為并發(fā)場(chǎng)景下自增值的生成提供了3種不同的模式。3種模式由innodb_autoinc_lock_mode控制（只讀變量，修改需要重啟），對(duì)應(yīng)的值分別為0, 1, 2：

• 0, 傳統(tǒng)模式（Traditional Lock Mode）
• 1, 連續(xù)模式（Consecutive Lock Mode）
• 2, 交錯(cuò)模式（Interleaved Lock Mode）

在解釋3種模式的區(qū)別前，先了解一下insert語句的分類，insert語句可以分為以下3類：

• 簡(jiǎn)單插入（Simple Inserts），如單記錄insert，或者多記錄insert，在解析SQL時(shí)就可以確定要加載的記錄數(shù)（即要生成自增值數(shù)量）
• 批量插入（Bulk Inserts），如insert … select, load data等，在解析SQL時(shí)不確定需要加載的記錄數(shù)
• 混合插入（Mixed-Mode Inserts），在多記錄簡(jiǎn)單插入中，為自增列顯式指定了部分值，如 insert into … values (null,‘a’), (5, ‘b’), (null, ‘c’)

3.1 傳統(tǒng)模式

在傳統(tǒng)模式下（innodb_autoinc_lock_mode=0），所有類型的insert都會(huì)使用表級(jí)X鎖，并且持有到insert語句結(jié)束，這意味著同一時(shí)間只有1條insert語句可以執(zhí)行，但可以保證單條insert語句產(chǎn)生的自增值是連續(xù)的。

在基于語句的主從復(fù)制（Statement-Based Replication）模式下insert語句在主從可以生成相同的值。傳統(tǒng)模式只是為了向前兼容，現(xiàn)在已經(jīng)不會(huì)使用了。

3.2 連續(xù)模式

連續(xù)模式（innodb_autoinc_lock_mode=1）是對(duì)傳統(tǒng)模式的優(yōu)化，對(duì)于批量插入這種不確定需要需要多少自增值的insert，會(huì)和傳統(tǒng)模式一樣，使用表級(jí)鎖直至insert語句執(zhí)行完成。

而對(duì)于可以事先確定插入記錄數(shù)的簡(jiǎn)單插入，MySQL會(huì)用mutex（閂，更輕量級(jí)的鎖）僅在預(yù)先分配自增值時(shí)鎖定，在insert語句執(zhí)行完成前就已經(jīng)釋放了。連續(xù)模式也可以保證基于語句的復(fù)制主從可以生成相同的自增值，但性能比傳統(tǒng)模式更好。

對(duì)于混合插入類型（多行簡(jiǎn)單插入中，部分行顯式指定自增值，部分行未指定），連續(xù)模式下會(huì)預(yù)先生成比要插入行更多的自增值，然后以連續(xù)方式分配給需要自增的行，多余的值就丟棄了。

MySQL8.0以前的版本默認(rèn)為連續(xù)模式。

3.3 交錯(cuò)模式

交錯(cuò)模式下（innodb_autoinc_lock_mode=2），不使用表鎖，任何并發(fā)insert都可以同時(shí)執(zhí)行，這意味著多條insert語句生成的自增值是可能是交錯(cuò)的，單條insert語句無法保證生成連續(xù)的自增值，但這種模式并發(fā)性能是最好的。

因?yàn)槿狈α吮礞i控制，多條insert并發(fā)插入，在主從執(zhí)行時(shí)無法保證自增值完全相同，此模式對(duì)基于語句的復(fù)制（應(yīng)該沒人用了吧？）是不安全的，建議配合基于行的復(fù)制（Row-Based Replication）使用（MySQL8.0默認(rèn)）。

MySQL8.0默認(rèn)為交錯(cuò)模式。

四、調(diào)整自增偏移

自增列的默認(rèn)初始值為1，步長(zhǎng)為1。但在多主復(fù)制、組復(fù)制這類可以多點(diǎn)寫入的環(huán)境，可能會(huì)產(chǎn)生沖突。

為了保證不出現(xiàn)沖突，可以設(shè)置auto_increment_offset和auto_increment_increment來修改自增的初始值和步長(zhǎng)，使各個(gè)寫入點(diǎn)產(chǎn)生的自增值不重疊（可以在會(huì)話和全局級(jí)別修改）。

例如現(xiàn)在有一個(gè)雙主環(huán)境，可以在一臺(tái)主機(jī)上配置初始值為1（默認(rèn)），步長(zhǎng)為2，這樣生成的自增值都是單數(shù)：

set auto_increment_increment=2;

而在另一臺(tái)主機(jī)上配置初始值為2，步長(zhǎng)為2，生成自增值都是雙數(shù)，這樣可以避免并發(fā)寫入時(shí)發(fā)生沖突：

set auto_increment_offset=2;
set auto_increment_increment=2;

五、監(jiān)控自增值的使用比例

由于數(shù)據(jù)類型最大值的限制，自增并不是沒有上限的。當(dāng)?shù)竭_(dá)上限時(shí)數(shù)據(jù)無法繼續(xù)插入，導(dǎo)致業(yè)務(wù)中斷，因此DBA需要監(jiān)控自增值的使用情況，在達(dá)到上限之前及時(shí)采取擴(kuò)容措施。

示例：這里又新建2張表，設(shè)置自增列類型為tinyint（8位有符號(hào)整數(shù)，范圍為-128~127），然后將自增值分別設(shè)置為64和127

create table t2(id tinyint primary key auto_increment);
create table t3(id tinyint primary key auto_increment);

alter table t2 auto_increment=64;
alter table t3 auto_increment=127;

使用下面的SQL即可查詢test數(shù)據(jù)庫下所有表的自增列使用比例（可根據(jù)情況調(diào)整，去除t.table_schema='test’可以查詢所有庫）：

select
t.table_schema,
t.table_name,
t.auto_increment,
c.column_type,
concat(round((t.auto_increment /
(case data_type
when 'tinyint'then if(column_type like '%unsigned', 255, 127)
when 'smallint' then if(column_type like '%unsigned', 65535, 32767)
when 'mediumint' then if(column_type like '%unsigned', 16777215, 8388607)
when 'int'then if(column_type like '%unsigned', 4294967295, 2147483647)
when 'bigint' then if(column_type like '%unsigned',18446744073709551615,9223372036854775807)
end))*100,2),'%') used_percentage
from information_schema.tables t
join information_schema.columns c on t.table_schema = c.table_schema and t.table_name = c.table_name
where t.auto_increment is not null and c.extra='auto_increment' and t.table_schema='test';

從結(jié)果可以看到t3表的自增列已經(jīng)使用100%，再插入新的數(shù)據(jù)就會(huì)報(bào)錯(cuò)了。

insert into t3 values(null);

到此這篇關(guān)于MySQL自增列解析(Auto_increment)的文章就介紹到這了,更多相關(guān)MySQL 自增列 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評(píng)論