http://blogold.chinaunix.net/u3/90603/showart_2041630.html

 


mysql除法精度

I ran into this little problem today; when dividing two whole number the result MySQL gave me was nowhere near as precise as I needed it to be. The values were truncated which caused me some errors in my application.

Example:
mysql> select 1*0.00001;
+-----------+
| 1*0.00001 |
+-----------+
| 0.00001 |
+-----------+

Ok, that looks fine. Let's do the same thing using division:

mysql> select 1/100000;
+----------+
| 1/100000 |
+----------+
| 0.0000 |
+----------+

Oops! What happened?

The issue is that the maximum precision of the result value depends on the number of decimal places in the arguments. Since the second version uses two whole numbers, the result uses the default number of decimal places, which is 4. So you really have to take care to make sure you're getting the precision you want out of your math operations!

To MySQL's credit, the behavior is clearly documented (however, I maintain it's still a bit troublesome because how often would you expect the division operator to have a list of instructions and caveats?)

There are at least a couple simple solutions:
mysql> SELECT CAST(1/100000 AS DECIMAL(8,5) );
+---------------------------------+
| CAST(1/100000 AS DECIMAL(8,5) ) |
+---------------------------------+
| 0.00001 |
+---------------------------------+

Or

mysql> SET div_precision_increment=5;
mysql> SELECT 1/100000;
+----------+
| 1/100000 |
+----------+
| 0.00001 |
+----------+

 

 原文地址 http://gtowey.blogspot.com/2009/07/decimal-math-precision.html
 TAG decimal 精度 除法
发表于： 2009-08-30 ，修改于： 2009-08-30 21:23，已浏览440次，有评论0条 推 荐 投诉