Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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