What to do when you need integers larger than 20 d

问题:

Seems like BIGINT is the biggest integer available on MySQL, right?

What to do when you need to store a BIGINT(80) for example?

Why in some cases, like somewhere in the Twitter API docs, they recommend us to store these large integers as varchar?

Which is the real reason behind the choice of using one type over another?

回答1:

Big integers aren't actually limited to 20 digits, they're limited to the numbers that can be expressed in 64 bits (for example, the number 99,999,999,999,999,999,999 is not a valid big integer despite it being 20 digits long).

The reason you have this limitation is that native format integers can be manipulated relatively fast by the underlying hardware whereas textual versions of a number (tend to) need to be processed one digit at a time.

If you want a number larger than the largest 64-bit unsigned integer 18,446,744,073,709,551,615 then you will need to store it as a varchar (or other textual field) and hope that you don't need to do much mathematical manipulation on it.

Alternatively, you can look into floating point numbers which have a larger range but less precision, or decimal numbers which should be able to give you 65 digits for an integral value, with decimal(65,0) as the column type.

回答2:

You can specify a numeric(65,0), but if you need to get larger, you'll need a varchar.

The reason to select one over another is usage, efficiency and space. Using an int is more efficient than a bigint or, I believe, numeric If you need to do math on it.

回答3:

You can store that big integers as an arbitrary binary string if you want maximum storage efficiency.

But I'm not sure if it worth it because you'll have to deal with over 64 bit integers in your application too, which is also not the thing you want to do without a strong reason.

Better keep things simple and use varchar.

回答4:

BIGINT is limited by definition to 8 digits. The maximum number of digits in DECIMAL type is 64. You must use VARCHAR to store values of larger precision and be aware that there is no direct math of such values.