MySQL十进制:用底数代替舍入 [英] Mysql decimal: floor instead of round
问题描述
在我的MySQL数据库中,我有字段DECIMAL(23,5),因此小数点后5位数字。现在,当我这样查询时:
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
然后我将获取该记录:
SELECT id, my_decimal_field FROM gijhars WHERE id = 1
我得到以下结果:
+------+------------------+
| id | my_decimal_field |
+------+------------------+
| 5733 | 123.12346 |
+------+------------------+
因此,当然,如果小数点后的位数超过6位,则MySQL将对这些值进行四舍五入。 MySQL中是否有任何设置可以底层这些值而不是四舍五入?
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?
推荐答案
从< a href = http://dev.mysql.com/doc/refman/5.1/en/fixed-point-types.html rel = noreferrer> doc
给该列分配的小数点后的位数比指定刻度允许的位数多时,该值将转换为该刻度。 (确切的行为是特定于操作系统的,但通常是截断了允许的位数。)
如果您的操作系统不是这种情况,则可以在使用截断
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
这篇关于MySQL十进制:用底数代替舍入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!