一文讓你徹底弄懂MySQL自增列

MYSQL的自增列在實(shí)際生產(chǎn)中應(yīng)用的非常廣泛，相信各位所在的公司or團(tuán)隊(duì)，MYSQL開(kāi)發(fā)規(guī)范中一定會(huì)有要求盡量使用自增列去充當(dāng)表的主鍵，為什么DBA會(huì)有這樣的要求，各位在使用MYSQL自增列時(shí)遇到過(guò)哪些問(wèn)題？這些問(wèn)題是由什么原因造成的呢？本文由淺入深，帶領(lǐng)大家徹底弄懂MYSQL的自增機(jī)制。

創(chuàng)新互聯(lián)建站-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比商州網(wǎng)站開(kāi)發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式商州網(wǎng)站制作公司更省心,省錢(qián),快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋商州地區(qū)。費(fèi)用合理售后完善，10多年實(shí)體公司更值得信賴。

1.? 通過(guò)auto_increment關(guān)鍵字來(lái)指定自增的列，并指定自增列的初始值為1。

[root@localhost][test1]Create table t(id int auto_increment ,namevarchar(10),primary key(id))auto_increment=1;

QueryOK, 0 rows affected (0.63 sec)

2.? 自增列上必須有索引，將t表的主鍵索引刪除掉，會(huì)報(bào)錯(cuò)

[root@localhost][test1]alter table t drop primary key;

ERROR1075 (42000): Incorrect table definition; there can be only one auto column andit must be defined as a key

3.? 設(shè)定auto_increment_increment參數(shù)，可以調(diào)整自增步長(zhǎng)，該參數(shù)有session級(jí)跟global級(jí)，在分庫(kù)分表以及雙主or多主的模式下比較有用。

4.? 一個(gè)表上只能有一個(gè)自增列

5.? Mysql5.7及以下版本，innodb表的自增值保存在內(nèi)存中，重啟后表的自增值會(huì)設(shè)為max(id)+1，而myisam引擎的自增值是保存在文件中，重啟不會(huì)丟失。Mysql8.0開(kāi)始，innodb的自增id能持久化了，重啟mysql，自增ID不會(huì)丟。

首先：表中自增列的上限是根據(jù)自增列的字段類(lèi)型來(lái)定的。

若設(shè)定了自增id充當(dāng)主鍵，當(dāng)達(dá)到了自增id的上限值時(shí)，會(huì)發(fā)生什么樣的事情呢？還是以上面創(chuàng)建的 t表為例， 先回顧它的表結(jié)構(gòu)：

CREATETABLE `t` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(10) COLLATE utf8mb4_binDEFAULT NULL,

PRIMARY KEY (`id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

無(wú)符號(hào)的int類(lèi)型，上限是2147483647。這里我們將表的自增值設(shè)為2147483647，再插入兩行數(shù)據(jù)：

[root@localhost][test1]alter table t auto_increment=2147483647;

QueryOK, 0 rows affected (0.01 sec)

Records:0? Duplicates: 0? Warnings: 0

[root@localhost][test1]insert into t(name) values ('test');??????????

QueryOK, 1 row affected (0.01 sec)

[root@localhost][test1]insert into t(name) values ('test');

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

可以看到，第一個(gè)插入沒(méi)問(wèn)題，因?yàn)樽栽隽械闹禐?147483647，這是達(dá)到了上限，還沒(méi)有超過(guò)，第二行數(shù)據(jù)插入時(shí)，則報(bào)出主鍵重復(fù)，在達(dá)到上限后，無(wú)法再分配新的更大的自增值，也沒(méi)有從1開(kāi)始從頭分配，在這里表的auto_increment值會(huì)一直是2147483647。

對(duì)于寫(xiě)入量大，且經(jīng)常刪除數(shù)據(jù)的表，自增id設(shè)為int類(lèi)型還是偏小的，所以我們?yōu)榱吮苊獬霈F(xiàn)自增id漲滿的情況，這邊統(tǒng)一建議自增id的類(lèi)型設(shè)為unsigned bingint，這樣基本可以保障表的自增id是永遠(yuǎn)夠用的。

這里內(nèi)容比較多，innodb是索引組織表，所以涉及到索引的知識(shí)，但這不是本文的重點(diǎn)，我們快速回顧索引知識(shí)：

1.? Innodb索引分為主鍵跟輔助索引，主鍵即全表，輔助索引葉子節(jié)點(diǎn)保存主鍵的值，而主鍵的葉子節(jié)點(diǎn)保存數(shù)據(jù)行，中間節(jié)點(diǎn)存著葉子節(jié)點(diǎn)的路由值。

2.? Innodb存儲(chǔ)數(shù)據(jù)（索引）的單位是頁(yè)，這里默認(rèn)是16K，這也意味著，數(shù)據(jù)本身越小，一個(gè)頁(yè)中能存數(shù)據(jù)的量越多，而檢索效率不僅僅由索引的層數(shù)來(lái)決定，更是由一次能夠緩存的數(shù)據(jù)量來(lái)定，也就是說(shuō)數(shù)據(jù)本身越小，則一次IO能夠提取到緩沖區(qū)的數(shù)據(jù)越多（OS每次IO的量是固定的4K），查詢的效率越好。

其實(shí)能夠理解索引的結(jié)構(gòu)及索引寫(xiě)入插入、更新的原理，則自然就明白為何建議使用自增id。這里我直接列出使用自增id 當(dāng)主鍵的好處吧：

1.? 順序?qū)懭耄苊饬巳~的分裂，數(shù)據(jù)寫(xiě)入效率好

2.? 縮小了表的體積，特別是相比于UUID當(dāng)主鍵，甚至組合字段當(dāng)主鍵時(shí)，效果更明顯

3.? 查詢效率好，原因就是我上面說(shuō)到索引知識(shí)的第二點(diǎn)。

4.? 某些情況下，我們可以利用自增id來(lái)統(tǒng)計(jì)大表的大致行數(shù)。

5.? 在數(shù)據(jù)歸檔or垃圾數(shù)據(jù)清理時(shí)，也可方便的利用這個(gè)id去操作，效率高。

容易出現(xiàn)不連續(xù)的id

有的同志會(huì)發(fā)現(xiàn)，自己的表中id值存在空洞，如類(lèi)似于1、2、3、8、9、10這樣，有的適合有想依賴于自增id的連續(xù)性來(lái)實(shí)現(xiàn)業(yè)務(wù)邏輯，所以會(huì)想方設(shè)法去修改id讓其變的連續(xù)，其實(shí)，這是沒(méi)有必要的，這一塊的業(yè)務(wù)邏輯交由MySQL實(shí)現(xiàn)是很不理智的，表的記錄小還好，要是表的數(shù)據(jù)量很大，修改起來(lái)就糟糕了。那么，為什么自增id會(huì)容易出現(xiàn)空洞呢？

自增id的修改機(jī)制如下：

在MySQL里面，如果字段id被定義為AUTO_INCREMENT，在插入一行數(shù)據(jù)的時(shí)候，自增值的行為如下：

1. 如果插入數(shù)據(jù)時(shí)id字段指定為0、null 或未指定值，那么就把這個(gè)表當(dāng)前的

AUTO_INCREMENT值填到自增字段；

2. 如果插入數(shù)據(jù)時(shí)id字段指定了具體的值，就直接使用語(yǔ)句里指定的值。

根據(jù)要插入的值和當(dāng)前自增值的大小關(guān)系，自增值的變更結(jié)果也會(huì)有所不同。假設(shè)，某次要插入的值是X，當(dāng)前的自增值是Y。

1. 如果XY，那么這個(gè)表的自增值不變；

2. 如果X≥Y，就需要把當(dāng)前自增值修改為 新的自增值 。

新的自增值生成算法是：從auto_increment_offset開(kāi)始，以auto_increment_increment為步長(zhǎng)，持續(xù)疊加，直到找到第一個(gè)大于X的值，作為新的自增值。

Insert、update、delete操作會(huì)讓id不連續(xù)。

Delete、update：刪除中間數(shù)據(jù)，會(huì)造成空動(dòng)，而修改自增id值，也會(huì)造成空洞（這個(gè)很少）。

Insert：插入報(bào)錯(cuò)（唯一鍵沖突與事務(wù)回滾），會(huì)造成空洞，因?yàn)檫@時(shí)候自增id已經(jīng)分配出去了，新的自增值已經(jīng)生成，如下面例子：

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

+----+------+

4 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 5 |

+----------------+

1 row in set (0.00 sec)

[root@localhost][test1] begin;

Query OK, 0 rows affected (0.00 sec)

[root@localhost][test1] insert intot(name) values('aaa');

Query OK, 1 row affected (0.00 sec)

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

|? 5| aaa? |

+----+------+

5 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 6 |

+----------------+

1 row in set (0.00 sec)

[root@localhost][test1] rollback;

Query OK, 0 rows affected (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 6 |

+----------------+

1 row in set (0.01 sec)

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

+----+------+

4 rows in set (0.00 sec)

可以看到，雖然事務(wù)回滾了，但自增id已經(jīng)回不到從前啦，唯一鍵沖突也是這樣的，這里就不做測(cè)試了。

在批量插入時(shí)（insert select等），也存在空洞的問(wèn)題。看下面實(shí)驗(yàn)：

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

+----+------+

4 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 5 |

+----------------+

1 row in set (0.00 sec)

[root@localhost][test1] insert intot(name) select name from t;??????????????????????

Query OK, 4 rows affected (0.04 sec)

Records: 4?Duplicates: 0? Warnings: 0

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

|? 5| aaa? |

|? 6| aaa? |

|? 7| aaa? |

|? 8| aaa? |

+----+------+

8 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|???????????? 12 |

+----------------+

1 row in set (0.00 sec)

可以看到，批量插入，導(dǎo)致下一個(gè)id值不為9了，再插入數(shù)據(jù)，即產(chǎn)生了空洞，這里是由mysql申請(qǐng)自增值的機(jī)制所造成的，MySQL在批量插入時(shí)，若一個(gè)值申請(qǐng)一個(gè)id，效率太慢，影響了批量插入的速度，故mysql采用下面的策略批量申請(qǐng)id。

1.? 語(yǔ)句執(zhí)行過(guò)程中，第一次申請(qǐng)自增id，會(huì)分配1個(gè)；

2.? 1個(gè)用完以后，這個(gè)語(yǔ)句第二次申請(qǐng)自增id，會(huì)分配2個(gè)；

3.? 2個(gè)用完以后，還是這個(gè)語(yǔ)句，第三次申請(qǐng)自增id，會(huì)分配4個(gè)；

4.? 依此類(lèi)推，同一個(gè)語(yǔ)句去申請(qǐng)自增id，每次申請(qǐng)到的自增id個(gè)數(shù)都是上一次的兩倍。

在對(duì)自增列進(jìn)行操作時(shí)，存在著自增鎖，mysql的innodb_autoinc_lock_mode參數(shù)控制著自增鎖的上鎖機(jī)制。該參數(shù)有0、1、2三種模式：

0：語(yǔ)句執(zhí)行結(jié)束后釋放自增鎖，MySQL5.0時(shí)采用這種模式，并發(fā)度較低。

1：mysql的默認(rèn)設(shè)置。普通的insert語(yǔ)句申請(qǐng)后立馬釋放，insert select、replace insert、load data等批量插入語(yǔ)句要等語(yǔ)句執(zhí)行結(jié)束后才釋放，并發(fā)讀得到提升

2：所有的語(yǔ)句都是申請(qǐng)后立馬釋放，并發(fā)度大大提升！但是在binlog為statement格式時(shí)，主從數(shù)據(jù)會(huì)發(fā)生不一致。這一塊網(wǎng)上有很多介紹，我不做介紹了。

在徹底了解了MYSQL的自增機(jī)制以后，在實(shí)際生產(chǎn)中就能靈活避坑，這里建議不要用自增id值去當(dāng)表的行數(shù)，當(dāng)需要對(duì)大表準(zhǔn)確統(tǒng)計(jì)行數(shù)時(shí)，可以去count(*)從庫(kù)，如果業(yè)務(wù)很依賴大表的準(zhǔn)確行數(shù)，直接弄個(gè)中間表來(lái)統(tǒng)計(jì)，或者考慮要不要用mysql的innodb來(lái)存儲(chǔ)數(shù)據(jù)，這個(gè)是需要自己去權(quán)衡。另外對(duì)于要求很高的寫(xiě)入性能，但寫(xiě)入量又比較大的業(yè)務(wù)，自增id的使用依然存在熱點(diǎn)寫(xiě)入的問(wèn)題，存在性能瓶頸，這時(shí)候可通過(guò)分庫(kù)分表來(lái)解決。

清空MySQL表，如何使ID重新從1自增？？？

清空MySQL表，使ID重新從1自增的步驟如下：

我們需要準(zhǔn)備的材料分別是：電腦、Mysql查詢器。

1、首先，打開(kāi)Mysql查詢器，連接上相應(yīng)的mysql連接。

2、鼠標(biāo)右擊需要清空自增ID的表，選擇“設(shè)計(jì)表”，再將選項(xiàng)卡切換到“設(shè)置”欄，會(huì)發(fā)現(xiàn)雖然清空了表，但是自動(dòng)遞增的數(shù)值仍然沒(méi)有變回1。

3、在自動(dòng)遞增欄，將數(shù)值更改為數(shù)字1，并點(diǎn)擊“保存”按鈕。

4、此時(shí)會(huì)發(fā)現(xiàn)，再新增數(shù)據(jù)時(shí)，ID自動(dòng)從1開(kāi)始遞增了。

在一個(gè)數(shù)據(jù)表中，怎么查詢到表中數(shù)據(jù)自增id最大的那條數(shù)據(jù)呢？

查詢id最大的數(shù)據(jù)有這幾種方法（以mysql數(shù)據(jù)庫(kù)為例）：

1），利用mysql自帶的max函數(shù)取得最大值

2），先按照id降序排列，然后利用limit函數(shù)取最大值：

擴(kuò)展資料：

數(shù)據(jù)庫(kù)是存儲(chǔ)電子文件的處所，可以通過(guò)數(shù)據(jù)庫(kù)對(duì)文件中的數(shù)據(jù)運(yùn)行新增、截取、更新、刪除等操作。常用的關(guān)系型數(shù)據(jù)庫(kù)有mysql，oracle，SQL Server，dBASE，MariaDB等，非關(guān)系型數(shù)據(jù)庫(kù)最常見(jiàn)的就是MongoDB。

所謂關(guān)系型數(shù)據(jù)庫(kù)就是指由多張能互相聯(lián)接的二維行列表格組成的數(shù)據(jù)庫(kù)。

如何獲得mysql數(shù)據(jù)庫(kù)自增長(zhǎng)主鍵的值？

this.employee_id = employee_id;}} 其它幾個(gè)屬性的getter和setter省略，這里我們要用到ejb3-persistence.jar，JPA的注解類(lèi)就在這個(gè)包中，下面詳細(xì)說(shuō)明上面使用到的注解。 @Entity：通過(guò)@Entity注解將一個(gè)類(lèi)聲明為一個(gè)實(shí)體bean @Table：通過(guò) @Table注解可以為實(shí)體bean映射指定表，name屬性表示實(shí)體所對(duì)應(yīng)表的名稱，如果沒(méi)有定義 @Table，那么系統(tǒng)自動(dòng)使用默認(rèn)值：實(shí)體的類(lèi)名（不帶包名） @Id：用于標(biāo)記屬性的主鍵 @Column：表示持久化屬性所映射表中的字段，如果屬性名與表中的字段名相同，則可以省略@Column注解，另外有兩種方式標(biāo)記，一是放在屬性前，另一種是放在getter方法前，例如： @Column(name = EMPLOYEE_NAME) private String employee_name; 或者 @Column(name = EMPLOYEE_NAME) public String getEmployee_name() { return employee_name; } 這兩種方式都是正解的，根據(jù)個(gè)人喜好來(lái)選擇。大象偏向于第二種，并且喜歡將屬性名與字段名設(shè)成一樣的，這樣可以省掉@Column注解，使代碼更簡(jiǎn)潔。 @TableGenerator：表生成器，將當(dāng)前主鍵的值單獨(dú)保存到一個(gè)數(shù)據(jù)庫(kù)表中，主鍵的值每次都是從指定的表中查詢來(lái)獲得，這種生成主鍵的方式是很常用的。這種方法生成主鍵的策略可以適用于任何數(shù)據(jù)庫(kù)，不必?fù)?dān)心不同數(shù)據(jù)庫(kù)不兼容造成的問(wèn)題。大象推薦這種方式管理主鍵，很方便，集中式管理表的主鍵，而且更換數(shù)據(jù)庫(kù)不會(huì)造成很大的問(wèn)題。各屬性含義如下： name：表示該表主鍵生成策略的名稱，這個(gè)名字可以自定義，它被引用在@GeneratedValue中設(shè)置的generator值中 table：表示表生成策略所持久化的表名，說(shuō)簡(jiǎn)單點(diǎn)就是一個(gè)管理其它表主鍵的表，本例中，這個(gè)表名為GENERATOR_TABLE pkColumnName：表生成器中的列名，用來(lái)存放其它表的主鍵鍵名，這個(gè)列名是與表中的字段對(duì)應(yīng)的 pkColumnValue：實(shí)體表所對(duì)應(yīng)到生成器表中的主鍵名，這個(gè)鍵名是可以自定義滴 valueColumnName：表生成器中的列名，實(shí)體表主鍵的下一個(gè)值，假設(shè)EMPLOYEE表中的EMPLOYEE_ID最大為2，那么此時(shí)，生成器表中與實(shí)體表主鍵對(duì)應(yīng)的鍵名值則為3 allocationSize：表示每次主鍵值增加的大小，例如設(shè)置成1，則表示每次創(chuàng)建新記錄后自動(dòng)加1，默認(rèn)為50@GeneratedValue：定義主鍵生成策略，這里因?yàn)槭褂玫氖荰ableGenerator，所以，主鍵的生成策略為GenerationType.TABLE，生成主鍵策略的名稱則為前面定義的”tab-store”。 這里大象想說(shuō)下，網(wǎng)上有很多文章寫(xiě)的是strategy = GenerationType.AUTO或是strategy = GenerationType.SEQUENCE，采用SEQUENCE序列是因?yàn)镺racle數(shù)據(jù)中不支持identity自動(dòng)增長(zhǎng)，要想使用它，還得在數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)序列，如果要更換數(shù)據(jù)庫(kù)，那將是一個(gè)非常麻煩的事情。SEQUENCE生成方式我們暫且不談，這里說(shuō)下采用AUTO和IDENTITY的生成方式，本例采用的是SQL Server 2000作為數(shù)據(jù)庫(kù)，所以如果想使用AUTO或是IDENTITY生成策略，則一定要對(duì)主鍵加上identity標(biāo)識(shí)，如identity(1,1)。不過(guò)對(duì)于AUTO來(lái)說(shuō)，是根據(jù)不同的數(shù)據(jù)庫(kù)選擇最合適的自增主鍵生成策略。如果使用MySQL，則主鍵要定義AUTO_INCREMENT，如果是Oracle，則要?jiǎng)?chuàng)建Sequence來(lái)實(shí)現(xiàn)自增。不管采用何種生成策略，增、刪、改這些方法中一定要加入事務(wù)，否則數(shù)據(jù)是不會(huì)添加到數(shù)據(jù)庫(kù)中滴~~~這是大象反復(fù)測(cè)試過(guò)的結(jié)果！

當(dāng)前標(biāo)題：mysql自增怎么查詢 mysql 查詢自增主鍵
網(wǎng)站路徑：http://www.chinadenli.net/article36/dopgepg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供建站公司、自適應(yīng)網(wǎng)站、定制開(kāi)發(fā)、虛擬主機(jī)、網(wǎng)站內(nèi)鏈、App設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)