What column type should I use to store values between 0 and 1 (say up to 5 decimal places) in MySQL?

Question

It is most important that it be accurate, but also it should take the least disk space possible.

Answer 1

You would need a DECIMAL(6,5) to store a number from 0 to 1 with 5 decimal places.

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.1 are as follows:

• M is the maximum number of digits (the precision). It has a range of 1 to 65.

• D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

Answer 2

According to this, in MySQL 5.0.3

• DECIMAL(5,5) or DECIMAL (6,6) should take 3 bytes.
• DECIMAL(4,4) 2 bytes.

If you need to store values from 0 to 1 inclusive, you might be tempted to use DECIMAL(6,5). But that occupies 4 bytes as integer and float parts are stored separately and you need one byte for integer and three for 5 decimal digits. And if you have 4 bytes you might as well use FLOAT.

Before MySql 5 DECIMALs were stored as strings and the most efficient way was to store SMALLINT or MEDIUMINT (2 or 3 bytes) and manually divide it by 10000 or 1000000 respectively.