标题: mysql除法精度 [打印本页] 作者: cenalulu 时间: 2009-08-30 21:23 标题: 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 |
+----------+
