CODEWITHSUNDEEP
sqlmysqlfloating-point
w-ll
w-ll

Reputation: 3915

MySQL float values jumping around on insert?

So i have a SQL table setup as such

CREATE TABLE IF NOT EXISTS `points` (
  `id` int(11) NOT NULL auto_increment,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

And im inserting stuff like

INSERT INTO `points` (`lat`, `lng`) VALUES ('89.123456','-12.123456');

Gives me a row with lat and lng being 89.123459 and -12.123455

Whats up?

Upvotes: 1

Views: 1617

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562871

FLOAT data types have rounding effects, because not every fractional base-10 number can be represented in the base-2 format of IEEE 754. For this reason, FLOAT and DOUBLE PRECISION should be considered inexact numeric data types.

Read: "What Every Computer Scientist Should Know About Floating-Point Arithmetic" (http://www.validlab.com/goldberg/paper.pdf)

+1 to @MindStalker's answer -- use NUMERIC or DECIMAL if you need exact numeric data types.

Upvotes: 1

MindStalker
MindStalker

Reputation: 14864

In computers a "float" value is stored as a number times a number squared generally, and some numbers can't be stored exactly as entered. If you need it exact you should store it as DECIMAL(8,6)

Upvotes: 3

Related Questions