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

MYSQL的自增列在實際生產中應用的非常廣泛，相信各位所在的公司or團隊，MYSQL開發規范中一定會有要求盡量使用自增列去充當表的主鍵，為什么DBA會有這樣的要求，各位在使用MYSQL自增列時遇到過哪些問題？這些問題是由什么原因造成的呢？本文由淺入深，帶領大家徹底弄懂MYSQL的自增機制。

創新互聯建站-專業網站定制、快速模板網站建設、高性價比商州網站開發、企業建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式商州網站制作公司更省心,省錢,快速模板網站建設找我們，業務覆蓋商州地區。費用合理售后完善，10多年實體公司更值得信賴。

1.? 通過auto_increment關鍵字來指定自增的列，并指定自增列的初始值為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表的主鍵索引刪除掉，會報錯

[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.? 設定auto_increment_increment參數，可以調整自增步長，該參數有session級跟global級，在分庫分表以及雙主or多主的模式下比較有用。

4.? 一個表上只能有一個自增列

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

首先：表中自增列的上限是根據自增列的字段類型來定的。

若設定了自增id充當主鍵，當達到了自增id的上限值時，會發生什么樣的事情呢？還是以上面創建的 t表為例， 先回顧它的表結構：

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

無符號的int類型，上限是2147483647。這里我們將表的自增值設為2147483647，再插入兩行數據：

[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'

可以看到，第一個插入沒問題，因為自增列的值為2147483647，這是達到了上限，還沒有超過，第二行數據插入時，則報出主鍵重復，在達到上限后，無法再分配新的更大的自增值，也沒有從1開始從頭分配，在這里表的auto_increment值會一直是2147483647。

對于寫入量大，且經常刪除數據的表，自增id設為int類型還是偏小的，所以我們為了避免出現自增id漲滿的情況，這邊統一建議自增id的類型設為unsigned bingint，這樣基本可以保障表的自增id是永遠夠用的。

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

1.? Innodb索引分為主鍵跟輔助索引，主鍵即全表，輔助索引葉子節點保存主鍵的值，而主鍵的葉子節點保存數據行，中間節點存著葉子節點的路由值。

2.? Innodb存儲數據（索引）的單位是頁，這里默認是16K，這也意味著，數據本身越小，一個頁中能存數據的量越多，而檢索效率不僅僅由索引的層數來決定，更是由一次能夠緩存的數據量來定，也就是說數據本身越小，則一次IO能夠提取到緩沖區的數據越多（OS每次IO的量是固定的4K），查詢的效率越好。

其實能夠理解索引的結構及索引寫入插入、更新的原理，則自然就明白為何建議使用自增id。這里我直接列出使用自增id 當主鍵的好處吧：

1.? 順序寫入，避免了葉的分裂，數據寫入效率好

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

3.? 查詢效率好，原因就是我上面說到索引知識的第二點。

4.? 某些情況下，我們可以利用自增id來統計大表的大致行數。

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

容易出現不連續的id

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

自增id的修改機制如下：

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

1. 如果插入數據時id字段指定為0、null 或未指定值，那么就把這個表當前的

AUTO_INCREMENT值填到自增字段；

2. 如果插入數據時id字段指定了具體的值，就直接使用語句里指定的值。

根據要插入的值和當前自增值的大小關系，自增值的變更結果也會有所不同。假設，某次要插入的值是X，當前的自增值是Y。

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

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

新的自增值生成算法是：從auto_increment_offset開始，以auto_increment_increment為步長，持續疊加，直到找到第一個大于X的值，作為新的自增值。

Insert、update、delete操作會讓id不連續。

Delete、update：刪除中間數據，會造成空動，而修改自增id值，也會造成空洞（這個很少）。

Insert：插入報錯（唯一鍵沖突與事務回滾），會造成空洞，因為這時候自增id已經分配出去了，新的自增值已經生成，如下面例子：

[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)

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

在批量插入時（insert select等），也存在空洞的問題?？聪旅鎸嶒灒?/p>

[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)

可以看到，批量插入，導致下一個id值不為9了，再插入數據，即產生了空洞，這里是由mysql申請自增值的機制所造成的，MySQL在批量插入時，若一個值申請一個id，效率太慢，影響了批量插入的速度，故mysql采用下面的策略批量申請id。

1.? 語句執行過程中，第一次申請自增id，會分配1個；

2.? 1個用完以后，這個語句第二次申請自增id，會分配2個；

3.? 2個用完以后，還是這個語句，第三次申請自增id，會分配4個；

4.? 依此類推，同一個語句去申請自增id，每次申請到的自增id個數都是上一次的兩倍。

在對自增列進行操作時，存在著自增鎖，mysql的innodb_autoinc_lock_mode參數控制著自增鎖的上鎖機制。該參數有0、1、2三種模式：

0：語句執行結束后釋放自增鎖，MySQL5.0時采用這種模式，并發度較低。

1：mysql的默認設置。普通的insert語句申請后立馬釋放，insert select、replace insert、load data等批量插入語句要等語句執行結束后才釋放，并發讀得到提升

2：所有的語句都是申請后立馬釋放，并發度大大提升！但是在binlog為statement格式時，主從數據會發生不一致。這一塊網上有很多介紹，我不做介紹了。

在徹底了解了MYSQL的自增機制以后，在實際生產中就能靈活避坑，這里建議不要用自增id值去當表的行數，當需要對大表準確統計行數時，可以去count(*)從庫，如果業務很依賴大表的準確行數，直接弄個中間表來統計，或者考慮要不要用mysql的innodb來存儲數據，這個是需要自己去權衡。另外對于要求很高的寫入性能，但寫入量又比較大的業務，自增id的使用依然存在熱點寫入的問題，存在性能瓶頸，這時候可通過分庫分表來解決。

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

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

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

1、首先，打開Mysql查詢器，連接上相應的mysql連接。

2、鼠標右擊需要清空自增ID的表，選擇“設計表”，再將選項卡切換到“設置”欄，會發現雖然清空了表，但是自動遞增的數值仍然沒有變回1。

3、在自動遞增欄，將數值更改為數字1，并點擊“保存”按鈕。

4、此時會發現，再新增數據時，ID自動從1開始遞增了。

在一個數據表中，怎么查詢到表中數據自增id最大的那條數據呢？

查詢id最大的數據有這幾種方法（以mysql數據庫為例）：

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

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

擴展資料：

數據庫是存儲電子文件的處所，可以通過數據庫對文件中的數據運行新增、截取、更新、刪除等操作。常用的關系型數據庫有mysql，oracle，SQL Server，dBASE，MariaDB等，非關系型數據庫最常見的就是MongoDB。

所謂關系型數據庫就是指由多張能互相聯接的二維行列表格組成的數據庫。

如何獲得mysql數據庫自增長主鍵的值？

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

當前標題：mysql自增怎么查詢 mysql 查詢自增主鍵
網站路徑：http://vcdvsql.cn/article36/dopgepg.html

成都網站建設公司_創新互聯，為您提供建站公司、自適應網站、定制開發、虛擬主機、網站內鏈、App設計

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯