mysql的設(shè)置主鍵自增

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

創(chuàng)新互聯(lián)專注于網(wǎng)站建設(shè)，為客戶提供網(wǎng)站建設(shè)、成都做網(wǎng)站、網(wǎng)頁設(shè)計(jì)開發(fā)服務(wù)，多年建網(wǎng)站服務(wù)經(jīng)驗(yàn)，各類網(wǎng)站都可以開發(fā)，品牌網(wǎng)站制作，公司官網(wǎng)，公司展示網(wǎng)站，網(wǎng)站設(shè)計(jì)，建網(wǎng)站費(fèi)用，建網(wǎng)站多少錢，價(jià)格優(yōu)惠，收費(fèi)合理。

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。

mysql自增id列怎么設(shè)置？

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)

);

MySQL是一個開放源碼的小型關(guān)聯(lián)式數(shù)據(jù)庫管理系統(tǒng)，開發(fā)者為瑞典MySQL AB公司。目前MySQL被廣泛地應(yīng)用在Internet上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點(diǎn)，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了MySQL作為網(wǎng)站數(shù)據(jù)庫。

系統(tǒng)特性

1．使用C和C++編寫，并使用了多種編譯器進(jìn)行測試，保證源代碼的可移植性

2．支持AIX、FreeBSD、HP-UX、Linux、Mac OS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris、Windows等多種操作系統(tǒng)

3．為多種編程語言提供了API。這些編程語言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

4．支持多線程，充分利用CPU資源

5．優(yōu)化的SQL查詢算法，有效地提高查詢速度

6．既能夠作為一個單獨(dú)的應(yīng)用程序應(yīng)用在客戶端服務(wù)器網(wǎng)絡(luò)環(huán)境中，也能夠作為一個庫而嵌入到其他的軟件中。

7．提供多語言支持，常見的編碼如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作數(shù)據(jù)表名和數(shù)據(jù)列名。

8．提供TCP/IP、ODBC和JDBC等多種數(shù)據(jù)庫連接途徑。

9．提供用于管理、檢查、優(yōu)化數(shù)據(jù)庫操作的管理工具。

10．支持大型的數(shù)據(jù)庫?？梢蕴幚頁碛猩锨f條記錄的大型數(shù)據(jù)庫。

11．支持多種存儲引擎。

索引功能

索引是一種特殊的文件（InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分），它們包含著對數(shù)據(jù)表里所有記錄的引用指針。索引不是萬能的，索引可以加快數(shù)據(jù)檢索操作，但會使數(shù)據(jù)修改操作變慢。每修改數(shù)據(jù)記錄，索引就必須刷新一次。為了在某種程序上彌補(bǔ)這一缺陷，許多SQL命令都有一個DELAY_KEY_WRITE項(xiàng)。這個選項(xiàng)的作用是暫時制止MySQL在該命令每插入一條新記錄和每修改一條現(xiàn)有之后立刻對索引進(jìn)行刷新，對索引的刷新將等到全部記錄插入/修改完畢之后再進(jìn)行。在需要把許多新記錄插入某個數(shù)據(jù)表的場合，DELAY_KEY_WRITE選項(xiàng)的作用將非常明顯。另外，索引還會在硬盤上占用相當(dāng)大的空間。因此應(yīng)該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。注意，如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實(shí)際效果。從理論上講，完全可以為數(shù)據(jù)表里的每個字段分別建一個索引，但MySQL把同一個數(shù)據(jù)表里的索引總數(shù)限制為16個。

1．InnoDB數(shù)據(jù)表的索引

與InnoDB數(shù)據(jù)表相比，在InnoDB數(shù)據(jù)表上，索引對InnoDB數(shù)據(jù)表的重要性要大得多。在InnoDB數(shù)據(jù)表上，索引不僅會在搜索數(shù)據(jù)記錄時發(fā)揮作用，還是數(shù)據(jù)行級鎖定機(jī)制的苊、基礎(chǔ)?！皵?shù)據(jù)行級鎖定”的意思是指在事務(wù)操作的執(zhí)行過程中鎖定正在被處理的個別記錄，不讓其他用戶進(jìn)行訪問。這種鎖定將影響到（但不限于）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE命令以及INSERT、UPDATE和DELETE命令。出于效率方面的考慮，InnoDB數(shù)據(jù)表的數(shù)據(jù)行級鎖定實(shí)際發(fā)生在它們的索引上，而不是數(shù)據(jù)表自身上。顯然，數(shù)據(jù)行級鎖定機(jī)制只有在有關(guān)的數(shù)據(jù)表有一個合適的索引可供鎖定的時候才能發(fā)揮效力。

2．限制

如果WHERE子句的查詢條件里有不等號（WHEREcoloum!=），MySQL將無法使用索引。類似地，如果WHERE子句的查詢條件里使用了函數(shù)（WHEREDAY（column）=），MySQL也將無法使用索引。在JOIN操作中（需要從多個數(shù)據(jù)表提取數(shù)據(jù)時），MySQL只有在主鍵和外鍵的數(shù)據(jù)類型相同時才能使用索引。

如果WHERE子句的查詢條件里使用比較操作符LIKE和REGEXP，MySQL只有在搜索模板的第一個字符不是通配符的情況下才能使用索引。比如說，如果查詢條件是LIKE'abc%‘，MySQL將使用索引；如果查詢條件是LIKE'%abc’，MySQL將不使用索引。

在ORDERBY操作中，MySQL只有在排序條件不是一個查詢條件表達(dá)式的情況下才使用索引。（雖然如此，在涉及多個數(shù)據(jù)表查詢里，即使有索引可用，那些索引在加快ORDERBY方面也沒什么作用）。如果某個數(shù)據(jù)列里包含許多重復(fù)的值，就算為它建立了索引也不會有很好的效果。比如說，如果某個數(shù)據(jù)列里包含的凈是些諸如“0/1”或“Y/N”等值，就沒有必要為它創(chuàng)建一個索引。

技術(shù)分享 | 關(guān)于 MySQL 自增 ID 的事兒

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

下文以 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ò)容的問題。

mysql如何設(shè)置自增主鍵

1.主鍵語法

①創(chuàng)建時：create table sc (

studentno int,

courseid int,

score int,

primary key (studentno) );

②修改時：ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(列名);

前提是原先沒有設(shè)置主鍵。

2.外鍵語法

①創(chuàng)建時：create table sc (

studentno int,

courseid int,

score int,

foreign key (courseid) );

②修改時：

ALTER TABLE news_info[子表名] ADD CONSTRAINT FK_news_info_news_type[約束名] FOREIGN KEY (info_id)[子表列] REFERENCES news_type[主表名] (id)[主表列] ;

3.使用組合主鍵

如果一列不能唯一區(qū)分一個表里的記錄時，可以考慮多個列組合起來達(dá)到區(qū)分表記錄的唯一性，形式

①創(chuàng)建時：create table sc (

studentno int,

courseid int,

score int,

primary key (studentno,courseid) );

②修改時：alter table tb_name add primary key (字段1,字段2,字段3);

前提是原來表中沒有設(shè)置主鍵，若原先已有主鍵則會報(bào)錯。

mysql如何設(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)取資料