MySQL table type for money [duplicate]

Question

Possible Duplicate:
Best Data Type for Currency

I have a table inside my database where I store product prices; the column type is set to DECIMAL(12, 2). However, whenever I add the following price: 1,199.00 and save it, for some reason it converts into 1.00.

Or, if I add a price like 12,000.00, it converts it to 12. Should I use using some other type of field or add other values to DECIMAL?

Answer 1

According to the documentation on DECIMAL, that column type should work fine. If you're using another language to interface with MySQL (PHP, for example), you should post that code along with your SQL code, since in plain SQL, I can't reproduce the behaviour you indicate. This is the sample I used:

CREATE TABLE money (
    amount DECIMAL(12, 2));

INSERT INTO `money` (`amount`) VALUES (12999.00);

INSERT INTO `money` (`amount`) VALUES (12,999.00);

This should fail with an error because the second INSERT statement sees 12,999.00 as two separate values destined for two separated columns. Since only one column is listed (amount), it fails. See this fiddle for how I reproduced the error.