ktsirig Posted April 30, 2009 Share Posted April 30, 2009 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! Quote Link to comment https://forums.phpfreaks.com/topic/156334-question-on-numeric-data-types-in-mysql/ Share on other sites More sharing options...
Mchl Posted April 30, 2009 Share Posted April 30, 2009 Store numbers as numbers. FLOAT should work for you. Quote Link to comment https://forums.phpfreaks.com/topic/156334-question-on-numeric-data-types-in-mysql/#findComment-823127 Share on other sites More sharing options...
TheFilmGod Posted May 1, 2009 Share Posted May 1, 2009 You shouldn't use a float unless you are storing numbers like 1.22223039540329 - you get the idea. Varchar would work, but the index would be much longer. To give you an idea - each character in varchar needs 8 bytes. The largest data number in mysql is 8 bytes long (bigINT) goes past 1 billion numbers. Use the numbers. More importantly, a numeric column forces the data to be a number. - keeps data cleaner. Consider calling your numeric column types "unsigned" - this gives you twice as much numbers as signed. Unsigned means only positive numbers. So instead of going -128 to 128 UNSIGNED tinyint would go 0 - 256. Quote Link to comment https://forums.phpfreaks.com/topic/156334-question-on-numeric-data-types-in-mysql/#findComment-823203 Share on other sites More sharing options...
Mchl Posted May 1, 2009 Share Posted May 1, 2009 You shouldn't use a float unless you are storing numbers like 1.22223039540329 - you get the idea. Varchar would work, but the index would be much longer. WTH are you talking about? Run this query SELECT 10 < 2 and this SELECT "10" < "2" The second one is how VARCHAR will work Quote Link to comment https://forums.phpfreaks.com/topic/156334-question-on-numeric-data-types-in-mysql/#findComment-823295 Share on other sites More sharing options...
ktsirig Posted May 1, 2009 Author Share Posted May 1, 2009 Hi, thanx to you all for your answers! 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. Quote Link to comment https://forums.phpfreaks.com/topic/156334-question-on-numeric-data-types-in-mysql/#findComment-823312 Share on other sites More sharing options...
Mchl Posted May 1, 2009 Share Posted May 1, 2009 Please familiarise yourself with this first: http://en.wikipedia.org/wiki/Floating_point MySQL's FLOAT will give you 32 bit precision (a.k.a. single-precision) DOUBLE will give you 64 bit (a.k.a. - you guessed it - double-precision) Quote Link to comment https://forums.phpfreaks.com/topic/156334-question-on-numeric-data-types-in-mysql/#findComment-823320 Share on other sites More sharing options...
fenway Posted May 1, 2009 Share Posted May 1, 2009 Yes, but you rarely want floating-point precision -- if you're dealing with "real" numbers, use DECIMAL. Trust me. Quote Link to comment https://forums.phpfreaks.com/topic/156334-question-on-numeric-data-types-in-mysql/#findComment-823542 Share on other sites More sharing options...
Mchl Posted May 1, 2009 Share Posted May 1, 2009 Yeah. For most uses DECIMAL is actually the best. However OP said that he might have to store numbers like 23e-56... DECIMAL doesn't really fit for that. Quote Link to comment https://forums.phpfreaks.com/topic/156334-question-on-numeric-data-types-in-mysql/#findComment-823624 Share on other sites More sharing options...
TheFilmGod Posted May 2, 2009 Share Posted May 2, 2009 You shouldn't use a float unless you are storing numbers like 1.22223039540329 - you get the idea. Varchar would work, but the index would be much longer. WTH are you talking about? Run this query SELECT 10 < 2 and this SELECT "10" < "2" The second one is how VARCHAR will work WTH are you talking about? Varchar index is much larger than a numeric column data index. Of course the way the query is setup it will work both ways, but it is inappropriate database design to store numbers as varchar when only numbers would go into that column. Quote Link to comment https://forums.phpfreaks.com/topic/156334-question-on-numeric-data-types-in-mysql/#findComment-823939 Share on other sites More sharing options...
Mchl Posted May 2, 2009 Share Posted May 2, 2009 Ok.... so I gather you weren't suggesting to use VARCHAR after all? That's how I understood you. Quote Link to comment https://forums.phpfreaks.com/topic/156334-question-on-numeric-data-types-in-mysql/#findComment-824087 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.