What does MySQL do when automatically increasing the number of identifiers?

Question

I have a django application that uses MySQL as a database. It has been working for several days, and I already have up to $ 1,000 already on some tables.

I am worried about what happens when I overflow the data type.

In any case, it must be said that auto-increment begins at some point? My data is very unstable, so when I overflow the identifier, there is no way that identifier 0 or anywhere near it is still used.

Answer 1

Depending on whether you are using an unsigned integer or not, and which version of MySQL you are using, you start a skating rink to receive negative negative values ​​for the primary key or (even worse), the line simply will not be inserted and will throw an error.

However, you can easily change the size / type of an integer in MySQL with the ALTER command to preventively stop this. The "standard" size for INT, used as the primary key, is INT (11), but the vast majority of database applications do not need something almost so big. Try MEDIUMINT.

MEDIUMINT - Signature range: -8388608 to 8388607. Unsigned range 0 to 16777215

Compared with....

INT or INTEGER. Signature range: -2147483648 to 2147483647. Unsigned range from 0 to 4294967295

There is also a BIGINT, but to be honest, you probably have a lot more scalability issues than your data types to worry about if the table has s> 2 billion rows :)

Answer 2

Well, the 32-bit INT defaults to about 2 billion. With 5,000 identifiers per day, which is about 1,000 years before overflow. I don’t think you need to worry yet ...