I have been using int(10) and just noticed that Wordpress uses bigint(20) - What is different to use bigint(20) and int(10) for id auto increment? Which one should I use for id column?
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
Vs
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
Thanks.
Remarks. The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type. bigint fits between smallmoney and int in the data type precedence chart.
The only difference is the range of the type. INT is a 32-bit long while BIGINT is 64-bit long, therefore it can store much larger numbers like 123456789123456789 (which cannot be stored as INT ).
You can use BIGINT as a primary key but with some penalties. BIGINT takes up more space on disk storage than INT and using BIGINT as a primary key (or any index) will add size to the index, perhaps as much as doubling it. This can have a performance impact on searching the index and make it slower to run queries.
The number is used to display width. BIGINT takes 8 bytes i.e. 64 bits. The signed range is -9223372036854775808 to 9223372036854775807 and unsigned range takes positive value. The range of unsigned is 0 to 18446744073709551615.
The difference is purely in the maximum value which can be stored (18,446,744,073,709,551,615 for the bigint(20) and 4,294,967,295 for the int(10), I believe), as per the details on the MySQL Numeric Types manual page.
Incidentally, the use of (20) and (10) is largely irrelevant unless you're using ZEROFILL. (i.e.: It doesn't actually change the size of the number stored - that's all down to the type.)
However, in practical terms it should be noted that you're not likely to hit either of these limits any time soon, unless you're a really active blogger.
The only difference is the range of the type. INT
is a 32-bit long while BIGINT
is 64-bit long, therefore it can store much larger numbers like 123456789123456789
(which cannot be stored as INT
).
Here's a full list of MySQL integer types: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With