16

According to the MySQL website, the signed bigint can go up to 18446744073709551615. What if I need a number bigger than that for the auto-incrementing primary key?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Artist Kampong
  • 161
  • 1
  • 1
  • 3
  • 9
    Give a real example where you need this that is already a very big number – Shakti Singh Apr 23 '11 at 05:13
  • 3
    I can't think of a theoretical situation where this would be necessary... but if so, just use a string that you increment yourself. Any database would fail LONG before hitting that many rows anyway. – Mikecito Apr 23 '11 at 05:40

3 Answers3

105

If you insert 1 million records per second 24x7, it will take 584542 years to reach the limit.

I hope by then a next version of MySQL will support bigger ID columns :)

Jean-François Fabre
  • 137,073
  • 23
  • 153
  • 219
9000
  • 39,899
  • 9
  • 66
  • 104
  • 2
    The actual 'math' is 18446744073709551615 / (1000000*60*60*24*365.25), pretty straightforward: a million, 60 seconds in a minute, 60 minutes in a hour, 24 hours in a day, 365 days in a year (and one-fourth for leap years, not really necessary). It doesn't take _any_ thinking. – 9000 Apr 23 '11 at 06:11
  • The answer to that expression is 58.45420460906263 years, and not 584542 years, right? – Jason Jul 01 '13 at 02:14
  • @Jason: I don't see how. I still suppose filling up a 64-bit counter by million every second will take up more than 584 thousand years. That's the whole point: while 64 bits is definitely limited, if is limitless for _practical_ purposes. – 9000 Jul 01 '13 at 04:55
  • Perhaps i should ask elsewhere. But i tried using "Calculator" on windows and MS Excel and it can't contain all that 18446744073709551615 number. What did u guys use? C++? – Fandi Susanto Sep 26 '14 at 05:04
  • 1
    @FandiSusanto: for back-of-the envelope calculations like that I personally use `python`, `irb`, `ghci`, or even `bc`, depending on what's available at hand. Sorry, `calc.exe` is one of the things I never use. Though neither `bash` using `$(( ))` nor elisp can handle this calculation, too; pretty many such tools are limited to the size of `int` or `long int` of the architecture they run on. – 9000 Sep 26 '14 at 16:57
17

With such a number (1 to 18446744073709551615), you can give all the animals on the earth a unique ID :)

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Sourav
  • 17,065
  • 35
  • 101
  • 159
2

I suppose you're screwed? You could get rid of MySQL's auto increment and could use a base 64 number you increment yourself.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Jess
  • 8,628
  • 6
  • 49
  • 67