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.