㈠ 技術分享 | 關於 Mysql 自增 ID 的事兒
當我們使用 MySQL 進行數據存儲時,一般會為一張表設置一個自增主鍵,當有數據行插入時,該主鍵欄位則會根據步長與偏移量增長(默認每次+1)。
下文以 Innodb 引擎為主進行介紹,使用自增主鍵的好處有很多,如:索引空間佔比小、范圍查詢與排序都友好、避免像 UUID 這樣隨機字元串帶來的頁分裂問題等...
當我們對該表設置了自增主鍵之後,則會在該表上產生一個計數器,用於為自增列分配 ID 。
自增的值並不是保存在表結構信息內的,對於不同的版本它們有如下的區別:
計數器的值存儲在內存中的,重啟後丟棄,下一次將讀取最大的一個自增ID往後繼續發號。
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
計數器的值將會持久化到磁碟。在每次發號時都將寫入 Redolog ,並在每個 Checkpoint 都進行保存,重啟時候使用 Redolog 恢復重啟之前的值。
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
可以預先確定插入行數的語句(像簡單 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自增
如何在MYSQL插數據ID自增的方法。
如下參考:
1.在添加欄位之前,第一個應該首先檢查當前tb1表的結構,如下圖所示。
注意事項:
MySQL使用的SQL語言是訪問資料庫最常用的標准語言。MySQL軟體採用雙重許可政策,分為社區版,商業版,由於其體積小、速度快、總體擁有成本低,尤其是開放源碼這一特性,一般中小網站開發選擇MySQL作為資料庫。
㈢ 怎麼在mySQL中設置自增id
設表名為t執行SQL語句alter table t auto_increment=1;
㈣ mysql建議自增id
mysql innodb索引的B+Tree圖
page為頁,默認為16
Mysql在使用中,一般都是建議使用自增id,為什麼呢
㈤ Mysql資料庫中,設置id為自動增加,向資料庫中插入數據時,SQL語句怎麼寫
在建立表的時候設置id為自動增長的
[id]
[int]
IDENTITY
(1,
1)
SQL語句是insert
into
user(name,passwd)
values
(name
,passwd)。新增一條數據
id
就會自動加1
INSERT
INTO是sql資料庫中的語句,可以用於向表格中插入新的行。
(5)mysql自增idsql擴展閱讀
(1)
數據記錄篩選:
sql="select
*
from
數據表
where欄位名=欄位值
order
by欄位名[desc]"(按某個欄位值降序排列。默認升序ASC)
sql="select
*
from
數據表
where欄位名like
'%欄位值%'
order
by
欄位名
[desc]"
sql="select
top
10
*
from
數據表
where欄位名=欄位值
order
by
欄位名
[desc]"
sql="select
top
10
*
from
數據表
order
by
欄位名
[desc]"
sql="select
*
from
數據表
where欄位名in
('值1','值2','值3')"
sql="select
*
from
數據表
where欄位名between
值1
and
值2"
(2)
更新數據記錄:
sql="update
數據表
set欄位名=欄位值
where
條件表達式"
sql="update
數據表
set
欄位1=值1,欄位2=值2
……
欄位n=值n
where
條件表達式"
(3)
刪除數據記錄:
sql="delete
from
數據表
where
條件表達式"
sql="delete
from
數據表"
(將數據表所有記錄刪除)
(4)
添加數據記錄:
sql="insert
into
數據表
(欄位1,欄位2,欄位3
…)
values
(值1,值2,值3
…)"
sql="insert
into
目標數據表
select
*
from
源數據表"
(把源數據表的記錄添加到目標數據表)
(5)
數據記錄統計函數:
AVG(欄位名)
得出一個表格欄平均值
COUNT(*;欄位名)
對數據行數的統計或對某一欄有值的數據行數統計
MAX(欄位名)
取得一個表格欄最大的值
MIN(欄位名)
取得一個表格欄最小的值
SUM(欄位名)
把數據欄的值相加
引用以上函數的方法:
sql="select
sum(欄位名)
as
別名
from
數據表
where
條件表達式"
set
rs=conn.excute(sql)
用
rs("別名")
獲取統計的值,其它函數運用同上。
查詢去除重復值:select
distinct
*
from
table1
(6)
數據表的建立和刪除:
CREATE
TABLE
數據表名稱(欄位1
類型1(長度),欄位2
類型2(長度)
……
)
(7)
單列求和:
SELECT
SUM(欄位名)
FROM
數據表
參考資料——搜狗網路SQL
insert
into
㈥ 怎麼在MySql里添加一個自動增長的ID欄位
以插入語句的方法來解決此問題。
如下參考:
1.在添加欄位之前,第一個zd應該首先檢查tb1表的當前結構。
注意事項:
MySQL使用的SQL語言是訪問資料庫最常用的標准化語言。MySQL軟體採用雙重許可政策,分為社區版和商業版,由於其體積小,速度快,整體擁有成本低,尤其是開源這一特點,一般中小型網站開發都選擇MySQL作為網站資料庫。
㈦ mysql自增id列怎麼設置
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資料庫中,設置id為自動增加,向資料庫中插入數據時,SQL語句怎麼寫
在建立表的時候設置id為自動增長的 [id] [int] IDENTITY (1, 1)
SQL語句是insert into user(name,passwd) values (name ,passwd)。新增一條數據 id 就會自動加1
INSERT INTO是sql資料庫中的語句,可以用於向表格中插入新的行。
(8)mysql自增idsql擴展閱讀
(1) 數據記錄篩選:
sql="select * from 數據表 where欄位名=欄位值 order by欄位名[desc]"(按某個欄位值降序排列。默認升序ASC)
sql="select * from 數據表 where欄位名like '%欄位值%' order by 欄位名 [desc]"
sql="select top 10 * from 數據表 where欄位名=欄位值 order by 欄位名 [desc]"
sql="select top 10 * from 數據表 order by 欄位名 [desc]"
sql="select * from 數據表 where欄位名in ('值1','值2','值3')"
sql="select * from 數據表 where欄位名between 值1 and 值2"
(2) 更新數據記錄:
sql="update 數據表 set欄位名=欄位值 where 條件表達式"
sql="update 數據表 set 欄位1=值1,欄位2=值2 …… 欄位n=值n where 條件表達式"
(3) 刪除數據記錄:
sql="delete from 數據表 where 條件表達式"
sql="delete from 數據表" (將數據表所有記錄刪除)
(4) 添加數據記錄:
sql="insert into 數據表 (欄位1,欄位2,欄位3 …) values (值1,值2,值3 …)"
sql="insert into 目標數據表 select * from 源數據表" (把源數據表的記錄添加到目標數據表)
(5) 數據記錄統計函數:
AVG(欄位名) 得出一個表格欄平均值
COUNT(*;欄位名) 對數據行數的統計或對某一欄有值的數據行數統計
MAX(欄位名) 取得一個表格欄最大的值
MIN(欄位名) 取得一個表格欄最小的值
SUM(欄位名) 把數據欄的值相加
引用以上函數的方法:
sql="select sum(欄位名) as 別名 from 數據表 where 條件表達式"
set rs=conn.excute(sql)
用 rs("別名") 獲取統計的值,其它函數運用同上。
查詢去除重復值:select distinct * from table1
(6) 數據表的建立和刪除:
CREATE TABLE 數據表名稱(欄位1 類型1(長度),欄位2 類型2(長度) …… )
(7) 單列求和:
SELECT SUM(欄位名) FROM 數據表
㈨ 一文讓你徹底弄懂MySQL自增列
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. 如果X<Y,那麼這個表的自增值不變;
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的使用依然存在熱點寫入的問題,存在性能瓶頸,這時候可通過分庫分表來解決。
㈩ id自增 sql語句怎麼寫
sql="select
top
條數
欄位名
from
表名
where
id>某id值
order
by
id
asc"
因為自增的所以id是有規律可循的,只要選大於當前id的值就可以了,條數根據你的需要自己設定就可以了!
希望對你的問題有所幫助,如果還有什麼疑問可以hi在線咨詢,it精英俱樂部真誠為您解答!