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!

[Mchl]

Store numbers as numbers. FLOAT should work for you.

[TheFilmGod]

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.

[Mchl]

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

[ktsirig]

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.

[Mchl]

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)

 

[fenway]

Yes, but you rarely want floating-point precision -- if you're dealing with "real" numbers, use DECIMAL.  Trust me.

[Mchl]

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.

[TheFilmGod]

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.

[Mchl]

Ok.... so I gather you weren't suggesting to use VARCHAR after all? That's how I understood you.