建站
咨詢
售后
建站咨詢
新聞中心
mysql中自增列初始值是什么意思
就是說從5開始增長呀，默認是一次增加一
成都創(chuàng)新互聯(lián)始終堅持【策劃先行，效果至上】的經(jīng)營理念，通過多達十多年累計超上千家客戶的網(wǎng)站建設總結(jié)了一套系統(tǒng)有效的全網(wǎng)推廣解決方案，現(xiàn)已廣泛運用于各行各業(yè)的客戶，其中包括：成都軟裝設計等企業(yè)，備受客戶贊譽。
比如說有一個用戶表，你想保留一些小的id，以后備用，就可以使用AUTO_INCREMENT=1000來設置，從1000開始增長(不指定id值的情況下)，以后如果說有需要，可以手動去插入id小于1000的
一文讓你徹底弄懂MySQL自增列
MYSQL的自增列在實際生產(chǎn)中應用的非常廣泛，相信各位所在的公司or團隊，MYSQL開發(fā)規(guī)范中一定會有要求盡量使用自增列去充當表的主鍵，為什么DBA會有這樣的要求，各位在使用MYSQL自增列時遇到過哪些問題？這些問題是由什么原因造成的呢？本文由淺入深，帶領大家徹底弄懂MYSQL的自增機制。
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參數(shù)，可以調(diào)整自增步長，該參數(shù)有session級跟global級，在分庫分表以及雙主or多主的模式下比較有用。
4.? 一個表上只能有一個自增列
5.? Mysql5.7及以下版本，innodb表的自增值保存在內(nèi)存中，重啟后表的自增值會設為max(id)+1，而myisam引擎的自增值是保存在文件中，重啟不會丟失。Mysql8.0開始，innodb的自增id能持久化了，重啟mysql，自增ID不會丟。
首先：表中自增列的上限是根據(jù)自增列的字段類型來定的。
若設定了自增id充當主鍵，當達到了自增id的上限值時，會發(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
無符號的int類型，上限是2147483647。這里我們將表的自增值設為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'
可以看到，第一個插入沒問題，因為自增列的值為2147483647，這是達到了上限，還沒有超過，第二行數(shù)據(jù)插入時，則報出主鍵重復，在達到上限后，無法再分配新的更大的自增值，也沒有從1開始從頭分配，在這里表的auto_increment值會一直是2147483647。
對于寫入量大，且經(jīng)常刪除數(shù)據(jù)的表，自增id設為int類型還是偏小的，所以我們?yōu)榱吮苊獬霈F(xiàn)自增id漲滿的情況，這邊統(tǒng)一建議自增id的類型設為unsigned bingint，這樣基本可以保障表的自增id是永遠夠用的。
這里內(nèi)容比較多，innodb是索引組織表，所以涉及到索引的知識，但這不是本文的重點，我們快速回顧索引知識：
1.? Innodb索引分為主鍵跟輔助索引，主鍵即全表，輔助索引葉子節(jié)點保存主鍵的值，而主鍵的葉子節(jié)點保存數(shù)據(jù)行，中間節(jié)點存著葉子節(jié)點的路由值。
2.? Innodb存儲數(shù)據(jù)（索引）的單位是頁，這里默認是16K，這也意味著，數(shù)據(jù)本身越小，一個頁中能存數(shù)據(jù)的量越多，而檢索效率不僅僅由索引的層數(shù)來決定，更是由一次能夠緩存的數(shù)據(jù)量來定，也就是說數(shù)據(jù)本身越小，則一次IO能夠提取到緩沖區(qū)的數(shù)據(jù)越多（OS每次IO的量是固定的4K），查詢的效率越好。
其實能夠理解索引的結(jié)構(gòu)及索引寫入插入、更新的原理，則自然就明白為何建議使用自增id。這里我直接列出使用自增id 當主鍵的好處吧：
1.? 順序?qū)懭耄苊饬巳~的分裂，數(shù)據(jù)寫入效率好
2.? 縮小了表的體積，特別是相比于UUID當主鍵，甚至組合字段當主鍵時，效果更明顯
3.? 查詢效率好，原因就是我上面說到索引知識的第二點。
4.? 某些情況下，我們可以利用自增id來統(tǒng)計大表的大致行數(shù)。
5.? 在數(shù)據(jù)歸檔or垃圾數(shù)據(jù)清理時，也可方便的利用這個id去操作，效率高。
容易出現(xiàn)不連續(xù)的id
有的同志會發(fā)現(xiàn)，自己的表中id值存在空洞，如類似于1、2、3、8、9、10這樣，有的適合有想依賴于自增id的連續(xù)性來實現(xiàn)業(yè)務邏輯，所以會想方設法去修改id讓其變的連續(xù)，其實，這是沒有必要的，這一塊的業(yè)務邏輯交由MySQL實現(xiàn)是很不理智的，表的記錄小還好，要是表的數(shù)據(jù)量很大，修改起來就糟糕了。那么，為什么自增id會容易出現(xiàn)空洞呢？
自增id的修改機制如下：
在MySQL里面，如果字段id被定義為AUTO_INCREMENT，在插入一行數(shù)據(jù)的時候，自增值的行為如下：
1. 如果插入數(shù)據(jù)時id字段指定為0、null 或未指定值，那么就把這個表當前的
AUTO_INCREMENT值填到自增字段；
2. 如果插入數(shù)據(jù)時id字段指定了具體的值，就直接使用語句里指定的值。
根據(jù)要插入的值和當前自增值的大小關系，自增值的變更結(jié)果也會有所不同。假設，某次要插入的值是X，當前的自增值是Y。
1. 如果XY，那么這個表的自增值不變；
2. 如果X≥Y，就需要把當前自增值修改為 新的自增值 。
新的自增值生成算法是：從auto_increment_offset開始，以auto_increment_increment為步長，持續(xù)疊加，直到找到第一個大于X的值，作為新的自增值。
Insert、update、delete操作會讓id不連續(xù)。
Delete、update：刪除中間數(shù)據(jù)，會造成空動，而修改自增id值，也會造成空洞（這個很少）。
Insert：插入報錯（唯一鍵沖突與事務回滾），會造成空洞，因為這時候自增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)
可以看到，雖然事務回滾了，但自增id已經(jīng)回不到從前啦，唯一鍵沖突也是這樣的，這里就不做測試了。
在批量插入時（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了，再插入數(shù)據(jù)，即產(chǎn)生了空洞，這里是由mysql申請自增值的機制所造成的，MySQL在批量插入時，若一個值申請一個id，效率太慢，影響了批量插入的速度，故mysql采用下面的策略批量申請id。
1.? 語句執(zhí)行過程中，第一次申請自增id，會分配1個；
2.? 1個用完以后，這個語句第二次申請自增id，會分配2個；
3.? 2個用完以后，還是這個語句，第三次申請自增id，會分配4個；
4.? 依此類推，同一個語句去申請自增id，每次申請到的自增id個數(shù)都是上一次的兩倍。
在對自增列進行操作時，存在著自增鎖，mysql的innodb_autoinc_lock_mode參數(shù)控制著自增鎖的上鎖機制。該參數(shù)有0、1、2三種模式：
0：語句執(zhí)行結(jié)束后釋放自增鎖，MySQL5.0時采用這種模式，并發(fā)度較低。
1：mysql的默認設置。普通的insert語句申請后立馬釋放，insert select、replace insert、load data等批量插入語句要等語句執(zhí)行結(jié)束后才釋放，并發(fā)讀得到提升
2：所有的語句都是申請后立馬釋放，并發(fā)度大大提升！但是在binlog為statement格式時，主從數(shù)據(jù)會發(fā)生不一致。這一塊網(wǎng)上有很多介紹，我不做介紹了。
在徹底了解了MYSQL的自增機制以后，在實際生產(chǎn)中就能靈活避坑，這里建議不要用自增id值去當表的行數(shù)，當需要對大表準確統(tǒng)計行數(shù)時，可以去count(*)從庫，如果業(yè)務很依賴大表的準確行數(shù)，直接弄個中間表來統(tǒng)計，或者考慮要不要用mysql的innodb來存儲數(shù)據(jù)，這個是需要自己去權(quán)衡。另外對于要求很高的寫入性能，但寫入量又比較大的業(yè)務，自增id的使用依然存在熱點寫入的問題，存在性能瓶頸，這時候可通過分庫分表來解決。
mysql自增id自定義id區(qū)別
mysql自增id自定義id區(qū)別是。
1、自增id通過mysql，id依次遞增，一般來說插入的id為上一個id值加1。
2、自定義id是桌面默認的，而且不增加。
mysql 數(shù)據(jù)庫 設計表 里面的選項有個自動遞增,是什么意思?
一般主鍵可以設置這個選項，主鍵是唯一的，自動遞增使得該列每項都是唯一的，還可以設置是自增的跨度，在插入數(shù)據(jù)時，該列就自動根據(jù)表況進行賦值了。
mysql中自增列為什么
create table cdat
(
localt char(20) not null,
cd char(5) not null,
snosat char(2) not null,
rnorec char(3) not null,
id INT(20) not null AUTO_INCREMENT,
primary key (id)
);
主鍵只能有一個，要設置索引的話請用index。是AUTO_INCREMENT, 不是auto0increment
mysql中auto_increment是干什么的？
MySQL的中AUTO_INCREMENT類型的屬性用于為一個表中記錄自動生成ID功能.
可在一定程度上代替Oracle，PostgreSQL等數(shù)據(jù)庫中的sequence。
在數(shù)據(jù)庫應用，我們經(jīng)常要用到唯一編號，以標識記錄。在MySQL中可通過數(shù)據(jù)列的AUTO_INCREMENT屬性來自動生成。
可在建表時可用“AUTO_INCREMENT=n”選項來指定一個自增的初始值。
可用alter table table_name AUTO_INCREMENT=n命令來重設自增的起始值。
當插入記錄時，如果為AUTO_INCREMENT數(shù)據(jù)列明確指定了一個數(shù)值，則會出現(xiàn)兩種情況，
情況一，如果插入的值與已有的編號重復，則會出現(xiàn)出錯信息，因為AUTO_INCREMENT數(shù)據(jù)列的值必須是唯一的；
情況二，如果插入的值大于已編號的值，則會把該插入到數(shù)據(jù)列中，并使在下一個編號將從這個新值開始遞增。也就是說，可以跳過一些編號。
如果自增序列的最大值被刪除了，則在插入新記錄時，該值被重用。
如果用UPDATE命令更新自增列，如果列值與已有的值重復，則會出錯。如果大于已有值，則下一個編號從該值開始遞增。
擴展資料
在使用AUTO_INCREMENT時，應注意以下幾點：
1、AUTO_INCREMENT是數(shù)據(jù)列的一種屬性，只適用于整數(shù)類型數(shù)據(jù)列。
2、設置AUTO_INCREMENT屬性的數(shù)據(jù)列應該是一個正數(shù)序列，所以應該把該數(shù)據(jù)列聲明為UNSIGNED，這樣序列的編號個可增加一倍。
3、AUTO_INCREMENT數(shù)據(jù)列必須有唯一索引，以避免序號重復(即是主鍵或者主鍵的一部分)。
4、AUTO_INCREMENT數(shù)據(jù)列必須具備NOT NULL屬性。
5、AUTO_INCREMENT數(shù)據(jù)列序號的最大值受該列的數(shù)據(jù)類型約束，如TINYINT數(shù)據(jù)列的最大編號是127,如加上UNSIGNED，則最大為255。
一旦達到上限，AUTO_INCREMENT就會失效。
6、當進行全表刪除時，MySQL AUTO_INCREMENT會從1重新開始編號。
這是因為進行全表操作時，MySQL(和PHP搭配之最佳組合)實際是做了這樣的優(yōu)化操作：先把數(shù)據(jù)表里的所有數(shù)據(jù)和索引刪除，然后重建數(shù)據(jù)表。
7、如果想刪除所有的數(shù)據(jù)行又想保留序列編號信息，可這樣用一個帶where的delete命令以抑制MySQL(和PHP搭配之最佳組合)的優(yōu)化：delete?from?table_name?where?1。
也可以用last_insert_id（）獲取剛剛自增過的值。
參考資料：IT專家網(wǎng)-MYSQL中取得最后一條記錄
網(wǎng)頁標題：mysql自增怎么理解 mysql語句自增
文章轉(zhuǎn)載：http://www.dlmjj.cn/article/hhphhd.html
