information_schema.TABLES无法实时更新数据

问题：information_schema.TABLES表的AUTO_INCREMENT字段更新不及时，无法实时获取数据

使用下面的SQL语句查询 tb_account 表的 AUTO_INCREMENT 自增值。

SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_transportation'
AND TABLE_NAME = 'tb_account';

得到结果如下

+----------------+
| AUTO_INCREMENT |
+----------------+
|             30 |
+----------------+
1 row in set (0.00 sec)

得知，tb_account 表 现在的自增值是30。

然后，修改这个表的自增值为50。

ALTER TABLE `tb_account` AUTO_INCREMENT = 50;

执行成功

mysql> ALTER TABLE `tb_account` AUTO_INCREMENT = 50;
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

此时再次查询表自增值

mysql> SELECT AUTO_INCREMENT FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA = 'db_transportation'
    -> AND TABLE_NAME = 'tb_account';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             30 |
+----------------+
1 row in set (0.00 sec)

结果仍是30不变。

但查询表结构如下，发现其实 AUTO_INCREMENT 的值已经成功修改为50，只是 information_schema.TABLES 表没有实时同步这个信息。

mysql> SHOW CREATE TABLE tb_account;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_account | CREATE TABLE `tb_account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `system_code` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `system` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `username` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

解决方法

在查询 AUTO_INCREMENT 自增值之前先主动触发一次 information_schema 的信息收集。

使用如下SQL语句主动触发信息收集。

ANALYZE TABLE `tb_account`;

+------------------------------+---------+----------+----------+
| Table                        | Op      | Msg_type | Msg_text |
+------------------------------+---------+----------+----------+
| db_transportation.tb_account | analyze | status   | OK       |
+------------------------------+---------+----------+----------+
1 row in set (0.02 sec)

再次执行查询，即可获得最新的数据结果。

mysql> SELECT AUTO_INCREMENT FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA = 'db_transportation'
    -> AND TABLE_NAME = 'tb_account';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             50 |
+----------------+
1 row in set (0.00 sec)

原因解析

MySQL 8.0版本中的 information_schema.TABLES 表已经变为视图，而不再是具体的引擎表。具体的统计信息存储在 mysql.table_stats 表中，而这些统计信息的更新并不是实时的，而是依赖于数据字典的缓存机制。

解决这一问题的方法是手动触发信息收集，可以通过执行 ANALYZE TABLE 语句或者设置information_schema_stats_expiry参数为0来强制刷新字典对象缓存，确保查询结果是最新的。

总体而言，MySQL 8.0版本引入了数据字典的改进，将元数据存储从文件（如.frm）转移到了数据字典表，提高了查询效率。然而，用户在使用information_schema表查询元数据时需要注意缓存机制，以确保获取到的信息是准确的。