*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'*.

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

**. 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**

*DECIMAL(6, 3)**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...

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