如何在MYSQL插數(shù)據(jù)ID自增的方法。
我們提供的服務(wù)有:網(wǎng)站制作、成都網(wǎng)站制作、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、南湖ssl等。為成百上千企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù),是有科學(xué)管理、有技術(shù)的南湖網(wǎng)站制作公司
如下參考:
1.在添加字段之前,第一個(gè)應(yīng)該首先檢查當(dāng)前tb1表的結(jié)構(gòu),如下圖所示。
2.實(shí)例字段列添加到表,如下所示。
3.再次看表結(jié)構(gòu)和比較之前和之后的情況添加字段,如下圖所示。
4.最后,插入新的數(shù)據(jù)行看到的樣子,最后添加自動(dòng)增長(zhǎng)的字段,如下所示。
注意事項(xiàng):
MySQL使用的SQL語(yǔ)言是訪問(wèn)數(shù)據(jù)庫(kù)最常用的標(biāo)準(zhǔn)語(yǔ)言。MySQL軟件采用雙重許可政策,分為社區(qū)版,商業(yè)版,由于其體積小、速度快、總體擁有成本低,尤其是開(kāi)放源碼這一特性,一般中小網(wǎng)站開(kāi)發(fā)選擇MySQL作為數(shù)據(jù)庫(kù)。
設(shè)置自增列
MYSQL的自增列一定要是有索引的列,設(shè)置種子值要在表的后面設(shè)置
--mysql
-- 設(shè)置自增ID從N開(kāi)始
CREATE TABLE empautoinc(
ID INT PRIMARY KEY AUTO_INCREMENT
) AUTO_INCREMENT = 100 ; --(設(shè)置自增ID從100開(kāi)始)
insert into empautoinc(id) values(null);
Query OK, 1 row affected (0.00 sec)
mysql select * from empautoinc;
+-----+
| ID |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)
show table status like 'empautoinc'G;
*************************** 1. row ***************************
Name: empautoinc
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 101
Create_time: 2016-10-27 01:50:32
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
設(shè)置自增列的步長(zhǎng),可以分為全局級(jí)別和會(huì)話級(jí)別
如果是會(huì)話級(jí)別,那么當(dāng)用戶新建一個(gè)會(huì)話的時(shí)候,那么步長(zhǎng)又回到了全局級(jí)別,所以mysql的步長(zhǎng)跟sqlserver的步長(zhǎng)有很大的不同
mysql不能設(shè)置為 表級(jí)別 的步長(zhǎng)
私信666領(lǐng)取資料
當(dāng)我們使用 MySQL 進(jìn)行數(shù)據(jù)存儲(chǔ)時(shí),一般會(huì)為一張表設(shè)置一個(gè)自增主鍵,當(dāng)有數(shù)據(jù)行插入時(shí),該主鍵字段則會(huì)根據(jù)步長(zhǎng)與偏移量增長(zhǎng)(默認(rèn)每次+1)。
下文以 Innodb 引擎為主進(jìn)行介紹,使用自增主鍵的好處有很多,如:索引空間占比小、范圍查詢與排序都友好、避免像 UUID 這樣隨機(jī)字符串帶來(lái)的頁(yè)分裂問(wèn)題等...
當(dāng)我們對(duì)該表設(shè)置了自增主鍵之后,則會(huì)在該表上產(chǎn)生一個(gè)計(jì)數(shù)器,用于為自增列分配 ID 。
自增的值并不是保存在表結(jié)構(gòu)信息內(nèi)的,對(duì)于不同的版本它們有如下的區(qū)別:
計(jì)數(shù)器的值存儲(chǔ)在內(nèi)存中的,重啟后丟棄,下一次將讀取最大的一個(gè)自增ID往后繼續(xù)發(fā)號(hào)。
計(jì)數(shù)器的值將會(huì)持久化到磁盤(pán)。在每次發(fā)號(hào)時(shí)都將寫(xiě)入 Redolog ,并在每個(gè) Checkpoint 都進(jìn)行保存,重啟時(shí)候使用 Redolog 恢復(fù)重啟之前的值。
可以預(yù)先確定插入行數(shù)的語(yǔ)句(像簡(jiǎn)單 insert 的語(yǔ)句包含多個(gè) value 這種情況也是屬于簡(jiǎn)單插入,因?yàn)樵谶M(jìn)行插入時(shí)就已經(jīng)可以確定行數(shù)了)
預(yù)先不知道要插入的行數(shù)的語(yǔ)句(包括 INSERT ... SELECT, REPLACE ... SELECT 和 LOAD DATA 語(yǔ)句,但不包括 plain INSERT )
如果一個(gè)事務(wù)正在向表中插入值,則會(huì)產(chǎn)生表級(jí)的共享鎖,以便當(dāng)前事務(wù)插入的行接收連續(xù)的主鍵值。
當(dāng)處于[ 傳統(tǒng)模式 ]與[ 連續(xù)模式 ]時(shí),每次訪問(wèn)計(jì)數(shù)器時(shí)都會(huì)加上一個(gè)名為 AUTO-INC 的表級(jí)鎖
傳統(tǒng)模式:鎖只持有到該語(yǔ)句執(zhí)行結(jié)束,注意是語(yǔ)句結(jié)束,不是事務(wù)結(jié)束
連續(xù)模式:批量插入時(shí)鎖持有到該語(yǔ)句執(zhí)行結(jié)束,簡(jiǎn)單插入時(shí)鎖持有到申請(qǐng)完自增ID后即釋放,不直到語(yǔ)句完成
通過(guò)調(diào)整 innodb_autoinc_lock_mode 配置項(xiàng),可以定義 AUTO-INC 鎖的模式,不同的模式對(duì)應(yīng)的策略與鎖的粒度也將不同。
當(dāng)使用基于 Binlog 的復(fù)制場(chǎng)景時(shí),對(duì)于 statement(SBR)同步模式下只有[ 傳統(tǒng)模式 ]與[ 連續(xù)模式 ]能保證語(yǔ)句的正確性。
基于 row(RBR)行復(fù)制的情況下任何配置模式都可以。
執(zhí)行語(yǔ)句時(shí)加 AUTO-INC 表級(jí)鎖,執(zhí)行完畢后釋放
針對(duì) Bulk Inserts 時(shí)才會(huì)采用 AUTO-INC 鎖,而針對(duì) Simple Inserts 時(shí),則采用了一種新的輕量級(jí)的互斥鎖來(lái)分配 auto_increment 列的值。
該模式下可以保證同一條 insert 語(yǔ)句中新插入的自增 ID 都是連續(xù)的,但如果前一個(gè)事務(wù) rollback 丟棄了一部分 ID 的話也會(huì)存在后續(xù) ID 出現(xiàn)間隔的情況。
來(lái)一個(gè)分配一個(gè),不會(huì)產(chǎn)生 AUTO-INC 表級(jí)鎖 ,僅僅會(huì)鎖住分配 ID 的過(guò)程。
由于鎖的粒度減少,多條語(yǔ)句在插入時(shí)進(jìn)行鎖競(jìng)爭(zhēng),自增長(zhǎng)的值可能不是連續(xù)的。
且當(dāng) Binlog 模式為 statement(SBR)時(shí)自增 ID 不能保證數(shù)據(jù)的正確性
不一定,業(yè)務(wù)也不應(yīng)該過(guò)分依賴 MySQL 自增 ID 的連續(xù)性,在以下三種情況下,并不能保證自增 ID 的連續(xù)性:
假設(shè)已存在數(shù)據(jù){1,張三},且張三所屬的字段設(shè)置了唯一主鍵
此時(shí)再次插入{null,張三}時(shí)候,主鍵沖突插入失敗,但表的計(jì)數(shù)器已由2變成了3
當(dāng)下次插入{null,李四}的時(shí)候最終入庫(kù)的會(huì)變成{3,李四}
在一個(gè)事務(wù)里進(jìn)行數(shù)據(jù)的插入,但最后并沒(méi)提交,而是執(zhí)行了 Rollback 。那么計(jì)數(shù)器已遞增的 ID 是不會(huì)返還的,而是被直接丟棄。
發(fā)生大量插入時(shí)可能會(huì)出現(xiàn)自增 ID 并不是連續(xù)的情況
當(dāng)我們?yōu)楸碓O(shè)置了自增主鍵后,自增 ID 的范圍則與主鍵的數(shù)據(jù)類型長(zhǎng)度相關(guān)。
如果沒(méi)有一張表里沒(méi)有設(shè)置任何主鍵,則會(huì)自動(dòng)生成一個(gè)隱性的6字節(jié)的 row_id 作為主鍵,它的取值范圍為 0 到 2^48-1。
row_id 是由一個(gè)全局的 dict_sys.row_id 參數(shù)進(jìn)行維護(hù)的,所有沒(méi)有主鍵的表都會(huì)用上它(并不是每一個(gè)表單獨(dú)占一份 row_id list )
那么針對(duì)這兩種主鍵,則會(huì)有以下兩種情況發(fā)生:
當(dāng)自增 ID 到達(dá)上限后,受到主鍵數(shù)據(jù)類型的影響,計(jì)數(shù)器發(fā)放的下一個(gè) ID 也是當(dāng)前這個(gè) Max ID ,當(dāng)執(zhí)行語(yǔ)句時(shí)則會(huì)提示主鍵沖突。
建議根據(jù)業(yè)務(wù)合理規(guī)劃,在進(jìn)行表設(shè)計(jì)時(shí)就選擇適合的數(shù)據(jù)類型。
當(dāng)然也可以直接選擇 Bigint 類型,它的取值范圍是無(wú)符號(hào)情況下:0到 2^64–1(18446744073709551615)
這里并不是指 bigint 類型一定不會(huì)用完,畢竟一個(gè)有范圍的持續(xù)增長(zhǎng)的值一定會(huì)有溢出的時(shí)候,只是說(shuō)一般場(chǎng)景下它都是足夠使用的。
當(dāng) row_id 使用完后則又會(huì)從 0 開(kāi)始發(fā)放,此時(shí)新插入的數(shù)據(jù)將覆蓋回 row_id=0 的數(shù)據(jù)行。
由于它并不產(chǎn)生錯(cuò)誤,還會(huì)造成數(shù)據(jù)的覆蓋寫(xiě)。所以我們平時(shí)還是盡量給表都設(shè)置一個(gè)合理的主鍵才是。
在實(shí)際業(yè)務(wù)場(chǎng)景中,ID 常常需要返回給客戶端用來(lái)進(jìn)行相關(guān)業(yè)務(wù)操作。
假如我們有個(gè) userinfo?uid=? 的 API 接口,而用戶 ID 是自增的,這時(shí)會(huì)發(fā)生什么?
該接口通過(guò)簡(jiǎn)單的嘗試就可以暴露出真實(shí)的業(yè)務(wù)用戶總數(shù),可以很方便的使用爬蟲(chóng)從1開(kāi)始遞增獲取數(shù)據(jù)信息。
那么有的同學(xué)說(shuō),我既想使用自增 ID 帶來(lái)的好處,也不想承受這種比較常見(jiàn)的問(wèn)題,那該怎么辦呢?
在輸出或者獲取前對(duì)指定字段進(jìn)行可逆的轉(zhuǎn)義操作
優(yōu)點(diǎn):實(shí)現(xiàn)起來(lái)比較簡(jiǎn)單,無(wú)論單體業(yè)務(wù)或者分布式應(yīng)用都無(wú)需考慮對(duì)數(shù)據(jù)源的解析,只需在客戶端實(shí)現(xiàn)自己的轉(zhuǎn)義與解析方法即可;
缺點(diǎn):業(yè)務(wù)入侵較大,且需要前后端各個(gè)合作方確認(rèn)統(tǒng)一的標(biāo)準(zhǔn);如果轉(zhuǎn)義方法有調(diào)整,變更影響面也會(huì)很大;字符串長(zhǎng)度會(huì)隨ID長(zhǎng)度而變化,使用空位填充也會(huì)特別明顯;
優(yōu)點(diǎn):由于采用了時(shí)間戳進(jìn)行 ID 生成,該 ID 是有序的,對(duì)范圍查詢與排序都比較友好;
缺點(diǎn):需要保證發(fā)號(hào)節(jié)點(diǎn)的高可用性;另外由于生成時(shí)依賴時(shí)間戳,需要考慮時(shí)鐘回?fù)芘c時(shí)鐘同步的問(wèn)題;
維護(hù)一份 ID 與 hash 的映射字典,它可以存在于客戶端本身,也可以依賴其他如 Redis 、ETCD 之類的組件
優(yōu)點(diǎn):hash 長(zhǎng)度不會(huì)隨著 ID 長(zhǎng)度或值的變化而變化;可以根據(jù)已有的 hash code 來(lái)造布隆過(guò)濾器;
缺點(diǎn):業(yè)務(wù)入侵較大,查詢時(shí)同樣需要先根據(jù) hash key 找到對(duì)應(yīng)的 ID 值;需要考慮選擇合適的 hash 算法以及解決 hash 沖突或擴(kuò)容的問(wèn)題。
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ī)制。
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)定的。
若設(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類型,上限是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類型還是偏小的,所以我們?yōu)榱吮苊獬霈F(xiàn)自增id漲滿的情況,這邊統(tǒng)一建議自增id的類型設(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值存在空洞,如類似于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.? 依此類推,同一個(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)解決。
方法一: 如果曾經(jīng)的數(shù)據(jù)都不需要的話,可以直接清空所有數(shù)據(jù),并將自增字段恢復(fù)從1開(kāi)始計(jì)數(shù)
truncate table 表名
方法二: dbcc checkident (’table_name’, reseed, new_reseed_value) 當(dāng)前值設(shè)置為 new_reseed_value。如果自創(chuàng)建表后沒(méi)有將行插入該表,則在執(zhí)行 DBCC CHECKIDENT 后插入的第一行將使用 new_reseed_value 作為標(biāo)識(shí)。否則,下一個(gè)插入的行將使用 new_reseed_value + 1。如果 new_reseed_value 的值小于標(biāo)識(shí)列中的最大值,以后引用該表時(shí)將產(chǎn)生 2627 號(hào)錯(cuò)誤信息。
方法二不會(huì)清空已有數(shù)據(jù),操作比較靈活,不僅可以將自增值歸零,也適用于刪除大量連續(xù)行后,重新設(shè)置自增值并插入新的數(shù)據(jù);或從新的值開(kāi)始,當(dāng)然不能和已有的沖突。
$sql="delete from $table_vote";
mysql_query($sql, $link);
$sql="alter table $table_vote auto_increment=1";
mysql_query($sql, $link);
網(wǎng)頁(yè)名稱:mysql自增怎么用 mysql實(shí)現(xiàn)自增
轉(zhuǎn)載來(lái)源:http://www.muchs.cn/article20/dosedco.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站營(yíng)銷、定制網(wǎng)站、搜索引擎優(yōu)化、建站公司、外貿(mào)建站、外貿(mào)網(wǎng)站建設(shè)
聲明:本網(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)