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:

Is it possible to set a limit on rows in my database because AUTO_INCREMENT is getting higher and higher.
If rows reach max limit what happens next? I mean that happens with AUTO_INCREMENT?
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.....

A MySQL `int` column can store 4.3 billion items, do you think you'll hit that quickly? Also, do you really need an ID if you are just reading the 40 most recent? You can just use the `reading_time`, I'd think — Chris Haas, Sep 29 '20 at 14:38
AS @ChrisHaas said, if you hit 4.3 billion that easy, use 8 byte alternate BIG INT it has 2^64 = 18446744073709551616, don't how to count :) — Duke, Sep 29 '20 at 14:50
As about #2, [this](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b776e2ffa48ee9c9466ebe8aae0cdf3e) is what happens: [Out of range value for column 'id'](https://stackoverflow.com/questions/14284494/mysql-error-1264-out-of-range-value-for-column) — Álvaro González, Sep 30 '20 at 07:51

score 1 · Accepted Answer · answered Sep 29 '20 at 14:37

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

MySQL row limit and AUTO_INCREMENT

1 Answers1