Page 1 of 1

Number formats in MySQL

Posted: Tue Nov 09, 2004 9:02 am
by mjseaden
Hi,

I have a MySQL table, created by reading in the contents of a file and inserting them into the table.

I have been using the decimal(n, d) data type in order to record the numbers, however the numbers I read in from the file are in e.g. 1.00053E+20 format.

MySQL seems to have a problem reading in these numbers, is there anything I can do? And for the above number would decimal(1,20) work?

Many thanks

Mark

Posted: Tue Nov 09, 2004 9:20 am
by Weirdan
you need [mysql_man]float[/mysql_man] columns to store that data. decimal(1,20) is not enough for such big numbers.

Posted: Tue Nov 09, 2004 9:21 am
by mjseaden
Thanks

Posted: Wed Nov 10, 2004 4:02 am
by mjseaden
Hi

I realise I need float/double variables, however for the number e.g. 1.000034E+20 (in that exact form), what numbers do I need to use for n and d?

What if the number was in the form 100003400000000000 - would n=1, d=20 work then?

I'm confused.

Thanks

Mark

Posted: Wed Nov 10, 2004 7:53 am
by mjseaden
/bump

Posted: Wed Nov 10, 2004 2:01 pm
by Weirdan
MySQL manual wrote: DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. If UNSIGNED is specified, negative values are disallowed. M is the display width and D is the number of decimals. DOUBLE without arguments or FLOAT(p) (where p is in the range from 25 to 53) stands for a double-precision floating-point number.
According to this manual, M is minimum number of characters used in string representation of the number, D is number of decimals stored after the mantissa is normalized. Following your example (100003400000000000):
  • First step is to normilize the mantissa: 1.000034
  • Due to the decimal dot has been moved 17 positions to the left, exponent is 17
  • Hence the number is represented as 1.000034E+17
therefor it perfectly fits into the FLOAT's range of allowed values.