第24章:精度数学 / 24.5. 精度数学示例

24.5. 精度数学示例

本节给出了一些示例,介绍了MySQL 5.1中的精度数学查询结果。

示例1。可能时,将使用给定的准确值:

mysql> SELECT .1 + .2 = .3;
+--------------+
| .1 + .2 = .3 |
+--------------+
|            1 |
+--------------+

但是,对于浮点值,结果是不准确的:

mysql> SELECT .1E0 + .2E0 = .3E0;
+--------------------+
| .1E0 + .2E0 = .3E0 |
+--------------------+
|                  0 |
+--------------------+

查看准确值和近似值处理差异的另一个方法是,增加1个小的数值,并多次累加。请考虑下述存储程序,它将.0001加到变量上1000次。

CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE d DECIMAL(10,4) DEFAULT 0;
  DECLARE f FLOAT DEFAULT 0;
  WHILE i < 10000 DO
    SET d = d + .0001;
    SET f = f + .0001E0;
    SET i = i + 1;
  END WHILE;
  SELECT d, f;
END;

从逻辑上讲,df的合计应为1,但仅对decimal计算来说是这样。浮点计算会引入小的误差:

+--------+------------------+
| d      | f                |
+--------+------------------+
| 1.0000 | 0.99999999999991 |
+--------+------------------+

示例2。乘法是按照标准SQL所要求的标度执行。也就是说,对于具有标度S1S2的两个数值X1X2,结果的标度为S1 + S2

mysql> SELECT .01 * .01;
+-----------+
| .01 * .01 |
+-----------+
| 0.0001    |
+-----------+

示例3:四舍五入定义良好:

MySQL 5.1中,四舍五入操作(例如,使用ROUND()函数)独立于底层C库函数的实施,这意味着,在不同平台上结果是一致的。

MySQL 5.1中,对于DECIMAL列和准确值数值,采用了半值向上舍入规则。对于小数部分等于或大于0.5的值,以0为分界舍入至最近的整数,如下所示:

mysql> SELECT ROUND(2.5), ROUND(-2.5);
+------------+-------------+
| ROUND(2.5) | ROUND(-2.5) |
+------------+-------------+
| 3          | -3          |
+------------+-------------+

但是,对于浮点值的舍入采用C库,在很多系统上,使用舍入至最近的偶数规则。在这类系统上,具有任何小数部分的值均将被舍入为最近的偶数:

mysql> SELECT ROUND(2.5E0), ROUND(-2.5E0);
+--------------+---------------+
| ROUND(2.5E0) | ROUND(-2.5E0) |
+--------------+---------------+
|            2 |            -2 |
+--------------+---------------+

示例4。在严格模式下,插入过大的值会导致溢出和错误,而不是截短至合法值。

MySQL未运行在严格模式下时,将截短至合法值:

mysql> SET SQL_MODE='';
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO t SET i = 128;
Query OK, 1 row affected, 1 warning (0.01 sec)
 
mysql> SELECT i FROM t;
+------+
| i    |
+------+
|  127 |
+------+
1 row in set (0.00 sec)

但是,如果严格模式起作用,将出现溢出状况:

mysql> SET SQL_MODE='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.01 sec)
 
mysql> SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.10 sec)
 
mysql> INSERT INTO t SET i = 128;
ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1
 
mysql> SELECT i FROM t;
Empty set (0.00 sec)

示例5。在严格模式下并具有ERROR_FOR_DIVISION_BY_ZERO设置时,除0会导致错误,而不是产生NULL结果。

在非严格模式下,除0将得出NULL结果:

mysql> SET SQL_MODE='';
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.01 sec)
 
mysql> INSERT INTO t SET i = 1 / 0;
Query OK, 1 row affected (0.06 sec)
 
mysql> SELECT i FROM t;
+------+
| i    |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

但是,如果恰当的SQL模式处于有效状态,除0将导致错误:

mysql> SET SQL_MODE='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)
 
mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO t SET i = 1 / 0;
ERROR 1365 (22012): Division by 0
 
mysql> SELECT i FROM t;
Empty set (0.01 sec)

示例6。在MySQL 4中(引入精度数学之前),准确值和近似值文字均会被转换为双精度浮点值:

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 4.0.25-standard |
+-----------------+
1 row in set (0.00 sec)
 
 
mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
 
mysql> DESCRIBE t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | double(3,1) |      |     | 0.0     |       |
| b     | double      |      |     | 0       |       |
+-------+-------------+------+-----+---------+-------+

MySQL 5.1中,近似值文字仍会被转换为浮点值,但准确值文字将被当作DECIMAL处理:

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.2-alpha-log |
+-----------------+
1 row in set (0.00 sec)
 
mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
 
mysql> DESCRIBE t;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | decimal(2,1) | NO   |     | 0.0     |       |
| b     | double       | NO   |     | 0       |       |
+-------+--------------+------+-----+---------+-------+

示例7。如果聚合函数的参量是准确的数值类型,其结果也是准确的数值类型,标度至少为参量的标度。

考虑下述语句:

mysql> CREATE TABLE t (i INT, d DECIMAL, f FLOAT);
mysql> INSERT INTO t VALUES(1,1,1);
mysql> CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;

MySQL 4.04.1(在MySQL中引入精度数学之前)中的结果:

mysql> DESCRIBE y;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| AVG(i) | double(17,4) | YES  |     | NULL    |       |
| AVG(d) | double(17,4) | YES  |     | NULL    |       |
| AVG(f) | double       | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

无论参量类型是什么,结果为double

MySQL 5.1中的结果:

mysql> DESCRIBE y;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| AVG(i) | decimal(14,4) | YES  |     | NULL    |       |
| AVG(d) | decimal(14,4) | YES  |     | NULL    |       |
| AVG(f) | double        | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

仅对浮点参量,其结果为double对于准确类型参量,结果也为准确类型。