當(dāng)我們使用 MySQL 進(jìn)行數(shù)據(jù)存儲時,一般會為一張表設(shè)置一個自增主鍵,當(dāng)有數(shù)據(jù)行插入時,該主鍵字段則會根據(jù)步長與偏移量增長(默認(rèn)每次+1)。
站在用戶的角度思考問題,與客戶深入溝通,找到溫縣網(wǎng)站設(shè)計(jì)與溫縣網(wǎng)站推廣的解決方案,憑借多年的經(jīng)驗(yàn),讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合,創(chuàng)造個性化、用戶體驗(yàn)好的作品,建站類型包括:網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、申請域名、虛擬空間、企業(yè)郵箱。業(yè)務(wù)覆蓋溫縣地區(qū)。
下文以 Innodb 引擎為主進(jìn)行介紹,使用自增主鍵的好處有很多,如:索引空間占比小、范圍查詢與排序都友好、避免像 UUID 這樣隨機(jī)字符串帶來的頁分裂問題等...
當(dāng)我們對該表設(shè)置了自增主鍵之后,則會在該表上產(chǎn)生一個計(jì)數(shù)器,用于為自增列分配 ID 。
自增的值并不是保存在表結(jié)構(gòu)信息內(nèi)的,對于不同的版本它們有如下的區(qū)別:
計(jì)數(shù)器的值存儲在內(nèi)存中的,重啟后丟棄,下一次將讀取最大的一個自增ID往后繼續(xù)發(fā)號。
計(jì)數(shù)器的值將會持久化到磁盤。在每次發(fā)號時都將寫入 Redolog ,并在每個 Checkpoint 都進(jìn)行保存,重啟時候使用 Redolog 恢復(fù)重啟之前的值。
可以預(yù)先確定插入行數(shù)的語句(像簡單 insert 的語句包含多個 value 這種情況也是屬于簡單插入,因?yàn)樵谶M(jìn)行插入時就已經(jīng)可以確定行數(shù)了)
預(yù)先不知道要插入的行數(shù)的語句(包括 INSERT ... SELECT, REPLACE ... SELECT 和 LOAD DATA 語句,但不包括 plain INSERT )
如果一個事務(wù)正在向表中插入值,則會產(chǎn)生表級的共享鎖,以便當(dāng)前事務(wù)插入的行接收連續(xù)的主鍵值。
當(dāng)處于[ 傳統(tǒng)模式 ]與[ 連續(xù)模式 ]時,每次訪問計(jì)數(shù)器時都會加上一個名為 AUTO-INC 的表級鎖
傳統(tǒng)模式:鎖只持有到該語句執(zhí)行結(jié)束,注意是語句結(jié)束,不是事務(wù)結(jié)束
連續(xù)模式:批量插入時鎖持有到該語句執(zhí)行結(jié)束,簡單插入時鎖持有到申請完自增ID后即釋放,不直到語句完成
通過調(diào)整 innodb_autoinc_lock_mode 配置項(xiàng),可以定義 AUTO-INC 鎖的模式,不同的模式對應(yīng)的策略與鎖的粒度也將不同。
當(dāng)使用基于 Binlog 的復(fù)制場景時,對于 statement(SBR)同步模式下只有[ 傳統(tǒng)模式 ]與[ 連續(xù)模式 ]能保證語句的正確性。
基于 row(RBR)行復(fù)制的情況下任何配置模式都可以。
執(zhí)行語句時加 AUTO-INC 表級鎖,執(zhí)行完畢后釋放
針對 Bulk Inserts 時才會采用 AUTO-INC 鎖,而針對 Simple Inserts 時,則采用了一種新的輕量級的互斥鎖來分配 auto_increment 列的值。
該模式下可以保證同一條 insert 語句中新插入的自增 ID 都是連續(xù)的,但如果前一個事務(wù) rollback 丟棄了一部分 ID 的話也會存在后續(xù) ID 出現(xiàn)間隔的情況。
來一個分配一個,不會產(chǎn)生 AUTO-INC 表級鎖 ,僅僅會鎖住分配 ID 的過程。
由于鎖的粒度減少,多條語句在插入時進(jìn)行鎖競爭,自增長的值可能不是連續(xù)的。
且當(dāng) Binlog 模式為 statement(SBR)時自增 ID 不能保證數(shù)據(jù)的正確性
不一定,業(yè)務(wù)也不應(yīng)該過分依賴 MySQL 自增 ID 的連續(xù)性,在以下三種情況下,并不能保證自增 ID 的連續(xù)性:
假設(shè)已存在數(shù)據(jù){1,張三},且張三所屬的字段設(shè)置了唯一主鍵
此時再次插入{null,張三}時候,主鍵沖突插入失敗,但表的計(jì)數(shù)器已由2變成了3
當(dāng)下次插入{null,李四}的時候最終入庫的會變成{3,李四}
在一個事務(wù)里進(jìn)行數(shù)據(jù)的插入,但最后并沒提交,而是執(zhí)行了 Rollback 。那么計(jì)數(shù)器已遞增的 ID 是不會返還的,而是被直接丟棄。
發(fā)生大量插入時可能會出現(xiàn)自增 ID 并不是連續(xù)的情況
當(dāng)我們?yōu)楸碓O(shè)置了自增主鍵后,自增 ID 的范圍則與主鍵的數(shù)據(jù)類型長度相關(guān)。
如果沒有一張表里沒有設(shè)置任何主鍵,則會自動生成一個隱性的6字節(jié)的 row_id 作為主鍵,它的取值范圍為 0 到 2^48-1。
row_id 是由一個全局的 dict_sys.row_id 參數(shù)進(jìn)行維護(hù)的,所有沒有主鍵的表都會用上它(并不是每一個表單獨(dú)占一份 row_id list )
那么針對這兩種主鍵,則會有以下兩種情況發(fā)生:
當(dāng)自增 ID 到達(dá)上限后,受到主鍵數(shù)據(jù)類型的影響,計(jì)數(shù)器發(fā)放的下一個 ID 也是當(dāng)前這個 Max ID ,當(dāng)執(zhí)行語句時則會提示主鍵沖突。
建議根據(jù)業(yè)務(wù)合理規(guī)劃,在進(jìn)行表設(shè)計(jì)時就選擇適合的數(shù)據(jù)類型。
當(dāng)然也可以直接選擇 Bigint 類型,它的取值范圍是無符號情況下:0到 2^64–1(18446744073709551615)
這里并不是指 bigint 類型一定不會用完,畢竟一個有范圍的持續(xù)增長的值一定會有溢出的時候,只是說一般場景下它都是足夠使用的。
當(dāng) row_id 使用完后則又會從 0 開始發(fā)放,此時新插入的數(shù)據(jù)將覆蓋回 row_id=0 的數(shù)據(jù)行。
由于它并不產(chǎn)生錯誤,還會造成數(shù)據(jù)的覆蓋寫。所以我們平時還是盡量給表都設(shè)置一個合理的主鍵才是。
在實(shí)際業(yè)務(wù)場景中,ID 常常需要返回給客戶端用來進(jìn)行相關(guān)業(yè)務(wù)操作。
假如我們有個 userinfo?uid=? 的 API 接口,而用戶 ID 是自增的,這時會發(fā)生什么?
該接口通過簡單的嘗試就可以暴露出真實(shí)的業(yè)務(wù)用戶總數(shù),可以很方便的使用爬蟲從1開始遞增獲取數(shù)據(jù)信息。
那么有的同學(xué)說,我既想使用自增 ID 帶來的好處,也不想承受這種比較常見的問題,那該怎么辦呢?
在輸出或者獲取前對指定字段進(jìn)行可逆的轉(zhuǎn)義操作
優(yōu)點(diǎn):實(shí)現(xiàn)起來比較簡單,無論單體業(yè)務(wù)或者分布式應(yīng)用都無需考慮對數(shù)據(jù)源的解析,只需在客戶端實(shí)現(xiàn)自己的轉(zhuǎn)義與解析方法即可;
缺點(diǎn):業(yè)務(wù)入侵較大,且需要前后端各個合作方確認(rèn)統(tǒng)一的標(biāo)準(zhǔn);如果轉(zhuǎn)義方法有調(diào)整,變更影響面也會很大;字符串長度會隨ID長度而變化,使用空位填充也會特別明顯;
優(yōu)點(diǎn):由于采用了時間戳進(jìn)行 ID 生成,該 ID 是有序的,對范圍查詢與排序都比較友好;
缺點(diǎn):需要保證發(fā)號節(jié)點(diǎn)的高可用性;另外由于生成時依賴時間戳,需要考慮時鐘回?fù)芘c時鐘同步的問題;
維護(hù)一份 ID 與 hash 的映射字典,它可以存在于客戶端本身,也可以依賴其他如 Redis 、ETCD 之類的組件
優(yōu)點(diǎn):hash 長度不會隨著 ID 長度或值的變化而變化;可以根據(jù)已有的 hash code 來造布隆過濾器;
缺點(diǎn):業(yè)務(wù)入侵較大,查詢時同樣需要先根據(jù) hash key 找到對應(yīng)的 ID 值;需要考慮選擇合適的 hash 算法以及解決 hash 沖突或擴(kuò)容的問題。
創(chuàng)建數(shù)據(jù)庫是定義id,例如
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
這樣就設(shè)置id為自增行,并為主鍵
設(shè)置自增列
MYSQL的自增列一定要是有索引的列,設(shè)置種子值要在表的后面設(shè)置
--mysql
-- 設(shè)置自增ID從N開始
CREATE TABLE empautoinc(
ID INT PRIMARY KEY AUTO_INCREMENT
) AUTO_INCREMENT = 100 ; --(設(shè)置自增ID從100開始)
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è)置自增列的步長,可以分為全局級別和會話級別
如果是會話級別,那么當(dāng)用戶新建一個會話的時候,那么步長又回到了全局級別,所以mysql的步長跟sqlserver的步長有很大的不同
mysql不能設(shè)置為 表級別 的步長
私信666領(lǐng)取資料
如何在MYSQL插數(shù)據(jù)ID自增的方法。
如下參考:
1.在添加字段之前,第一個應(yīng)該首先檢查當(dāng)前tb1表的結(jié)構(gòu),如下圖所示。
2.實(shí)例字段列添加到表,如下所示。
3.再次看表結(jié)構(gòu)和比較之前和之后的情況添加字段,如下圖所示。
4.最后,插入新的數(shù)據(jù)行看到的樣子,最后添加自動增長的字段,如下所示。
注意事項(xiàng):
MySQL使用的SQL語言是訪問數(shù)據(jù)庫最常用的標(biāo)準(zhǔn)語言。MySQL軟件采用雙重許可政策,分為社區(qū)版,商業(yè)版,由于其體積小、速度快、總體擁有成本低,尤其是開放源碼這一特性,一般中小網(wǎng)站開發(fā)選擇MySQL作為數(shù)據(jù)庫。
在MySQL中可通過字段的AUTO_INCREMENT屬性來自動生成。
mysql數(shù)據(jù)庫表主鍵自增長的sql語句
1、不控制主鍵的起點(diǎn)
create?table?emb_t_dictBusType
(
emb_c_busTypeID??????int?not?null?auto_increment,
emb_c_busTypeEnName??varchar(255)?not?null,
emb_c_busTypeZhName??varchar(255)?not?null,
primary?key(emb_c_busTypeID)??
)engine=INNODB??default?charset=gbk;
2、控制主鍵的起點(diǎn)
create?table?emb_t_dictBusType
(
emb_c_busTypeID??????int?not?null?auto_increment,
emb_c_busTypeEnName??varchar(255)?not?null,
emb_c_busTypeZhName??varchar(255)?not?null,
primary?key(emb_c_busTypeID)??
)engine=INNODB?auto_increment=1001?default?charset=gbk;
列的自增屬性,一般用來設(shè)置整數(shù)列根據(jù)一定步長逐步增長的值,類似于其他數(shù)據(jù)庫的序列。不過這里的“序列”是基于特定一張表的。關(guān)于自增屬性的相關(guān)特性如下:
1. 控制自增屬性性能的變量:innodb_autoinc_lock_mode
innodb_autoinc_lock_mode=0
代表傳統(tǒng)模式,也就是說,在對有自增屬性的字段插入記錄時,會持續(xù)持有一個表級別的自增鎖,直到語句執(zhí)行結(jié)束為止。比如說下面兩條語句,SQL 1 在執(zhí)行期間,一直持有對表 f1 的表級自增鎖,接下來 SQL 2 執(zhí)行時鎖超時。
innodb_autoinc_lock_mode=1
代表連續(xù)模式,和傳統(tǒng)模式差不多,不同的點(diǎn)在于對于簡單的插入語句,比如 SQL 2,只在分配新的 ID 過程中持有一個輕量級的互斥鎖(線程級別,而不是事務(wù)級別),而不是直到語句結(jié)束才釋放的表鎖。
代表交錯模式。這個模式下放棄了自增表鎖,產(chǎn)生的值會不連續(xù)。不過這是性能最高的模式,多條插入語句可以并發(fā)執(zhí)行。MySQL 8.0 默認(rèn)就是交錯模式。
那針對復(fù)制安全來說,以上三種模式,0 和 1 對語句級別安全,也就是產(chǎn)生的二進(jìn)制日志復(fù)制到任何其他機(jī)器都數(shù)據(jù)都一致;2 對于語句級別不安全;三種模式對二進(jìn)制日志格式為行的都安全。
2. 控制自增屬性的步長以及偏移量
一般用在主主復(fù)制架構(gòu)或者多源復(fù)制架構(gòu)里,主動規(guī)避主鍵沖突。
auto_increment_increment 控制步長
auto_increment_offset 控制偏移量
3. 對于要立刻獲取插入值的需求
就是說要獲取一張表任何時候的最大值,應(yīng)該時刻執(zhí)行以下 SQL 3 ,而不是 SQL 2。SQL 2 里的函數(shù) last_insert_id() 只獲取上一條語句最開始的 ID,只適合簡單的 INSERT。
4. 自增列溢出現(xiàn)象
自增屬性的列如果到了此列數(shù)據(jù)類型的最大值,會發(fā)生值溢出。比如變更表 f1 的自增屬性列為 tinyint。
新聞標(biāo)題:mysql自增怎么定義 mysql中怎么設(shè)置自增
URL分享:http://chinadenli.net/article28/hgodjp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供做網(wǎng)站、面包屑導(dǎo)航、搜索引擎優(yōu)化、外貿(mào)網(wǎng)站建設(shè)、品牌網(wǎng)站設(shè)計(jì)、ChatGPT
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)