mysql , bigint or decimal for storing > 32 bit values but less than 64 bits

We're needing to store integer valu开发者_如何学Ces of up to 2^38 . Are there any reasons to use decimal(12,0) or should we use bigint ?

In my view, bigint would be better. It's stored as an integer that MySQL will understand natively without any conversion required, and will therefore (I imagine) be faster at manipulating. You should therefore expect MySQL to be marginally more efficient if you use bigint.

According to this manual page, the first 9 digits of your number will be stored in a four-byte block and the remaining digits (you require up to 12) will be stored in a two-byte block. That means your column takes up 6 bytes per row, as opposed to 8 bytes for bigint. I would suggest that unless a) you are going to be storing a truly obscene number of rows, such that the space taken up is a serious concern, and b) you are going to need to query the data in question very little, you should go with bigint.

This is an assumption, but I think its a good one ... on a 64bit machine, i'm pretty sure accessing a 64bit integer is very efficient, so you should stick with bigint. i don't know off-hand how mysql stores decimals, but i can't imagine how it would do so more efficiently than storing a 64-bit integer.