CODEWITHSUNDEEP
mysqlcastingdoubledecimal
c00kiemonster
c00kiemonster

Reputation: 23361

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?

Upvotes: 9

Views: 29689

Answers (4)

PowerGamer
PowerGamer

Reputation: 2136

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.

Upvotes: 13

Rick James
Rick James

Reputation: 142296

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.)

Upvotes: -1

JNAK
JNAK

Reputation: 291

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

Upvotes: 2

OderWat
OderWat

Reputation: 5709

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)

Upvotes: 0

Related Questions