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

Question

We need to store integer values ​​up to 2 ^ 38. Is there any reason to use decimal (12.0) or should bigint be used?

Answer 1

In my opinion, bigint will be better. It is stored as an integer, which MySQL will understand initially without any conversion, and therefore (I think) will be faster to manipulate. Therefore, you should expect MySQL to be a little 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 need up to 12) will be stored in a two-byte block. This means that your column is 6 bytes per row, not 8 bytes for bigint. I would suggest that if a) you are going to store a really obscene number of rows, so the space occupied is a serious concern, and b) you will need to request very little data in question, go with bigint.

Answer 2

This is an assumption, but I think it is good ... on a 64-bit machine, I am sure that access to a 64-bit integer is very efficient, so you should stick with bigint. I don’t know how mysql stores decimal numbers, but I can’t imagine how it will do this more efficiently than storing a 64-bit integer.