homepage Welcome to WebmasterWorld Guest from 23.21.9.44
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Question on numeric data types in Mysql
ktsirig




msg:3904493
 10:49 pm on Apr 30, 2009 (gmt 0)

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




msg:3904506
 11:03 pm on Apr 30, 2009 (gmt 0)

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




msg:3904809
 8:50 am on May 1, 2009 (gmt 0)

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




msg:3905088
 5:41 pm on May 1, 2009 (gmt 0)

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




msg:3905924
 4:42 pm on May 3, 2009 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved