MySQL row limit and AUTO_INCREMENT

Question

I use a NodeMCU arduino to send sensor data to MySQL database. I used this code to create my table

CREATE TABLE Sensor (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  value1 VARCHAR(10),
  value2 VARCHAR(10),
  value3 VARCHAR(10),
  reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

I also use a php page to store data on database and a second php page to visualise 40 most recent entires

My question is:

1. Is it possible to set a limit on rows in my database because AUTO_INCREMENT is getting higher and higher.
2. If rows reach max limit what happens next? I mean that happens with AUTO_INCREMENT?
3. Let's supose our stored indexes are 5,6,7,8,9... Is it possible to reset the index of every entry to look like 5->1,6->2.....

Answer 1

change your datatype to BigINT

this should help for the next few years. till they decide to make it finaly 64 bit or even higher is necessary

When AUTO_INCREMENT hits the limit of the datatype it stops inserting with an error message like

Failed to read auto-increment value from storage engine

changing the id is always a bad idea so let it stay and auto_increment doesn't garantee a consequential number. you can always build one yourself look for row_numbw