mysql主键自增列到底怎么玩？

程序猿读历史 2021-05-31

581

mysq有2个表，分别是InnoDB引擎和MyISAM引擎，这2个表只有两列，第一列id是自增主键列，第二列name字符串类型，当insert了5条记录之后，删除了id等于4和5的记录。此时分别对这2个表插入一条，自增列的值会是什么呢？

实验过程如下：

数据库版本：

root@localhost [test]>select version();
+------------+
| version()  |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)
复制

建表语句：

CREATE TABLE `t_test_innodb_01` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `t_test_myisam_01` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
复制

分别插入5条数据，再删除id等于4和5的记录：

insert into t_test_innodb_01 (name) values('a'),('b'),('c'),('d'),('e');
insert into t_test_myisam_01 (name) values('a'),('b'),('c'),('d'),('e');
delete from t_test_innodb_01 where id in (4,5);
delete from t_test_myisam_01 where id in (4,5);
复制

此时查询2个表应该只有1、2、3三条记录：

root@localhost [test]>select * from t_test_innodb_01;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)


root@localhost [test]>select * from t_test_myisam_01;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)
复制

重启数据库：

[root@192-168-0-171 ~]# service mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL...... SUCCESS!
[root@192-168-0-171 ~]#
复制

再对2个表分别插入1条数据

root@localhost [test]>
root@localhost [test]>insert into t_test_innodb_01 (name) values('aaa');
Query OK, 1 row affected (0.02 sec)


root@localhost [test]>insert into t_test_myisam_01 (name) values('aaa');
Query OK, 1 row affected (0.00 sec)
复制

此时再查询，发现myisam的引擎的表id是6，而innodb引擎的表是4：

root@localhost [test]>select * from t_test_myisam_01;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  6 | aaa  |
+----+------+
4 rows in set (0.00 sec)




root@localhost [test]>select * from t_test_innodb_01;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | aaa  |
+----+------+
4 rows in set (0.00 sec)
复制

结论：

mysql5.7.23版本下，MyISAM和InnoDB的表，因为存储引擎对于自增列的实现机制不同，自增值也可能会有所不同，对于InnoDB存储引擎的表，ID是按照max(id)+1的算法来计算的。

原因：

这是因为InnoDB存储引擎中，自增主键没有持久化，而是放在内存中，关于自增主键的分配，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会通过SELECT MAX(ID) FROM TEST FOR UPDATE这样的SQL语句来初始化（不同表对应不同的SQL语句）.

其实这是一个bug来着, 官方说明地址为：

https://bugs.mysql.com/bug.php?id=199。

对应与MySIAM存储引擎，自增主键的最大值存放在数据文件当中，每次重启MySQL服务都不会影响其值变化。

问题：

mysql最新的8.0版本是否解决了该bug呢？敬请期待下文。

mysql

文章转载自程序猿读历史，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

mysql主键自增列到底怎么玩？

评论