[sclug] FW: MySQL & number types

[Paul Vanlint]

Hi Chris,

I am not quite sure why you didn't get it to work with float, but I am
using MySQL 4.0, perhaps that is it.

Anyway, when you are dealing with decimal numbers, you have two choices:

Exact representation, using numeric (or decimal).

e.g. to create a field which can store up to 10 digits of precision,
with up to 5 decimal digits...

mysql> create table tmp4 (a numeric(10,5));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tmp4 values (1E-5);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tmp4;
+---------+
| a       |
+---------+
| 0.00001 |
+---------+
1 row in set (0.00 sec)


Or, approximate representation, using float or double

e.g.

mysql> create table tmp4 (a float);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tmp4 values (1E-5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tmp4;
+-------+
| a     |
+-------+
| 1e-05 |
+-------+
1 row in set (0.01 sec)

Note that if you want to get the number back in decimal notation, then
you must use:

mysql> select format(a,5) from tmp4;
+-------------+
| format(a,5) |
+-------------+
| 0.00001     |
+-------------+
1 row in set (0.02 sec)


Regards,

Paul.


On Fri, 2003-08-08 at 11:00, Chris Aitken wrote:
> Morning All,
> 
> I have an access db, with some forms & reports, which I have converted to
> MySQL db, with an access front end (ODBC)
> 
> I need to store numbers such as 2E-5 etc. I don't mind how they are stored
> (as general notation 0.00002, or scientific notation 2E-5), as I can massage
> that with the Access form, but whatever value I put in, always ends up as 0.
> Large numbers such as 1,2, 2000, 2000000 always get displayed correctly, but
> I cannot work this for negative exponent numbers.
> 
> I have tried Float, decimal, double, etc, but to no avail!
> 
> It is MySQL 3.23.49, running on e-smith 5.6
> 
> Any help/ideas gratefully accepted.
> 
> Cheers
> 
> Chris
> 
> 
> -- 
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
> 
> _______________________________________________
> sclug mailing list
> sclug at sclug.org.uk
> http://www.sclug.org.uk/mailman/listinfo/sclug
>