Auto_increment詳解

来源:http://www.cnblogs.com/pangduzi/archive/2016/08/21/5792560.html
-Advertisement-
Play Games

Auto_increment Mysql AUTO_INCREMENT 1.Innodb表的自動增長列可以手工插入，但是插入的值如果是空或者0，則實際插入的將是自動增長後的值 2.可以通過alter table t1 auto_incremenrt=n 語句強制設置自動增長列的初始值，預設從1開始， ...


Auto_increment

Mysql AUTO_INCREMENT

1.Innodb表的自動增長列可以手工插入，但是插入的值如果是空或者0，則實際插入的將是自動增長後的值
mysql> create table t1(id int not null auto_increment primary key,name varchar(10));
Query OK, 0 rows affected (0.06 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into t1 values(0,'fanboshi');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(null,'duyalan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | fanboshi |
|  2 | duyalan  |
+----+----------+
2 rows in set (0.00 sec)
2.可以通過alter table t1 auto_incremenrt=n 語句強制設置自動增長列的初始值，預設從1開始，但是該強制的預設值是保留在記憶體中的，如果該值在使用之前資料庫重新啟動，那麼這個強制的預設值就會丟失，就需要資料庫啟動後重新設置
mysql> alter table t1 auto_increment=5;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t1 values(null,'handudu');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | fanboshi |
|  2 | duyalan  |
|  5 | handudu  |
+----+----------+
3 rows in set (0.00 sec)
3.可以是用last_insert_id()查詢當前線程最後插入記錄使用的值。如果一次插入多條記錄，那麼返回的是第一條記錄使用的自動增長值。
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)
註意last_insert_id()是所有表auto_increment的最新插入值，
因此在併發的情況下，獲取某表的最新插入auto_increment可能出現錯誤
4.對於innodb表，自動增長列必須是索引，且必須是組合索引的第一列，且一個表只能有一個auto_increment屬性。

mysql> create table t2(id int not null auto_increment,name varchar(10));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

非主鍵
mysql> create table t2(id int not null auto_increment,name varchar(10),index(id));
Query OK, 0 rows affected (0.09 sec)

mysql> mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          1 | id       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          1 | id       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

不是主鍵，只是有索引
mysql> insert into t2 values(1,'fan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(2,'fan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | fan  |
|  2 | fan  |
+----+------+
2 rows in set (0.00 sec)

如果是組合索引，也必須是組合索引的第一列
mysql> create table t3(id1 int not null auto_increment,id2 int,name varchar(10),index(id2,id1));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

但是對於MyISAM表，自動增長列可以使組合索引的其他列，這樣插入記錄後，自動增長列是按照組合索引的前面幾列進行排序後遞增的。

mysql> create table t3_myisam(id1 int not null auto_increment,id2 int,name varchar(10),index(id2,id1)) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t3_myisam(id2,name) values(3,'fanboshi'),(1,'duyalan'),(1,'daduzi'),(2,'fan'),(5,'hehe'),(6,'keke');
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t3_myisam;
+-----+------+----------+
| id1 | id2  | name     |
+-----+------+----------+
|   1 |    3 | fanboshi |
|   1 |    1 | duyalan  |
|   2 |    1 | daduzi   |
|   1 |    2 | fan      |
|   1 |    5 | hehe     |
|   1 |    6 | keke     |
+-----+------+----------+
6 rows in set (0.00 sec)
好像看不出啥規律
再插入一次
mysql> insert into t3_myisam(id2,name) values(3,'fanboshi'),(1,'duyalan'),(1,'daduzi'),(2,'fan'),(5,'hehe'),(6,'keke');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> select * from t3_myisam order by id2,id1;
+-----+------+----------+
| id1 | id2  | name     |
+-----+------+----------+
|   1 |    1 | duyalan  |
|   2 |    1 | daduzi   |
|   3 |    1 | duyalan  |
|   4 |    1 | daduzi   |
|   1 |    2 | fan      |
|   2 |    2 | fan      |
|   1 |    3 | fanboshi |
|   2 |    3 | fanboshi |
|   1 |    5 | hehe     |
|   2 |    5 | hehe     |
|   1 |    6 | keke     |
|   2 |    6 | keke     |
+-----+------+----------+
12 rows in set (0.00 sec)
id2=1有四個，所以id1有1,2,3,4
id2=2有倆，id1=1,2
自動增長列id1作為組合索引的第二列，對該表插入一些記錄後，可以發現自動增長列是按照組合索引第一列id2進行排序後分組遞增的

5.MyISAM 及INNODB表，表中auto_increment最大值被刪除，將不會被重用。就是說會跳號

mysql> insert into t1(name) values('hehe');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | fanboshi |
|  2 | duyalan  |
|  5 | handudu  |
|  6 | hehe     |
+----+----------+
4 rows in set (0.00 sec)

mysql> delete from t1 where id=6;
Query OK, 1 row affected (0.08 sec)

mysql> insert into t1(name) values('keke');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | fanboshi |
|  2 | duyalan  |
|  5 | handudu  |
|  7 | keke     |
+----+----------+
4 rows in set (0.00 sec)
6.用"WHERE auto_col IS NULL"條件選擇出新插入的行，即在INSERT後馬上用:

SELECT * FROM t4 WHERE id IS NULL;
選擇出來的將是新插入的行，而非真正的滿足"id IS NULL"條件的行。
但你要是再執行一次上述查詢，則返回的又變成了真正的滿足"a IS NULL"條件的行，
由於a是主鍵，因此肯定會返回空集。這看上去很詭異是嗎，不過MySQL也不想這麼乾，為了支持 ODBC標準
不過可以將SQL_AUTO_IS_NULL設為0來禁止這一用法。
此方法獲取last_insert_id不推薦

mysql> insert into t1(name) values('new');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 where id is null;
Empty set (0.00 sec)

mysql> show variables like 'sql_auto_is_null';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_auto_is_null | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

mysql> set session sql_auto_is_null=on;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'sql_auto_is_null';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_auto_is_null | ON    |
+------------------+-------+
1 row in set (0.00 sec)

mysql> select * from t1 where id is null;
+----+------+
| id | name |
+----+------+
|  8 | new  |
+----+------+
1 row in set (0.01 sec)

mysql> select * from t1 where id is null;
Empty set (0.00 sec)
7.AUTO_INCREMENT屬性也給複製帶來了麻煩。一般情況下複製AUTO_INCREMENT屬性能正確工作，但以下情況還是有問題：
  1. INSERT DELAYED ... VALUES(LAST_INSERT_ID())不能被正確複製
  2. 存儲過程插入的使用AUTO_INCREMENT屬性的記錄不能被正確複製
  3. 通過"ALTER TABLE"命令增加AUTO_INCREMENT屬性時在主從節點上產生的值可能是不一樣的，因為這個各行AUTO_INCREMENT屬性的值取決於物理上的存儲順序。
8.對於replication的master-master方式 為防止auto_increment欄位的重覆，可做如下設置

A伺服器的my.cnf設置如下：

auto_increment_offset = 1
auto_increment_increment = 2

這樣A的auto_increment欄位產生的數值是：1, 3, 5, 7, ...

B伺服器的my.cnf設置如下：

auto_increment_offset = 2
auto_increment_increment = 2

這樣B的auto_increment欄位產生的數值是：2, 4, 6, 8, ...

8.根據官方的說明：If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. （如果auto_increment_offset的值大於auto_increment_increment的值，則auto_increment_offset的值會被忽略）

mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> set session auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 5     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> create table t5 like t1;
Query OK, 0 rows affected (0.07 sec)

mysql> desc t5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into t5(name) values('fanboshi');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
+----+----------+
| id | name     |
+----+----------+
|  1 | fanboshi |
+----+----------+
1 row in set (0.00 sec)

mysql> set session auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t5(name) values('duyalan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5(name) values('heheda');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t5;
+----+----------+
| id | name     |
+----+----------+
|  1 | fanboshi |
|  5 | duyalan  |
| 10 | heheda   |
+----+----------+
3 rows in set (0.00 sec)

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • MySQL 全量及增量備份，基於xtrabackup 2.3.5
    昨天一個pgp加密盤壞了，東西全丟了。。。，我的筆記啊！我咋怎麼2呢。。 備份個腳本吧，方便以後手機查看，話說我都快忘記帳號密碼了 備註：腳步恢復時只能恢復到備份前的狀態，如果需要恢復到當前狀態，還需配合binlog，睡覺就這樣 ...
  • mysql 主從複製 實踐
    非同步主從複製 主從部署步驟: 備份還原 使用mysqldump或者xtrabackup 把主庫現有基礎數據還原到從庫 授權 grant replication slave on *.* 給從庫一個複製binlog的賬號 配置複製，並啟動 從庫上配置複製信息，並指向master 查看主從複製信息 sh ...
  • HDP2.4安裝(六)：小結與回顧
    基於Centos7安裝過程中常用工具及操作技術總結回顧。 操作技巧： tab鍵，命令自動補全 xshell 預設： Ctrl + Insert （複製） Shift + Insert (粘貼） systemctl 服務操作命令： systemctl start postfix.service 動一個 ...
  • C#操作MongoDB入門
    聲明：此文是我學習MongoDB一周以來的經驗總結。一來給自己做一個備份，二來給和我一樣剛接觸MongoDB的小白分享一下，有什麼問題，給位請多擔待。 1、MongoDB安裝及配置 （1）下載： mongodb官網 https://www.mongodb.com/download-center 進入 ...
  • 大話存儲 3 - 七種磁碟RAID技術
    RAID技術 Redundant Array of Independent Disks由獨立的磁碟組成的具有冗餘特性的陣列。有兩個特性：陣列：需要很多磁碟來組成冗餘：允許某塊磁碟損壞之後，數據仍然可用目前，單塊磁碟容量只有幾T，對於現代應用程式來說遠遠不夠。為了提供容量更大的磁碟，發明瞭RAID技術... ...
  • hadoop(三):hdfs 機架感知
    client 向 Active NN 發送寫請求時，NN為這些數據分配DN地址，HDFS文件塊副本的放置對於系統整體的可靠性和性能有關鍵性影響。一個簡單但非優化的副本放置策略是，把副本分別放在不同機架，甚至不同IDC，這樣可以防止整個機架、甚至整個IDC崩潰帶來的錯誤，但是這樣文件寫必須在多個機架之 ...
  • mysql 主從複製原理
    主從形式 mysql主從複製 靈活 一主一從 主主複製 一主多從 擴展系統讀取的性能，因為讀是在從庫讀取的； 多主一從 5.7開始支持 聯級複製 用途及條件 mysql主從複製用途 實時災備，用於故障切換 讀寫分離，提供查詢服務 備份，避免影響業務 主從部署必要條件： 主庫開啟binlog日誌（設置 ...
  • hadoop(二):hdfs HA原理及配置
    早期的hadoop版本，NN是HDFS集群的單點故障點，每一個集群只有一個NN,如果這個機器或進程不可用，整個集群就無法使用。為瞭解決這個問題，出現了一堆針對HDFS HA的解決方案（如：Linux HA, VMware FT, shared NAS+NFS, BookKeeper, QJM/Quo ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類