Geek Answers Handbook

Drop from 1 to decimal 0.9999 instead of 1.0000 in MySQL

When casting 1 to decimal in MySQL, I expected 1.0000 , but instead the output is 0.9999 .

What exactly is going on here?

 SELECT CAST(1 AS DECIMAL(4,4))
+5
source share
3 answers

out of range

For example, DECIMAL (5.2) stores any value with five digits and two decimal places, a range from -999.99 to 999.99.

 SELECT CAST(1 AS DECIMAL(5,4)) -> 1.0000 SELECT CAST(1 AS DECIMAL(4,3)) -> 1.000 SELECT CAST(0.0001 AS DECIMAL(4,4)) -> 0.0001 SELECT CAST(0.00001 AS DECIMAL(4,4)) -> 0.0000 SELECT CAST(12345 AS DECIMAL(5,4)) -> 9.9999

Additional Information:

https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types .html

+3
source

MySQL truncates the converted value to the largest, which will fit into the target type. A decimal(4, 4) does not contain significant digits to the left of the decimal point.

This tide does not cause overflow only when the server is not working in strict mode, as described in the documentation .

+4
source

How are you trying to convert an integer (in your case its value is 1) to the decimal number DECIMAL (n, m) that have precision n, i.e. the total number of digits (n = 4 in your case) and the scale as t, i.e. the number of digits after the decimal (m = 4 in your case)

So, in your case, the decimal number will vary from (-0.9999 to 0.9999)

And therefore, when you try to convert any integer> 0 , it will be converted as 0.9999 (Maximum possible value)

and when you try to convert any integer <0 it will be converted as -0.9999 (the lowest possible value)

this type of conversion will cause an error if the server is running in strict mode.

+1
source

More articles:


All Articles