【无标题】

最新推荐文章于 2023-08-02 07:50:36 发布

原创

最新推荐文章于 2023-08-02 07:50:36 发布 · 308 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#servlet #java #前端 #spring boot #后端

本文探讨了在测试MySQL数据插入时，包括insert、insert ignore和replace into三种方式，遇到主键或唯一键重复情况下的处理。在测试过程中发现，即使插入失败，主键自增值仍会增加，导致自增列出现“空洞”。分析了MyISAM和InnoDB引擎中自增值的保存策略，解释了为何自增值会变大。此外，文章还提到在事务回滚后自增值不变的问题，以及在生产环境中可能出现的类似问题。

* 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_INCREMENT,
  `c1` varchar(64) D