---
slug: "mysql-auto-increment-refresh-information-schema-caching"
title: "Setting and Retrieving AUTO_INCREMENT Values in MySQL"
description: "When setting or retrieving the auto_increment value for a table in MySQL, starting from MySQL 8, information_schema is cached. As a result, retrieving AUTO_INCREMENT consecutively may yield outdated values. This article discusses countermeasures for such situations."
url: "https://www.ytyng.com/en/blog/mysql-auto-increment-refresh-information-schema-caching"
publish_date: "2023-05-18T00:31:20Z"
created: "2023-05-18T00:31:20Z"
updated: "2026-02-27T11:47:58.367Z"
categories: []
keywords: ""
featured_image_url: "https://media.ytyng.com/resize/20250602/63a966f5a1fb4f698fc889c05370b31b.png.webp?width=768"
has_video: true
has_music: true
video_urls: ["https://media.ytyng.net/ytyng-blog/283/featured-video-1.mp4", "https://media.ytyng.net/ytyng-blog/283/featured-video-2.mp4", "https://media.ytyng.net/ytyng-blog/283/featured-video-3.mp4"]
music_urls: ["https://media.ytyng.net/ytyng-blog/283/featured-music-283-4.mp3", "https://media.ytyng.net/ytyng-blog/283/featured-music-283-5.mp3"]
lang: "en"
---

# Setting and Retrieving AUTO_INCREMENT Values in MySQL

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
```mysql
ALTER TABLE my_table_name AUTO_INCREMENT = 1;
```

## Retrieving
```mysql
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
```mysql
SET information_schema_stats_expiry = 0;
```

### Solution B: Forcefully update the cache of `information_schema` for the table
```mysql
ANALYZE TABLE my_table_name;
```

# Reference Pages
- [[MySQL8.0] When the AUTO_INCREMENT of `information_schema.TABLES` is outdated in InnoDB - Qiita](https://qiita.com/lofi/items/3a12f69081c73f81945a)
- [MySQL :: MySQL 8.0 Reference Manual :: 8.2.3 Optimization of `INFORMATION_SCHEMA` Queries](https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimization.html)