Bigint in mysql

Question

I need to store twitter tweet ID numbers, and these numbers are large, like 16 digits. I was interested to know how to use bigint in mysql to store them? or is there another alternative. How many digits can bigint process? I tried looking at the specifications, but that was not clear. What are the limitations of storing such large numbers in a mysql table? thanks!

Answer 1

Bigint is 64 bit, which goes -9223372036854775808 - 9223372036854775807, and 0 to 18446744073709551615 unsigned, according to mysql docs .

Answer 2

Even if at present the identifiers correspond to BIGINT (64-bit signature), when the generation of the identifier is beyond your control, it is safer to store it as a text string.

Not only can it grow even more (well, it is unlikely that it will ever overflow with BIGINT); but later you can add a prefix and save identifiers from other systems in one table. Or maybe some future API might use non-numeric identifiers

Answer 3

In the MySQL manual

BIGINT [(M)] [UNSIGNED] [ZEROFILL]

A large integer. Signed range: -9223372036854775808 9223372036854775807. Unsigned range from 0 to 18446744073709551615

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

Answer 4

I think with respect to Wrikken said it would be wise to store two identifiers.

• string (VARCHAR) of the actual id of the tweet, so you have it (and just in case, they decide to go for characters).

• INT which you use for internal things like table binding

Answer 5

You say you use PHP. You should keep in mind that PHP's built-in numeric type is machine dependent, and on x86 only 32 bits are used for integer values. Values ​​that are not integers or are outside the 32-bit range will be stored using floating point types that are not designed for the exact last digit for very large numbers.

Although there are libraries for PHP that allow you to carry large integers regardless of your architecture, it would probably be easier to just keep the id field as a string all the time. Since you will not do arithmetic on it, just treating it as an identifier, this should not be a big problem.

Answer 6

BigInt is the way to go. There is no more efficient data type for this. BigInt can store nubers up to 2 ^ 64, which is about 25 digits.