Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

I've started to use the DECIMAL type in MySQL to store longitude/latitude coordinates recently and for some reason no matter what value I stored, when queried back I'd always get '0.999'. A little bit of investigation and I found the answer so lets see what was going wrong.

My table was defined something like this...
 SQL
CREATE TABLE test (
ID INT(10) UNSIGNED NOT NULL,
VALUE DECIMAL(3,3) NOT NULL,
PRIMARY KEY (ID)
);


Inserting values that I thought should have worked always failed. This is the kind of INSERT statement I was running...
 SQL
INSERT INTO test(ID, VALUE)
VALUES (1,123.123);


...and the result - always '0.999'.
mysqldec1.png


The problem turned out to be with the DECIMAL(3,3) line. I didn't read the documentation fully before jumping in so what I ended up creating was a field that had 3 digits with 3 digits after the decimal point. So naturally MySQL interpreted that as numbers that would be in the range of .999 to .000. It appears that storing a number larger than 0.999 ends up storing 0.999 all the time i.e. the maximum value this field can store.

What I wanted of course was a field that had 3 digits BEFORE the decimal point and 3 digits AFTER the decimal point. The solution - simply change the column definition to DECIMAL(6, 3). In this definition, the 6 was the total length of the field and the 3 was how many digits to allow after the decimal point. The range that way is 999.999 to 000.000.



The table definition now becomes...
 SQL
CREATE TABLE test (
ID INT(10) UNSIGNED NOT NULL,
VALUE DECIMAL(6,3) NOT NULL,
PRIMARY KEY (ID)
);


Running the same INSERT statement as before, the data was now stored correctly...
mysqldec2.png


So it pays to read the documentation before assuming to know how a particular product to works. In my case I got caught off guard assuming I knew how MySQL DECIMAL worked and ended up wasting time resolving this issue that could have been spent more productively.

-i

Use of any information contained in this blog post/article is subject to this disclaimer.
comments powered by Disqus
Other posts you may like...