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 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...
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
Facebook
Twitter
Google+