MySQL主鍵自增值為什么有“空洞”?

作者：小蝦米&蟹黃瓜子 2022-07-03 22:00:49

本文在測試 insert、insert ignore、replace into 三種數據插入方式的時候，發現插入數據的時候在表內存在帶有“唯一特性”的值重復的情況下三種語句的處理方式。最終發現了MySQL主鍵自增值“空洞”了

一.場景準備

測試場景為MySQL 8.0：

主鍵重復場景
唯一鍵重復場景

1、建表，包含主鍵及唯一約束

CREATE TABLE t1(
  id int(11) NOT NULL auto_increment,
  c1 varchar(64) DEFAULT NULL,
  c2 int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_c1 (c1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、寫入初始測試數據

insert into t1 (c1,c2) values ('a',1),('b',2),('c',3);

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
|  3 | c    |    3 |
+----+------+------+
3 rows in set (0.00 sec)

二.開始測試

insert into

# 測試主鍵重復
mysql> insert into t1 values (1,'aaa', 111);
ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'

# 測試唯一鍵重復
mysql> insert into t1 (c1,c2) values('a', 4);
ERROR 1062 (23000): Duplicate entry 'a' for key 't1.uk_c1'

insert ignore into

insert方式插入數據在處理過程中發生主鍵傳統等錯誤時候，語句會被終止，并告知錯誤的原因。而使用insert ignore的方式進行數據插入，則會忽略插入錯誤的行繼續插入沒有問題的行記錄，最終以warning進行提示。

# 測試主鍵重復
mysql> insert ignore into t1 values (1,'aaa', 111);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 't1.PRIMARY' |
+---------+------+------------------------------------------+
1 row in set (0.01 sec)

# 測試唯一鍵重復
mysql> insert ignore into t1 (c1,c2) values('a', 4);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1062 | Duplicate entry 'a' for key 't1.uk_c1' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

在測試過程中驚奇地發現測試表中的主鍵自增列發生了改變，經過之前的操作已經變成了7：

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
# 可是表的行數據并沒有增加
mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
|  3 | c    |    3 |
+----+------+------+
3 rows in set (0.00 sec)
# 新寫入一條數據后，自增 id 變成 7
mysql> insert into t1 (c1,c2) values('d', 4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
|  3 | c    |    3 |
|  7 | d    |    4 |
+----+------+------+
4 rows in set (0.00 sec)

replace into

最后，replace into的方式導致如果插入數據是原值的情況，然后主鍵沖突，就對該主鍵的內容進行替換，如果唯一鍵沖突，唯一值所在行就會刪除，重新插入新的行，如果都不沖突則正常插入數據。

# 測試主鍵重復
mysql> replace into t1 values (1,'aaa', 111);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | aaa  |  111 |
|  2 | b    |    2 |
|  3 | c    |    3 |
|  7 | d    |    4 |
+----+------+------+
4 rows in set (0.00 sec)

# 測試唯一鍵重復
mysql> replace into t1 (c1,c2) values('b', 4);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | aaa  |  111 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
+----+------+------+
4 rows in set (0.00 sec)

上文測試了三種插入數據的方式，可是測試過程中發現插入失敗的時候，自增列的自增值居然變大了。

三.問題分析

為了更好地理解，首先讓我們具體認識一下 AUTO_INCREMENT 屬性在不同的存儲引擎當中，其自增值的保存策略有所不同：

MyISAM引擎的自增值是保存在數據文件中的。
InnoDB引擎的自增值，其實是保存在了內存里，并且到了MySQL 8.0版本后，將自增值的變更記錄在了redo log中，當MySQL發生重啟的時候依靠redo log恢復重啟之前的自增值。在此前，現在該表的自增值是7，重啟后又變成4了。

可是理解了這個并不能馬上理解現在的這個問題，我們知道當數據進行數據插入的時候，如果插入的數據中自增列不指定其值的時候，該列就會以當前自增值作為其值，如果指定其值就會插入指定的值，當然也有滿足唯一的原則，同時插入指定值大于自增值時，自增值也會隨之改變。而自增值使用的算法是以 auto_increment_offset 參數決定開始，以 auto_increment_increment 決定步長來實現的,默認情況都是1：