MySQL table type for money

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?

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

Solution 2

Your datatype DECIMAL(12,2) seems ok.

You should insert 12999.00, not 12,999.00

Comments

[Boris Zegarac]

I cant control how prices are formatted because they are provided by external stores. Since this datatype is ok I will take a look at the PHP code and see if something suspicious comes up.

[darma]

@Wesley Murch : sorry but why the edit? I would understand if my post was not understandable or not correctly formatted, here you just changed the meaning of my sentence and it doesn't seem right to me. Add maybe a comment instead if you wish to elaborate on my answer?

[Wesley Murch]

@darma: You were asking a question rather than answering one (and besides, you are correct). It looks bad to new users to see questions posted as answers, but you are free to revert my edit. In the future, you should ask for clarification from the OP in the comments.

[darma]

@Wesley Murch : yes i first did ask for the SQL query in a comment ; then a user (and i think also a second one who deleted his comment) told me to post as an answer. Feels weird to me to be edited that way, i think it's a nice place here to use sentences like "you should probably", "maybe think of such and such instead of" etc. and you changed my tone to a more directive one.