如何在MYSQL 8中查找表的下一个AUTO_INCREMENT值

Question

我使用以下查询语句来查找MYSQL 5.7表的下一个AUTO_INCREMENT值：

SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TABLENAME'
AND TABLE_SCHEMA = DATABASE( ); 

但是，在MYSQL 8中，相同的查询返回NULL。我在我的应用程序中使用了上述查询，在MYSQL 5.7中可以正常工作，但在MYSQL 8中表现奇怪。

有人能建议如何在MYSQL 8中查找下一个AUTO_INCREMENT值吗？

此外，如果有一个通用的查询，可以在两个版本（MYSQL 5.7和8）中共同使用，那将非常有帮助。

- Rahul

---

我在 MYSQL 8 的文档中没有看到任何提示表格是否已更改。您确定该表格具有自动递增列吗？ - Barmar

请参见Bug #91038 AUTO_INCREMENT does not increase automatically。 - wchiquito

顺便说一句：您的代码不应该关心查找当前/下一个自增值。先插入行，然后从插入中找到分配的值。听起来您正在做一些在任何有意义的并发级别下都会出问题的事情。 - Michael - sqlbot

请查看5.1.7服务器系统变量::information_schema_stats_expiry。请检查dbfiddle。 - wchiquito

@wchiquito 非常感谢您指引我正确的方向。您的评论对我很有帮助。我正在寻找一种永久设置系统变量information_schema_stats_expiry值为0的方法，以便每次都从最新的自动增量号码获取，而不是从缓存中获取。我尝试使用SET GLOBAL INFORMATION_SCHEMA_STATS_EXPIRY = 0;来设置变量值，在那一天它起作用了，但第二天我感觉值又被设置回了原始值。我将尝试使用SET PERSIST INFORMATION_SCHEMA_STATS_EXPIRY = 0;来解决问题。 - Rahul

Answer 1

默认情况下，MySQL 8.0 只会每86400秒（也就是一天）刷新一次AUTO_INCREMENT的值。在获取AUTO_INCREMENT的值之前，可以执行以下语句手动刷新每个表的值，例如一个名为TABLENAME的表。

ANALYZE TABLE `TABLENAME`;

这个语句适用于MySQL 5.6、5.7和8.0。

然后，您可以像以前一样执行您的语句。

SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TABLENAME'
AND TABLE_SCHEMA = DATABASE( );

- CJ Dennis

Answer 2

尝试使用

ALTER TABLE %1
ADD COLUMN id BIGINT NOT NULL AUTO_INCREMENT UNIQUE FIRST;

创建列，然后查询。

SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TABLENAME'
AND TABLE_SCHEMA = DATABASE( );

应该返回正确的值。

- Adrian Karolewski

---

添加一个虚拟的第二个AUTO_INCREMENT列的理由是什么？ - Álvaro González

你的回答可以通过提供更多支持信息来改进。请编辑以添加进一步的细节，例如引用或文档，以便他人可以确认你的答案是正确的。您可以在帮助中心中找到有关如何编写良好答案的更多信息。 - Community