mysql unlimited primary keys auto increment

Question

I have a question with im really unsure with. First feel free to downvote me if its a must but i would really like to hear a more experienced developers opinion.

I am building a site where i would like to build similar functionality like google circles.

My logic would be this. Every user will have circles attaced to them after signup.

example if the user will sign up

form filed and the following querys will be insierted to the database

**id   | circle_name       | user_id**
------------------------------------
1      | circle one        | 1
------------------------------
2      | circle two        | 1
------------------------------
3      | circle three      | 1

Every circle will have a primary key

But this is what im unsure with, so after a time im a bit scared that the table will break, what im mean is if it will reach a number of id's it will actually stop generating more.

When you specifiy an int in the database the default value is 11, yes i know i can incrase or set it to the value what i want, but still giveing higher values is a good idea?

or is there any possibility to make a primary key auto increment to be unlimited?

thank you for the opinions and help outs

For what it's worth, the length of an int field is not the actual length. It's basically only useful for zerofill columns (and the functionality of zero padding doesn't belong in the database anyway in my opinion). You can easily test this: try to insert a 5 character int into an `int(3)`. It will happily accept it. Similarly an `int` > 10 doesn't exist (since 2^31-1 or 2^32-1 is 10 digits). — Corbin, Dec 21 '12 at 09:07
"If you insert 1M records per second 24x7, it will take 584542 years to reach the limit." @9000.... http://stackoverflow.com/questions/5762327/mysql-what-if-i-need-a-very-very-big-autoincrement-id — bipen, Dec 21 '12 at 09:08

Mark Byers · Accepted Answer · 2012-12-21T09:13:11.910

14

or is there any possibility to make a primary key auto increment to be unlimited?

You can use a BIGINT.

Strictly speaking it's not unlimited, but the range is so incredibly huge that you wouldn't be able to use up all the values even if you tried really hard.

edited Dec 21 '12 at 09:13

answered Dec 21 '12 at 09:05

Mark Byers

811,555
193
1,581
1,452

4

You could also make it unsigned which would be ridiculously big – Dale Dec 21 '12 at 09:06
2

In addition to this, when you start to get to a point where you have so many entries that BIGINT is no longer good enough, you be able to hire the smartest minds on earth to solve your database problems. – Ryan McDonough Dec 21 '12 at 11:08

score 4 · Answer 2 · answered Dec 21 '12 at 09:07

Just run some maths and you ll get the answer yourself. If a length can store billions of values and you don't expect to have 1 million new registrations every week then getting to a point where it breaks would be "practically" tough, even if "theoretically" possible

mysql unlimited primary keys auto increment

2 Answers2