How can I handle the problem whe AUTO_INCREMENT hit its limit?

开发者_如何学运维

Is there any good practice for this?

I wish I could solve the problem when primary key hit the limit and not to avoid it. Because this is what will happen in my specific problem.

If it's unavoidable... What can i do?

This is mysql question, is there Sybase sql anywhere same problem?

---

Why would you hit the limit on that field? If you defined it with a datatype large enough it should be able to hold all your records.

If you used an unsigned bigint you can have up to 18,446,744,073,709,551,615 records!

---

You should pick the correct type for the primary key, if you know you will have lots of rows you could use bigint instead of the commonly used int.

In mysql you can easily adjust the primary key collumn with the alter table statement to adjust the range.
you should also use the unsigned property on that collumn because an auto increment primary key is always positive.

when the limit is reached you could maybe create some algorithm to put inside the ON DUPLICATE KEY UPDATE statement of an INSERT

---

Well, depending on the autoincrement column's datatype.
Unsign int goes up to 4294967295.

If you want to prevent the error, you can check the value last autoincrement value: LAST_INSERT_ID()

If it's approaching the datatype's max, either do not allow insertion or handle it in other ways.

Other than that, I can only suggest you use bigint so you can almost not hit the max for most scenario.

Can't give you a foolproof answer though :)

---

I know this question might be too old, but I would like to answer as well.

It is actually impossible to make that scenario unavoidable. Just by thinking there is a physical limit about how many storage drives humankind is able to make. But this is certainly not likely to happen to fill all available storage.

As others have told you, an UNSIGNED BIGINT is able to handle up to 18,446,744,073,709,551,615 records, probably the way to go in "most" cases.

Here is another idea: by triggering the number of records of your database (for example, 85% full) you could backup that table into another table/region/database, and scale your infrastructure accordingly. And then reset that initial table.

And my last approach: some companies opt to do a tiny change to their license and use agreement, that they will shutdown an account if the user do not log in for a certain amount of time (say, 6 months for free users, 6 years for pro users, 60 years for ultimate users... <-- hey! you can also use different tables for those too!).

Hope somebody find this useful.