'Is it possible to cast a DECIMAL to DOUBLE in MySQL?

I know all the numerical implications, that is, the possible rounding issues inherent to floating point formats, but in my case I have DECIMAL columns in MySQL that I want to convert to DOUBLE straight in the MySQL query rather than down stream.

Could anyone help?

mysqlcastingdoubledecimal


Solution 1:[1]

SELECT my_decimal_field + 0E0 FROM my_table

The following quotes from MySQL manual explain how this works:

9.1.2 Numeric Literals

Number literals include exact-value (integer and DECIMAL) literals and approximate-value (floating-point) literals.

Numbers represented in scientific notation with a mantissa and exponent are approximate-value numbers.

12.22.3 Expression Handling

Handling of a numeric expression depends on what kind of values the expression contains:

  • If any approximate values are present, the expression is approximate and is evaluated using floating-point arithmetic.

Solution 2:[2]

Because of the limitations of the built in CAST function in MySQL, it is only possible to convert DECIMAL to DOUBLE with your own user defined cast function.

Sample use case:

SELECT castDecimalAsDouble(0.000000000000000000100000000000);

Result: 1e-23

CREATE DEFINER=`root`@`localhost` FUNCTION `castDecimalAsDouble`(
decimalInput DECIMAL(65,30) ) RETURNS double
DETERMINISTIC
BEGIN
DECLARE doubleOutput DOUBLE;

SET doubleOutput = decimalInput;

RETURN doubleOutput;
END

Solution 3:[3]

It seems not possible to cast it to DOUBLE which brings problems if you do calculations and for example want to ROUND() a DECIMAL 12,2 in the third digit. Using ROUND(foo * bar,2) will just ignore the additional digits if your foo and bar are DECIMAL 12,2 fields.

That said you can do something like this to still make it work:

ROUND(CAST(foo AS DECIMAL(30,15)*CAST(bar AS DECIMAL(30,15)),2)

Solution 4:[4]

DECIMAL may save space. For example, DECIMAL(4,2) occupies only 2 bytes. FLOAT takes 4; DOUBLE takes 8.

As for the original question, simply do:

ALTER TABLE t MODIFY COLUMN c DOUBLE ...;

(The "..." should include the other stuff you already had, such as NOT NULL.)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Joel Karunungan
Solution 3 OderWat
Solution 4

Related Questions