【无标题】

*   G r e a t S Q L 社 区 原 创 内 容 未 经 授 权 不 得 随 意 使 用 ， 转 载 请 联 系 小 编 并 注 明 来 源 。

• 1.场景准备

• 2.开始测试

• 3.问题分析

• 4.问题拓展

本文在测试 insert 、 insert ignore 、 replace into 三种数据插入方式的时候，发现插入数据的时候在表内存在带有“唯一特性”的值重复的情况下三种语句的处理方式。最终发现了MySQL主键自增值“空洞”了

1.场景准备

测试场景为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)

2.开始测试

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_INC