Question on numeric data types in Mysql

[ktsirig]

Hello all!
I have a field in my Mysql db which can store various numeric types, like 123, -45.74423, 23e-56 etc, and for those numbers, I can't know if they will be float, integers, positive or negative or how many decimal digits they may have.
My question is, should I use something like VARCHAR (100) as data type for this column?
If, in a query, I want to select all numbers that are >50 for example, will the comparison work or there will be a problem because I would have stored the numbers as VARCHAR?

Please advice!

[LifeinAsia]

Putting numbers in VARCHAR fields is just asking for trouble! You know that 50 > 6. But did you know that '50' < '6'?

Lacking any other information, I would use some sort of numeric field- whichever one has the highest precision that you'll need.

[edited by: LifeinAsia at 11:05 pm (utc) on April 30, 2009]

[ktsirig]

Hi, thank you all for your answer!
But my question remains: will these data types you suggested be able to store numbers like -1235456.754 or 645E-45 or 23.6 without any problem? As I said before, I CAN'T know beforehand what kind of number will the user enter, how many integer digits will it have, how many decimal places or if it will be negative or positive... And I MUST store the number exactly as it will be provided.

[LifeinAsia]

And I MUST store the number exactly as it will be provided.

If that's the case, then you'll probably have to use VARCHAR. But just be aware of the problems associated when comparing values with character strings.

Again, without more information about why you have to store things like that or what kind of comparison, I can't offer better advice.

[rocknbil]

Well I don't know about the exponential notation, you should experiment and review the documentation for that. But you can probably use decimal fields for everything else. The added "dots" in the rows are mine to try and maintain the format output on this board.

create table test (id int(11) not null primary key auto_increment, int_fld int(25) not null, dec_fld decimal (100,25));

insert into test (int_fld,dec_fld) values ('123','123');
insert into test (int_fld,dec_fld) values ('-123','-123');
insert into test (int_fld,dec_fld) values ('-45.74423','-45.74423');
insert into test (int_fld,dec_fld) values ('23e-56','23e-56');

select * from test;

+-------+----------+--------------------------------+ 
¦..id...¦..int_fld.¦......dec_fld...................¦ 
¦..1....¦......123.¦...123.0000000000000000000000000¦ 
¦..2....¦.....-123.¦..-123.0000000000000000000000000¦ 
¦..3....¦-45.74423.¦..-45.74423000000000000000000000¦ 
¦..4....¦.......23.¦.....0.0000000000000000000000000¦ 
+-------+----------+--------------------------------+ 

So it looks like you just need to come up with a different method of dealing with large numbers that express themselves as exponential output.

You'd likely want to do right-side trimming of zeros, but a little programming in the recipe should cook it up nice.