小数数据类型将值取整 [英] Decimal datatype is rounding the values

问题描述

我已经将我的MySQL字段表数据类型设置为decimal，因为从我所读的内容中，我将能够在逗号和点之间将价格存储在decimal数据类型字段中...问题是，每当我用逗号或点存储任何数据时，MySQL都会自动将其四舍五入.例如.当我执行以下查询时:

UPDATE table SET field = 114.21 WHERE id = 1;

然后设置了字段，但是将值四舍五入为114，而不是显示我在查询(114.21)中设置的数据-有什么解决方案吗?还是我应该只使用其他数据类型?

解决方案

1. AFAIK点是十进制值的标准符号.如果语法环境允许使用逗号，则使用逗号可能会触发SQL解析错误，或者可能不会引起注意.

2. 您如何定义DECIMAL列的精度?

   如果为DECIMAL(10, 2)，则将总共有10个数字，其中2个为十进制值(2个十进制舍入表示10.215另存为10.22，而10.214变为10.21). /p>

   如果为DECIMAL(10)，则它将没有任何十进制值，并且会四舍五入为整数.

3. 如果使用FLOAT或DOUBLE PRECISION，则不必指定十进制值的数量，但是它有其自身的缺陷.

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?

解决方案

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.

这篇关于小数数据类型将值取整的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！