在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