I'm storing A LOT of data every millisecond in a database. I'm told to do that. And it's OK if the primary key reaches to the top, then it's OK to reset it and overwrite the current rows in the database.
Is it possible to set the primary key in MySQL or Spring Boot JPA so it will AUTO-reset by it self if the primary key id reaches its top?
In this case, I have selected long as the datatype. But not sure if it will hold and be good enough. This stradegy as auto incrimention.
// ID
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
Sorry, MySQL's auto-increment mechanism is designed never to generate a value that is less than the max value in the auto-increment column. It will not overwrite existing values, or even "fill in" values that are unused.
I suggest you use a BIGINT UNSIGNED if you are incrementing the auto-increment rapidly. Assuming you increment the values by 1 on each INSERT, you will not run out of values in your lifetime.
Do the math.
Assume you insert data rapidly enough to increment the auto-increment of this table by 1 million every second. So you insert 1 million * 3600 * 24 rows per day. This works out to 31,536,000,000,000 rows in one year (which is already beyond the capacity of any single MySQL instance).
That will use 0.00017% of the range of a BIGINT UNSIGNED column. The 64-bit integer goes up to 18,446,744,073,709,551,615.
You would need to continue inserting data into that table at that rate for 5,850 years to fill up the primary key.
If you still want to design your app to overwrite records when it reaches the max id, feel free, but you can't do it using MySQL auto-increment feature. You will have to generate the id values in some other way.