當前位置:首頁 » 編程語言 » mysql自增idsql
擴展閱讀
webinf下怎麼引入js 2023-08-31 21:54:13
堡壘機怎麼打開web 2023-08-31 21:54:11

mysql自增idsql

發布時間: 2022-09-24 11:40:49

㈠ 技術分享 | 關於 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精英俱樂部真誠為您解答!