In my MySQL database I have field DECIMAL(23,5), so 5 digits after decimal point. Now, when I will query like this:
UPDATE my_table SET my_decimal_field = 123.123456789 WHERE id = 1
And then I will fetch that record:
SELECT id, my_decimal_field FROM gijhars WHERE id = 1
I get this result:
+------+------------------+
| id | my_decimal_field |
+------+------------------+
| 5733 | 123.12346 |
+------+------------------+
So, of course, MySQL is rounding these values, if they have more than 6 digits after decimal point. Is there any setting in MySQL, to floor these values instead of rounding?
From the doc
When such a column is assigned a value with more digits following the decimal point than are permitted by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the permissible number of digits.)
If that is not the case for your operating system, then you can handle this while updating with truncate
UPDATE my_table
SET my_decimal_field = truncate(123.123456789, 5)
WHERE id = 1
SQLFiddle demo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With