MySQL 精度数学示例

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;

从逻辑上讲，d和f的合计应为1，但仅对decimal计算来说是这样。浮点计算会引入小的误差：

+--------+------------------+

| d      | f                |

+--------+------------------+

| 1.0000 | 0.99999999999991 |

+--------+------------------+

示例2。乘法是按照标准SQL所要求的标度执行。也就是说，对于具有标度S1和S2的两个数值X1和X2，结果的标度为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.0或4.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。对于准确类型参量，结果也为准确类型。

这是MySQL参考手册的翻译版本，关于MySQL参考手册，请访问dev.mysql.com。原始参考手册为英文版，与英文版参考手册相比，本翻译版可能不是最新的。