Problem with very small decimals


I am just a novice mysql user so hopefully you will be able to help me.

I am storing some very small numbers in a field of type double. Some of the numbers are being stored as values like this: 8.72852595488195e-005. This is no good to me because some of the 3rd party components that I work with cannot handle the data in this form (ie. e-005).

Can i prevent the database from storing the values with this notation? Perhaps I need to use a different data type?

many thanks
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I have never seen mysql store values like that.  This sounds like a problem with your application.
Bernard S.CTOCommented:
I think you need to make a distinction between:
- how the number is stored, and more specifically how many significant digits are kept, or more precisely are usable
- how the number is displayed.
(and remember than values are stored with binary digits, while they are rendered as decimal digits... which adds some problems).

When looking at the value, it is describing

If you know that all your values will be in the range of 0.5e-5 (ie 0.000 005) to 10.0e-5 (ie 0.000 100), and that you will need 3 significant digits eg 0.598e-5 (0.000 00598) you might consider storing not the values but the values time e+8, ie scaling them to 100 000 000 times the value.
0.598e-5 would be stored as 598,  10.0e-5 as 10 000
No problem with additions and substarction, you would just need to remember that these values are to be divided by e+8, ie by 100 000 000.
BEWARE though that you have to take a special care
- if you are multiplying or dividing by a "normal" value (eg 13%, or one-tenth, or 10 times), no problem (but remember that you will have to round the result to integer value)
- if you are multiplying two of the numbers together.... remember that you will have to divide the "integer result" by the scaling factor 100 000 000 (once)
- if you are dividing two of the numbers together... nothing to do, scale is correct.
andiejeAuthor Commented:

The values can be anything from large to very small.

The data in this form -  8.72852595488195e-005- must come from an application I am using which is storing the data direclty in the database. Is there anyway I can get mysql to convert this data to its other form (0.0000872852595488195) when data like this is written to the database? Perhaps you could use an insert/update trigger?
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Bernard S.CTOCommented:
Yes you can, but in fact you can't.

- you define (ie it cannot be changed) the width of the field
- you can define that you want 20 digits out of which 19 after the decimal point... you can define 20 digits out of which 10 after the decimal point... but you cannot have BOTH at the same time

So if you want to cover a range of 10 digits before the decimal points (and say 8 significant digits) down to 12 after the decimal point (out of which still 8 significvant digits)... you will need to go into "scientific notation" which will use powers of ten as a scaling factor.

BEWARE of the significance of computations in these situations... specially when dealing with additions and substractions.
eg if A=3.E+10 (30 000 000 000), B=2.E-5 (0.00002), and if you add them keeping 8 significant digist, you will be in a "non-mathematical situation" where A+B and A are (numerically) equal while B>0...

The problem is not mysql.  If you login directly to mysql and enter large decimal numbers into the database and select them back out, you'll see that they are exactly as you entered them.  I believe the problem is  Leave it M$ to muck about with your data! :-)

Search google for " displaying numbers in scientific notation" and there are a ton of results that should point you in the right direction.  I don't know well enough to give you code and I don't feel good about copying pasting an example from google that you could easily get yourself.

It seems that if you convert the number to a string it will display properly.  I said I wasn't going to do this, but here is an example showing the problem...

Dim d As Double = Math.PI / 10000000000
Dim ds As String
ds = d.ToString() 'with scientific notation
ds = d.ToString("N20") 'without


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
It is how it is being presented to you most likely as you suspect.  The data should be stored without the scientific notation.  On the VB.NET side, use Decimal instead of Double.

Dim d As Decimal
Kevin CrossChief Technology OfficerCommented:

Dim d As Decimal = Math.PI / 10000000000
MsgBox(d) 'Without scientific notation
Dim ds As String = d.ToString() 
MsgBox(ds) 'Also without scientific notation

Open in new window

andiejeAuthor Commented:
Many thanks
andiejeAuthor Commented:
mwvisa - I'm sorry i did not see your comment about using a decimal. That was actually key to fixing the issue. I had to this

dim d as decimal = getReallySmallValueasDouble.tostring("N20")

to get rid of the notation
Kevin CrossChief Technology OfficerCommented:
Just glad to help.

Best regards,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.