CODEWITHSUNDEEP
mysql
Odyss3us
Odyss3us

Reputation: 6635

Storing a floating point number as an INT?

what would be the best data type to store a floating point number in mySQL?

Can I somehow store it as an INT?

I am thinking that VARCHAR is my best option, but if you guys know of anything better I would appreciate the input.

Thanx in advance!

Upvotes: 0

Views: 5628

Answers (4)

Paul Kenjora
Paul Kenjora

Reputation: 2004

Depends on what type of floating point number you are storing, all details regarding MySQL numeric types can be found here:

http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html

DECIMAL: For currency, you should use Decimal because its precise and you wont get weird decimal rounding in arithmetic.

INT: Yes you can store floats as ints to gain performance. For example currency can be stored as $19.56 or 1956 as long as you always / 100 when displaying, accounting software does this often. Furthermore you can store latitude / longitude as integers, 322274063, -1109654800 = 32.2274063, -110.9654800 as long as you divide by 10,000,000 when outside the database.

FLOAT / DOUBLE: And then of course there is long and double. Use them when performance is not a priority or the number of decimal places varies.

SO RULE: If performance is an issue and you know the decimal places are always a fixed length, you can easily store an INT to store a FLOAT.

Upvotes: 7

PP.
PP.

Reputation: 10864

If you have to encode a floating point number in a flat format I'd recommend having a look at http://en.wikipedia.org/wiki/IEEE_754-2008

Upvotes: 0

KeithS
KeithS

Reputation: 71565

Why do you want to store a floating-point as an int? MySQL has decimal and float types just like your programming language.

I'll assume you have a good reason. To store a float as an int, you can try a few things:

  • Multiply the number by 10^n, where n is the number of significant digits you want to keep, and then truncate the rest of the fractional part. When you get it back out of the DB, convert to float/decimal and divide by n. This requires an int big enough to store the multiplied value; in 32-bit architecture, a "native" int can store values up to 2 billion.
  • Split the number into its integer part and its fractional part. This requires two fields, but each field can have a value up to the maximum integer value, allowing you to easily have precision in the hundred-millionths.

Upvotes: 2

Steve Weet
Steve Weet

Reputation: 28392

I would use a Mysql Float type.

Upvotes: 5

Related Questions