CODEWITHSUNDEEP
mysqldecimalsqldatatypes
Scott
Scott

Reputation: 6251

Decimal datatype is rounding the values

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?

Upvotes: 5

Views: 23148

Answers (3)

user3534791
user3534791

Reputation: 1

Well before I have also an issue regarding on what to use on my numbers with decimal points. But problem solved by using DOUBLE(10,2) as my DATATYPE, and it shows the exact number on the database when you save it. Hope it will help.

Upvotes: 0

ron_dobley
ron_dobley

Reputation: 3109

As Mihai mentioned you need to define the proper precision for the decimal type, e.g. DECIMAL(10,2) for two decimal places.

When inserting a decimal value mySQL will round.

From the docs:

For inserts into a DECIMAL or integer column, the target is an exact data type, so rounding uses “round half away from zero,” regardless of whether the value to be inserted is exact or approximate.

See http://dev.mysql.com/doc/refman/5.0/en/precision-math-rounding.html for details.

Upvotes: 0

Mihai Stancu
Mihai Stancu

Reputation: 16117

  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.

Upvotes: 17

Related Questions