Welcome to WebmasterWorld Guest from 54.226.110.143

Forum Moderators: open

Message Too Old, No Replies

Question on numeric data types in Mysql

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

Junior Member

10+ Year Member

joined:Sept 26, 2005
posts:92
votes: 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!

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

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5628
votes: 48


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]

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

Junior Member

10+ Year Member

joined:Sept 26, 2005
posts:92
votes: 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.
5:41 pm on May 1, 2009 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5628
votes: 48


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.

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

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members