Number formats in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Number formats in MySQL

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

you need [mysql_man]float[/mysql_man] columns to store that data. decimal(1,20) is not enough for such big numbers.
Last edited by Weirdan on Tue Nov 09, 2004 9:24 am, edited 1 time in total.
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Thanks
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post 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
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

/bump
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Post Reply