smallest mysql type that accomodates single decimal

Question

Database newbie here. I'm setting up a mysql table. One of the fields will accept a value in increment of a 0.5. e.g. 0.5, 1.0, 1.5, 2.0, .... 200.5, etc.

I've tried int but it doesn't capture the decimals.

`value` int(10), 

What would be the smallest type that can accommodate this value, considering it's only a single decimal.

I also was considering that because the decimal will always be 0.5 if at all, I could store it in a separate boolean field? So I would have 2 fields instead. Is this a stupid or somewhat over complicated idea? I don't know if it really saves me any memory, and it might get slower now that I'm accessing 2 fields instead of 1

`value` int(10), 
`half` bool,  //or something similar to boolean

What are your suggestions guys? Is the first option better, and what's the smallest data type in that case that would get me the 0.5?

Answer 1

You'll want to look at the DECIMAL(P,S) type.

For that, P is the precision and S is the scale. You can think of P as how many digits there are in total (both before and after the decimal point), and S as how many of the digits are after the decimal point.

So for instance, to store from -999.99 to 999.99, you'd need 5 digits of precision and a scale of 2, therefore you'd use DECIMAL(5, 2).

In your case, you'd need a DECIMAL(n, 1), where n is how many digits you need before the decimal point + 1 for the decimal.

Answer 2

You can use

DECIMAL(N,1)

Where N is the precision that represents the max number of significant digits that can be stored.

Answer 3

Apart from using DECIMAL, you could store the tenfold of the value in an INT column (which I would think could be more efficient.)

• 0.5 => 5
• 201.5 => 2015
• etc.