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_c1insert 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)

在测试过程中惊奇地发现测试表中的主键自增列发生了改变，经过之前的b2b供应网主增值操作已经变成了7：

mysql> show create table t1\G

很赞哦!（4636）