MySQL field type for storing decimals

Question

I'm creating a DB that will hold products with several "height" columns (in meters, for ex 7.79 or 12,8). Never more than 2 digits before and 2 after the decimal point. What field type should I use for this?

If I use decimal(2,2) an try to insert 7.79 in phpmyadmin I get an error saying Warning: #1264 Out of range value for column 'working_height' at row 1

I'll be using this DB for searching, so I have to be able to run a query like "select all products where height is great than 7".

Answer 1

Try DECIMAL(4,2) instead

Refer to: MySQL Numeric Types

Answer 2

You're looking for decimal(4,2) - in general, decimal(m,n) means m total digits, and n to the right of the decimal point. Docs here.

So a decimal(2,2) can store two total digits, both to the right of the decimal point. This explains the error that you are seeing.

Answer 3

People will say to use decimal(s, d) but how about storing the values as integers, in centimeters instead of meters? Easier to compare (no precision loss).

Just my two cents.