Level 35
I would definitely go for your second option: round it yourself and than store it. This way you are in charge and not a setting in your database that could be easily overlooked the next time you setup a database.
1 like
Mar 19, 2018
4
Level 7
MySQL - Rounding
Hi
Our Laravel application's database runs on a MySQL 5.7.21 server. We store some float values with up to 6 decimal places into a double field with 2 decimal places. MySQL automatically rounds the value to 2 decimals.
Now the rounding function rounds for example 6.025 to 6.02 instead of 6.03. But 5 should be rounded up (mathematically round). MySQL rounds 6.026 correctly to 6.03.
Anyone knows where or how I can fix this? Otherwise we will round the value before storing it to the DB.
Thanks and regards
Level 7
Yeah, only thought if I it's possible to define this in a Laravel migration I would prefer that way.
Level 35
I do not know if a setting is available that can change this behavior. Mysql docs does say about DOUBLE data types:
MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001. https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html
But no further details if this behavior is editable.
About the 'DECIMAL' data type it say:
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.) https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html
So based on this two lines I would go for making sure in your application that you pass the correct value to your database before storing.
Level 7
Thanks for your research! We're going to round the value in the applcation.
Please or to participate in this conversation.