-1

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;
euraad
  • 2,467
  • 5
  • 30
  • 51
  • 2
    And what do you think will happen to the existing records in the database when you do this? Do you even understand how many records a `long` can hold? – Ian Kemp Jan 10 '21 at 19:37
  • @IanKemp I'm told to do this. – euraad Jan 10 '21 at 20:13
  • 1
    A coder should not just do what they are told. They should explain what the problems are in problematic, inconsistent requirements, and make better proposals. – trincot Jan 10 '21 at 20:20
  • @trincot Well. not in this case. I'm happy about it because I don't need to take responsibility for it. :) – euraad Jan 10 '21 at 20:25
  • 1
    Good luck with that. – trincot Jan 10 '21 at 20:27
  • @trincot They are well understood about the database. And they want it so. – euraad Jan 10 '21 at 20:29
  • 2
    I can't see the use case clearly. Would you ever run out of PK values? You would need to insert a few billion rows per second to use them all in 5 centuries. – The Impaler Jan 10 '21 at 21:20

1 Answers1

2

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • So "BIG INT" = Long in Java? – euraad Jan 10 '21 at 20:19
  • Read https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html – Bill Karwin Jan 10 '21 at 20:26
  • @DanielMårtensson Yes, a `BIGINT` is equivalent to a Java `long`. – The Impaler Jan 10 '21 at 21:18
  • @TheImpaler And not `BigInteger`. I can use that instead? – euraad Jan 10 '21 at 21:18
  • @DanielMårtensson You sure can, but why? `long` is the exact equivalent to a `BIGINT`. `BigInteger` can handle much bigger values, but MySQL can't. The only use case I can see for a Java `BigInteger` is for a MySQL `UNSIGNED BIGINT`. – The Impaler Jan 10 '21 at 21:21
  • MySQL's `BIGINT` can be stored in a `java.lang.Long`. But that Java type doesn't have an unsigned version, so it can only go up to 2^31-1. If you use MySQL's `BIGINT UNSIGNED`, it may have values up to 2^32-1, which are too large to be stored in a `java.lang.Long`, so you must use `java.math.BigInteger`. – Bill Karwin Jan 10 '21 at 21:24