mysql表不手动刷新无法写入,MySQL不会更新information_schema，除非我手动运行ANALYZE TABLE`myTable`...-优快云博客

当尝试获取MySQL中InnoDB表的 AUTO_INCREMENT 值时，发现information_schema表并未实时更新。必须手动执行ANALYZE TABLE来刷新。此问题与innodb_stats_on_metadata和innodb_stats_persistent变量有关。虽然强制执行ANALYZE可能影响性能，但没有直接的“修复”方法。通常建议使用MAX()函数获取auto_increment列的最大值，并依赖LAST_INSERT_ID()获取新插入行的值。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

I have the need to get last id (primary key) of a table (InnoDB), and to do so I perform the following query:

SELECT (SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'mySchema' AND `TABLE_NAME` = 'myTable') - 1;

which returns the wrong AUTO_INCREMENT. The problem is the TABLES table of information_schema is not updated with the current value, unless I run the following query:

ANALYZE TABLE `myTable`;

Why doesn't MySQL update information_schema automatically, and how could I fix this behavior?

Running MySQL Server 8.0.13 X64.

解决方案

Q: Why doesn't MySQL update information_schema automatically, and how could I fix this behavior?

A: InnoDB holds the auto_increment value in memory, and doesn't persist that to disk.

Behavior of metadata queries (e.g. SHOW TABLE STATUS) is influenced by setting of innodb_stats_on_metadata and innodb_stats_persistent variables.

Forcing an ANALYZE everytime we query metadata can be a drain on performance.

Other than the settings of those variables, or forcing statistics to be collected by manually executing the ANALYZE TABLE, I don't think there's a "fix" for the issue.

(I think that mostly because I don't think it's a problem that needs to be fixed.)

To get the highest value of an auto_increment column in a table, the normative pattern is:

SELECT MAX(`ai_col`) FROM `myschema`.`mytable`

What puzzles me is why we need to retrieve this particular piece of information. What are we going to use it for?

Certainly, we aren't going to use that in application code to determine a value that was assigned to a row we just inserted. There's no guarantee that the highest value isn't from a row that was inserted by some other session. And we have LAST_INSERT_ID() mechanism to retrieve the value of a row our session just inserted.

If we go with the ANALYZE TABLE to refresh statistics, there's still a small some time between that and a subsequent SELECT... another session could slip in another INSERT so that the value we get from the gather stats could be "out of date" by the time we retrieve it.