What is the correct default value for a MySQL decimal field?

Question

I have a decimal field in my MySQL database. I have defined it as this: decimal(1,1) UNSIGNED NULL. But I would like to set a default value for it like 7.0, and this is the problem I have. Whenever I want to set this value, I get this error:

Invalid default value ...

I also tried to set it as 7,0 and 7 but it resulted the same error. What is the correct default value for a MySQL decimal field?

Note: I am using Navicat for MySQL

Answer 1

In MySQL, when declaring DECIMAL(P,S) :

The precision (P) represents the number of significant digits that are stored for values, and the scale (S) represents the number of digits that can be stored following the decimal point.

So in your example, DECIMAL(1,1) means at most 1 digit, and at most 1 digit after the dot... which doesn't really make sense.

To better understand, here are more examples:

• DECIMAL(5,2): 5 digits, two of them being used for the fractional part. Hence, possible values range from -999.99 to 999.99
• DECIMAL(5,0): no fractional part allowed, so it is equivalent to an integer with maximum 5 digits.

With UNSIGNED, the behavior is the same, but using a minus sign will throw an error.