由一个关于auto_increment的奇怪案例看MySQL8的一些改进优化

最新推荐文章于 2025-05-29 06:57:34 发布

原创

最新推荐文章于 2025-05-29 06:57:34 发布 · 2.2k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #开源 #数据库 #运维 #版本优化

在MySQL8上偶然发现一个比较奇怪的问题，在使用alter table tab auto_increment=N修改表的自增初始值时，information_schema.tables这个表的auto_increment列并没有同步更新：

mysql> show create table test.incr_test\g
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                  |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| incr_test | CREATE TABLE `incr_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='incr_test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|            101 |
+----------------+
1 row in set (0.00 sec)

mysql> alter table test.incr_test auto_increment=120;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='incr_test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|            101 |
+----------------+
1 row in set (0.00 sec)

首先想到的是，数据在buffer没提交？于是重启一下MySQL，auto_increment列仍是没有变化：

[root@linux-base data]# service mysql restart
Shutting down MySQL...                                     [  OK  ]
Starting MySQL..                                           [  OK  ]
[root@linux-base data]# mysql
Welcome to the MySQL monitor.  Commands end wi