CODEWITHSUNDEEP
mysqldecimal
DD.
DD.

Reputation: 21991

Storing small decimals MySQL

I need to store a large volume of small decimals numbers (3 digits before the decimal, 6 digits after the decimal).

From my understanding of the spec, this will require 8 bytes. I could store the number as an int which requires only 4 bytes and convert after retrieval using a fixed scale factor.

Is there a better alternative instead of using an int, I can't easily do any arithmetic on the numbers?

Thanks.

Upvotes: 2

Views: 2897

Answers (2)

Maagic Mike
Maagic Mike

Reputation: 21

I do not think this is correct. DECIMAL(9,6) should do the job. It will require 2 bytes for the 3 digits and 3 bytes for the 6 digits according to mysql 5.1 manual. IMHO that´s 5 bytes not 8 bytes in total. You will therefore not require a lot more memory than with the integer "hacking" you proposed. I would definitely go with decimal in your case.

Upvotes: 2

Knowledge Craving
Knowledge Craving

Reputation: 7993

No, it won't work if you are using "int" data type of MySQL. This is because integers can't handle decimal precision.

According to your question, you should be using "Fixed Point Data Types", which will benefit you in large calculations & monetary data. In MySQL, the required data type is "DECIMAL" and you can read more on it here.

The proper syntax in you case will be "DECIMAL (9, 6)", where 9 means that values can be stored with up to 9 digits in total, of which 6 digits are after the decimal point and 3 digits are before the decimal point.

Hope it helps.

Upvotes: -1

Related Questions