技術分享 | 關于 MySQL 自增 ID 的事兒

當我們使用 MySQL 進行數據存儲時，一般會為一張表設置一個自增主鍵，當有數據行插入時，該主鍵字段則會根據步長與偏移量增長（默認每次+1）。

站在用戶的角度思考問題，與客戶深入溝通，找到溫縣網站設計與溫縣網站推廣的解決方案，憑借多年的經驗，讓設計與互聯網技術結合，創造個性化、用戶體驗好的作品，建站類型包括：網站設計、成都網站建設、企業官網、英文網站、手機端網站、網站推廣、申請域名、虛擬空間、企業郵箱。業務覆蓋溫縣地區。

下文以 Innodb 引擎為主進行介紹，使用自增主鍵的好處有很多，如：索引空間占比小、范圍查詢與排序都友好、避免像 UUID 這樣隨機字符串帶來的頁分裂問題等...

當我們對該表設置了自增主鍵之后，則會在該表上產生一個計數器，用于為自增列分配 ID 。

自增的值并不是保存在表結構信息內的，對于不同的版本它們有如下的區別：

計數器的值存儲在內存中的，重啟后丟棄，下一次將讀取最大的一個自增ID往后繼續發號。

計數器的值將會持久化到磁盤。在每次發號時都將寫入 Redolog ，并在每個 Checkpoint 都進行保存，重啟時候使用 Redolog 恢復重啟之前的值。

可以預先確定插入行數的語句（像簡單 insert 的語句包含多個 value 這種情況也是屬于簡單插入，因為在進行插入時就已經可以確定行數了）

預先不知道要插入的行數的語句（包括 INSERT ... SELECT, REPLACE ... SELECT 和 LOAD DATA 語句，但不包括 plain INSERT ）

如果一個事務正在向表中插入值，則會產生表級的共享鎖，以便當前事務插入的行接收連續的主鍵值。

當處于[ 傳統模式 ]與[ 連續模式 ]時，每次訪問計數器時都會加上一個名為 AUTO-INC 的表級鎖

傳統模式：鎖只持有到該語句執行結束，注意是語句結束，不是事務結束

連續模式：批量插入時鎖持有到該語句執行結束，簡單插入時鎖持有到申請完自增ID后即釋放，不直到語句完成

通過調整 innodb_autoinc_lock_mode 配置項，可以定義 AUTO-INC 鎖的模式，不同的模式對應的策略與鎖的粒度也將不同。

當使用基于 Binlog 的復制場景時，對于 statement（SBR）同步模式下只有[ 傳統模式 ]與[ 連續模式 ]能保證語句的正確性。

基于 row（RBR）行復制的情況下任何配置模式都可以。

執行語句時加 AUTO-INC 表級鎖，執行完畢后釋放

針對 Bulk Inserts 時才會采用 AUTO-INC 鎖，而針對 Simple Inserts 時，則采用了一種新的輕量級的互斥鎖來分配 auto_increment 列的值。

該模式下可以保證同一條 insert 語句中新插入的自增 ID 都是連續的，但如果前一個事務 rollback 丟棄了一部分 ID 的話也會存在后續 ID 出現間隔的情況。

來一個分配一個，不會產生 AUTO-INC 表級鎖 ，僅僅會鎖住分配 ID 的過程。

由于鎖的粒度減少，多條語句在插入時進行鎖競爭，自增長的值可能不是連續的。

且當 Binlog 模式為 statement（SBR）時自增 ID 不能保證數據的正確性

不一定，業務也不應該過分依賴 MySQL 自增 ID 的連續性，在以下三種情況下，并不能保證自增 ID 的連續性：

假設已存在數據{1,張三}，且張三所屬的字段設置了唯一主鍵

此時再次插入{null,張三}時候，主鍵沖突插入失敗，但表的計數器已由2變成了3

當下次插入{null,李四}的時候最終入庫的會變成{3,李四}

在一個事務里進行數據的插入，但最后并沒提交，而是執行了 Rollback 。那么計數器已遞增的 ID 是不會返還的，而是被直接丟棄。

發生大量插入時可能會出現自增 ID 并不是連續的情況

當我們為表設置了自增主鍵后，自增 ID 的范圍則與主鍵的數據類型長度相關。

如果沒有一張表里沒有設置任何主鍵，則會自動生成一個隱性的6字節的 row_id 作為主鍵，它的取值范圍為 0 到 2^48-1。

row_id 是由一個全局的 dict_sys.row_id 參數進行維護的，所有沒有主鍵的表都會用上它（并不是每一個表單獨占一份 row_id list ）

那么針對這兩種主鍵，則會有以下兩種情況發生：

當自增 ID 到達上限后，受到主鍵數據類型的影響，計數器發放的下一個 ID 也是當前這個 Max ID ，當執行語句時則會提示主鍵沖突。

建議根據業務合理規劃，在進行表設計時就選擇適合的數據類型。

當然也可以直接選擇 Bigint 類型，它的取值范圍是無符號情況下：0到 2^64–1（18446744073709551615）

這里并不是指 bigint 類型一定不會用完，畢竟一個有范圍的持續增長的值一定會有溢出的時候，只是說一般場景下它都是足夠使用的。

當 row_id 使用完后則又會從 0 開始發放，此時新插入的數據將覆蓋回 row_id=0 的數據行。

由于它并不產生錯誤，還會造成數據的覆蓋寫。所以我們平時還是盡量給表都設置一個合理的主鍵才是。

在實際業務場景中，ID 常常需要返回給客戶端用來進行相關業務操作。

假如我們有個 userinfo?uid=? 的 API 接口，而用戶 ID 是自增的，這時會發生什么？

該接口通過簡單的嘗試就可以暴露出真實的業務用戶總數，可以很方便的使用爬蟲從1開始遞增獲取數據信息。

那么有的同學說，我既想使用自增 ID 帶來的好處，也不想承受這種比較常見的問題，那該怎么辦呢？

在輸出或者獲取前對指定字段進行可逆的轉義操作

優點：實現起來比較簡單，無論單體業務或者分布式應用都無需考慮對數據源的解析，只需在客戶端實現自己的轉義與解析方法即可；

缺點：業務入侵較大，且需要前后端各個合作方確認統一的標準；如果轉義方法有調整，變更影響面也會很大；字符串長度會隨ID長度而變化，使用空位填充也會特別明顯；

優點：由于采用了時間戳進行 ID 生成，該 ID 是有序的，對范圍查詢與排序都比較友好；

缺點：需要保證發號節點的高可用性；另外由于生成時依賴時間戳，需要考慮時鐘回撥與時鐘同步的問題；

維護一份 ID 與 hash 的映射字典，它可以存在于客戶端本身，也可以依賴其他如 Redis 、ETCD 之類的組件

優點：hash 長度不會隨著 ID 長度或值的變化而變化；可以根據已有的 hash code 來造布隆過濾器；

缺點：業務入侵較大，查詢時同樣需要先根據 hash key 找到對應的 ID 值；需要考慮選擇合適的 hash 算法以及解決 hash 沖突或擴容的問題。

mysql怎么把數據庫id設為自增

創建數據庫是定義id，例如

id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT

這樣就設置id為自增行，并為主鍵

mysql如何設置ID自增

設置自增列

MYSQL的自增列一定要是有索引的列，設置種子值要在表的后面設置

--mysql

-- 設置自增ID從N開始

CREATE TABLE empautoinc(

ID INT PRIMARY KEY AUTO_INCREMENT

) AUTO_INCREMENT = 100 ; --（設置自增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)

設置自增列的步長，可以分為全局級別和會話級別

如果是會話級別，那么當用戶新建一個會話的時候，那么步長又回到了全局級別，所以mysql的步長跟sqlserver的步長有很大的不同

mysql不能設置為 表級別 的步長

私信666領取資料

如何在MYSQL插數據 ID自增

如何在MYSQL插數據ID自增的方法。

如下參考：

1.在添加字段之前，第一個應該首先檢查當前tb1表的結構，如下圖所示。

2.實例字段列添加到表，如下所示。

3.再次看表結構和比較之前和之后的情況添加字段，如下圖所示。

4.最后，插入新的數據行看到的樣子，最后添加自動增長的字段，如下所示。

注意事項：

MySQL使用的SQL語言是訪問數據庫最常用的標準語言。MySQL軟件采用雙重許可政策,分為社區版,商業版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特性，一般中小網站開發選擇MySQL作為數據庫。

Mysql數據庫自增長如何設置？

在MySQL中可通過字段的AUTO_INCREMENT屬性來自動生成。

mysql數據庫表主鍵自增長的sql語句

1、不控制主鍵的起點

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、控制主鍵的起點

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;

mysql的設置主鍵自增

列的自增屬性，一般用來設置整數列根據一定步長逐步增長的值，類似于其他數據庫的序列。不過這里的“序列”是基于特定一張表的。關于自增屬性的相關特性如下：

1. 控制自增屬性性能的變量：innodb_autoinc_lock_mode

innodb_autoinc_lock_mode=0

代表傳統模式，也就是說，在對有自增屬性的字段插入記錄時，會持續持有一個表級別的自增鎖，直到語句執行結束為止。比如說下面兩條語句，SQL 1 在執行期間，一直持有對表 f1 的表級自增鎖，接下來 SQL 2 執行時鎖超時。

innodb_autoinc_lock_mode=1

代表連續模式，和傳統模式差不多，不同的點在于對于簡單的插入語句，比如 SQL 2，只在分配新的 ID 過程中持有一個輕量級的互斥鎖（線程級別，而不是事務級別），而不是直到語句結束才釋放的表鎖。

代表交錯模式。這個模式下放棄了自增表鎖，產生的值會不連續。不過這是性能最高的模式，多條插入語句可以并發執行。MySQL 8.0 默認就是交錯模式。

那針對復制安全來說，以上三種模式，0 和 1 對語句級別安全，也就是產生的二進制日志復制到任何其他機器都數據都一致；2 對于語句級別不安全；三種模式對二進制日志格式為行的都安全。

2. 控制自增屬性的步長以及偏移量

一般用在主主復制架構或者多源復制架構里，主動規避主鍵沖突。

auto_increment_increment 控制步長

auto_increment_offset 控制偏移量

3. 對于要立刻獲取插入值的需求

就是說要獲取一張表任何時候的最大值，應該時刻執行以下 SQL 3 ，而不是 SQL 2。SQL 2 里的函數 last_insert_id() 只獲取上一條語句最開始的 ID，只適合簡單的 INSERT。

4. 自增列溢出現象

自增屬性的列如果到了此列數據類型的最大值，會發生值溢出。比如變更表 f1 的自增屬性列為 tinyint。

新聞標題：mysql自增怎么定義 mysql中怎么設置自增
URL分享：http://www.yijiale78.com/article28/hgodjp.html

成都網站建設公司_創新互聯，為您提供做網站、面包屑導航、搜索引擎優化、外貿網站建設、品牌網站設計、ChatGPT

廣告

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