Decimal datatype is rounding the values

Question

I have set my MySQL field table data type to the decimal because from what I have read, I would be able to store the price with commas/dots in the decimal data type fields... The problem is that whenever I store any data with the comma or dot, MySQL is rounding it automatically up or down. Eg. When I'm executing the following query:

UPDATE table SET field = 114.21 WHERE id = 1;

Then field is set, but the value is rounded to 114, instead of displaying the data I set in the query (114.21) - is there any solution for that? Or I should just use other data type?

Answer 1

1. AFAIK the dot is the standard notation for decimal values. Using Commas may trigger SQL parse errors or may go unnoticed if the syntactical context allows for a comma to be there.

2. How did you define the precision of the DECIMAL column?

   If it is DECIMAL(10, 2) it will have a total of 10 numbers of which 2 are decimal values (with 2 decimal rounding meaning that 10.215 is saved as 10.22 and 10.214 becomes 10.21).

   If it is DECIMAL(10) it will not have any decimal values and be rounded to an integer.

3. If you use FLOAT or DOUBLE PRECISION you don't have to specify the number of decimal values but it has its own flaws.