設置自增列
專注于為中小企業提供網站設計制作、做網站服務,電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業哈巴河免費做網站提供優質的服務。我們立足成都,凝聚了一批互聯網行業人才,有力地推動了千余家企業的穩健成長,幫助中小企業通過網站建設實現規模擴充和轉變。
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的自增列在實際生產中應用的非常廣泛,相信各位所在的公司or團隊,MYSQL開發規范中一定會有要求盡量使用自增列去充當表的主鍵,為什么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參數,可以調整自增步長,該參數有session級跟global級,在分庫分表以及雙主or多主的模式下比較有用。
4.? 一個表上只能有一個自增列
5.? Mysql5.7及以下版本,innodb表的自增值保存在內存中,重啟后表的自增值會設為max(id)+1,而myisam引擎的自增值是保存在文件中,重啟不會丟失。Mysql8.0開始,innodb的自增id能持久化了,重啟mysql,自增ID不會丟。
首先:表中自增列的上限是根據自增列的字段類型來定的。
若設定了自增id充當主鍵,當達到了自增id的上限值時,會發生什么樣的事情呢?還是以上面創建的 t表為例, 先回顧它的表結構:
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,再插入兩行數據:
[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,這是達到了上限,還沒有超過,第二行數據插入時,則報出主鍵重復,在達到上限后,無法再分配新的更大的自增值,也沒有從1開始從頭分配,在這里表的auto_increment值會一直是2147483647。
對于寫入量大,且經常刪除數據的表,自增id設為int類型還是偏小的,所以我們為了避免出現自增id漲滿的情況,這邊統一建議自增id的類型設為unsigned bingint,這樣基本可以保障表的自增id是永遠夠用的。
這里內容比較多,innodb是索引組織表,所以涉及到索引的知識,但這不是本文的重點,我們快速回顧索引知識:
1.? Innodb索引分為主鍵跟輔助索引,主鍵即全表,輔助索引葉子節點保存主鍵的值,而主鍵的葉子節點保存數據行,中間節點存著葉子節點的路由值。
2.? Innodb存儲數據(索引)的單位是頁,這里默認是16K,這也意味著,數據本身越小,一個頁中能存數據的量越多,而檢索效率不僅僅由索引的層數來決定,更是由一次能夠緩存的數據量來定,也就是說數據本身越小,則一次IO能夠提取到緩沖區的數據越多(OS每次IO的量是固定的4K),查詢的效率越好。
其實能夠理解索引的結構及索引寫入插入、更新的原理,則自然就明白為何建議使用自增id。這里我直接列出使用自增id 當主鍵的好處吧:
1.? 順序寫入,避免了葉的分裂,數據寫入效率好
2.? 縮小了表的體積,特別是相比于UUID當主鍵,甚至組合字段當主鍵時,效果更明顯
3.? 查詢效率好,原因就是我上面說到索引知識的第二點。
4.? 某些情況下,我們可以利用自增id來統計大表的大致行數。
5.? 在數據歸檔or垃圾數據清理時,也可方便的利用這個id去操作,效率高。
容易出現不連續的id
有的同志會發現,自己的表中id值存在空洞,如類似于1、2、3、8、9、10這樣,有的適合有想依賴于自增id的連續性來實現業務邏輯,所以會想方設法去修改id讓其變的連續,其實,這是沒有必要的,這一塊的業務邏輯交由MySQL實現是很不理智的,表的記錄小還好,要是表的數據量很大,修改起來就糟糕了。那么,為什么自增id會容易出現空洞呢?
自增id的修改機制如下:
在MySQL里面,如果字段id被定義為AUTO_INCREMENT,在插入一行數據的時候,自增值的行為如下:
1. 如果插入數據時id字段指定為0、null 或未指定值,那么就把這個表當前的
AUTO_INCREMENT值填到自增字段;
2. 如果插入數據時id字段指定了具體的值,就直接使用語句里指定的值。
根據要插入的值和當前自增值的大小關系,自增值的變更結果也會有所不同。假設,某次要插入的值是X,當前的自增值是Y。
1. 如果XY,那么這個表的自增值不變;
2. 如果X≥Y,就需要把當前自增值修改為 新的自增值 。
新的自增值生成算法是:從auto_increment_offset開始,以auto_increment_increment為步長,持續疊加,直到找到第一個大于X的值,作為新的自增值。
Insert、update、delete操作會讓id不連續。
Delete、update:刪除中間數據,會造成空動,而修改自增id值,也會造成空洞(這個很少)。
Insert:插入報錯(唯一鍵沖突與事務回滾),會造成空洞,因為這時候自增id已經分配出去了,新的自增值已經生成,如下面例子:
[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已經回不到從前啦,唯一鍵沖突也是這樣的,這里就不做測試了。
在批量插入時(insert select等),也存在空洞的問題。看下面實驗:
[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了,再插入數據,即產生了空洞,這里是由mysql申請自增值的機制所造成的,MySQL在批量插入時,若一個值申請一個id,效率太慢,影響了批量插入的速度,故mysql采用下面的策略批量申請id。
1.? 語句執行過程中,第一次申請自增id,會分配1個;
2.? 1個用完以后,這個語句第二次申請自增id,會分配2個;
3.? 2個用完以后,還是這個語句,第三次申請自增id,會分配4個;
4.? 依此類推,同一個語句去申請自增id,每次申請到的自增id個數都是上一次的兩倍。
在對自增列進行操作時,存在著自增鎖,mysql的innodb_autoinc_lock_mode參數控制著自增鎖的上鎖機制。該參數有0、1、2三種模式:
0:語句執行結束后釋放自增鎖,MySQL5.0時采用這種模式,并發度較低。
1:mysql的默認設置。普通的insert語句申請后立馬釋放,insert select、replace insert、load data等批量插入語句要等語句執行結束后才釋放,并發讀得到提升
2:所有的語句都是申請后立馬釋放,并發度大大提升!但是在binlog為statement格式時,主從數據會發生不一致。這一塊網上有很多介紹,我不做介紹了。
在徹底了解了MYSQL的自增機制以后,在實際生產中就能靈活避坑,這里建議不要用自增id值去當表的行數,當需要對大表準確統計行數時,可以去count(*)從庫,如果業務很依賴大表的準確行數,直接弄個中間表來統計,或者考慮要不要用mysql的innodb來存儲數據,這個是需要自己去權衡。另外對于要求很高的寫入性能,但寫入量又比較大的業務,自增id的使用依然存在熱點寫入的問題,存在性能瓶頸,這時候可通過分庫分表來解決。
#1、查看現在mysql自增id的配置
show variables like '%increment%';
#2、下面步長為2的增長
+-------------------------------+-------+
| Variable_name???????????????? | Value |
+-------------------------------+-------+
|?auto_increment_increment????? | 2 ?? |
| auto_increment_offset???????? | 1???? |
| div_precision_increment?????? | 4???? |
| innodb_autoextend_increment?? | 8???? |
| ndb_autoincrement_prefetch_sz | 32??? |
+-------------------------------+-------+
5 rows in set (0.01 sec)
#3、解決
set auto_increment_increment = 1和set @@auto_increment_increment = 1
如果想永久性的改回為1,在my.cnf or my.ini中查找這個field,然后修改,然后restart mysql.
本文題目:mysql自增怎么解決 mysql數據自增
分享路徑:http://m.newbst.com/article14/hihjge.html
成都網站建設公司_創新互聯,為您提供定制開發、品牌網站制作、商城網站、響應式網站、外貿建站、網站設計公司
聲明:本網站發布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網站立場,如需處理請聯系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創新互聯