Setting and Retrieving AUTO_INCREMENT Values in MySQL
2023-05-18 09:31 (1 years ago)
ytyng
When setting or retrieving the auto_increment of a table in MySQL, starting from MySQL 8, the information_schema is cached. This means that when you retrieve AUTO_INCREMENT consecutively, you might get an outdated value. Here are some countermeasures for this issue.
Setting
ALTER TABLE my_table_name AUTO_INCREMENT = 1;Retrieving
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE TABLE_NAME = 'my_table_name';However, there are instances where you might retrieve a cached value. You can address this using one of the following methods:
Solution A: Disable the cache of information_schema within the session
SET information_schema_stats_expiry = 0;Solution B: Forcefully update the cache of information_schema for the table
ANALYZE TABLE my_table_name;
Comments