2

Is it possible to retrieve non-exponential value in double data type column? After the width of the column is increased to 15 it shows the values in exponential form.

Now I get the following:

mysql> create table abc(a int,b double) engine innodb;
mysql> insert into abcd values(10,123456789123456789123456789);

mysql> select * from abc;
+------+-----------------------+
| a    | b                     |
+------+-----------------------+
|   10 | 1.2345678912345679e26 |
+------+-----------------------+

Instead of this I want it to be shown in the form what I entered as 123456789123456789123456789 on mysql client.

1 Answer 1

1

It isn't exactly possible to do what you are attempting to do, because a double precision floating point value cannot contain the number 123456789123456789123456789 without a loss of precision.

A "double" is stored in 8 bytes of memory and is limited to around 15 significant digits of precision -- not by MySQL but by the nature of a "double."

When you perform the insert shown above, you're actually storing 1.2345678912345679 x 10^26 ... so MySQL is actually returning the value correctly.

It is possible to coerce the number back to the original format but not to the exact original value because you didn't store the exact value. Cast it to a sufficiently large DECIMAL value and then cast the DECIMAL to a CHAR:

SELECT CAST(CAST(b AS DECIMAL(30)) AS CHAR) FROM abcd;

The value returned is 123457890123456790000000000... which is correct (despite appearances to the contrary) because that's what's being stored in the field... but the value expressed in that format gives an inaccurate sense of precision... which is presumably the reason it's being returned in exponential notation in the first place.

If you need the precision, you will want to store these values in a DECIMAL column, not a DOUBLE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.