24. 精度数学 · mysql5.1参考手册

### 第24章：精度数学 MySQL 5.1提供了对精度数学的支持，也就是说，数值处理功能，它能给出极其精确的结果，并能对无效值进行高度控制。精度数学基于下述两种特性： · SQL模式，控制服务器接受或拒绝无效值的严格程度（请参见[5.3.2节，“SQL服务器模式”](# "5.3.2. The Server SQL Mode")）。 · 用于定点算法的MySQL库。对于数值操作，这些特性具有数种隐含意义： · **精确计算：**对于准确值数值，计算不会引入浮点错误。相反，将使用准确的精度。例如，对于数值.0001，会将其当作准确值予以处理，而不是近似值，将其加10000次可获得准确的结果1，而不是近似于1的值。 · **定义良好的四舍五入特性：**对于准确值数值，ROUND()的结果取决于其参量，而不是环境因素，如底层C库的工作方式等。 · **平台无关性：**对准确数值的操作在不同平台上（如Unix和Windows）是相同的。 · **对无效值处理的控制：**能够检测到溢出和除0情况，并会将其当作错误加以处理。例如，能够将对于某列来说过大的值当作错误对待，而不是对该值进行截短使之位于列数据类型的范围内。同样，也会将除0当作错误，而不是会获得NULL结果的操作。至于选择那种方式，它是由系统变量sql_mode的设置决定的（请参见[5.3.2节，“SQL服务器模式”](# "5.3.2. The Server SQL Mode")）。这类特性的一个重要结果是，MySQL 5.1提供了与标准SQL的高度兼容性。在下面的讨论中，介绍了精度数学的数种工作方式（包括与早期应用程序的可能的不兼容性）。在最后，给出了一些示例，演示了MySQL 5.1是如何精确处理数值操作的。 ### 24.1. 数值的类型对于准确值操作，精度数学的范围包括准确值的数据类型（DECIMAL和整数类型）以及准确值数值文字。对于近似值数据类型和数值文字，仍会将其当作浮点数值予以处理。准确值数值文字具有整数部分和小数部分，或两者。它们可以是有符号的。例如：1、.2、3.4、-5、-6.78、+9.10。近似值数值文字采用科学计数法表示，包含尾数和指数。任意部分或两者均可以是带符号的。例如，1.2E3、1.2E-3、-1.2E3、-1.2E-3。对于看上去类似的数值，不需要均为准确值或近似值。例如，2.34是准确值（定点）数值，而2.34E0是近似值（浮点）数值。 DECIMAL数据类型是定点类型，其计算是准确的。在MySQL中，DECIMAL类型有多个同义词：NUMERIC、DEC、FIXED。整数类型也是准确值类型。 FLOAT和DOUBLE数据类型是浮点类型，其计算是近似的。在MySQL中，与FLOAT或DOUBLE同义的类型是DOUBLE PRECISION和REAL。 ### 24.2. DECIMAL数据类型更改本节讨论了MySQL 5.1中DECIMAL数据类型（以及其同义类型）的特性，尤其是下述方面： · 数字的最大数。 · 存储格式。 · 存储要求。 · 对DECIMAL列上界的非标准MySQL扩展。在本节中，对于为较早MySQL版本编写的应用程序，在相应的地方指出了可能的不兼容性。 DECIMAL列的声明语法是DECIMAL(_M_,_D_)。在MySQL 5.1中，参量的取值范围如下： · _M_是数字的最大数（精度）。其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254）。 · _D_是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过_M_。对于M，最大值65意味着，对DECIMAL值的计算能精确到65位数字。这种65位数字的精度限制也适用于准确值数值文字，因此，这类文字值的最大范围不同于以前的范围（在较早的MySQL版本中，十进制值能达到254位。不过，采用的是浮点计算，因而是近似计算而不是准确计算）。在MySQL 5.1中，采用二进制格式保存DECIMAL列的值，将9个十进制数字打包在4字节中。对于每个值的整数部分和小数部分，其存储要求是分别确定的。每9个数字需要4字节，任何剩余的数字将占用4字节的一部分。例如，DECIMAL(18,9)列在小数点的每一侧均有9位数字，因此，整数部分和小数部分均需要4字节。DECIMAL(20,10)列在小数点的每一侧均有10位数字。对于每一部分，9位数字需要4字节，剩余的1位数字需要1字节。在下表中，给出了关于剩余数字的存储要求： <table border="1" cellpadding="0" id="table1"><tr><td> 剩余的数字</td> <td> 字节数</td> </tr><tr><td> 0</td> <td> 0</td> </tr><tr><td> 1</td> <td> 1</td> </tr><tr><td> 2</td> <td> 1</td> </tr><tr><td> 3</td> <td> 2</td> </tr><tr><td> 4</td> <td> 2</td> </tr><tr><td> 5</td> <td> 3</td> </tr><tr><td> 6</td> <td> 3</td> </tr><tr><td> 7</td> <td> 4</td> </tr><tr><td> 8</td> <td> 4</td> </tr><tr><td> 9</td> <td> 4</td> </tr></table> 与某些较早的MySQL版本不同，在MySQL 5.1中，DECIMAL列不保存前导“+”字符或前导“0”数字。如果将+0003.1插入DECIMAL(5,1)列，将保存为3.1。为了适应该变化，必须更改依赖于早期行为的应用程序。在MySQL 5.1中，DECIMAL列不允许保存大于列定义中隐含范围的值。例如，DECIMAL(3,0)列支持的范围为-999～999。对于DECIMAL(_M_,_D_)列，小数点左侧最多允许_M_–_D_位数字（它与依赖于早期MySQL版本的应用程序不兼容，允许保存额外数字代替“+”号）。 SQL标准要求，NUMERIC(_M_,_D_)的精度必须准确为_M_位数字。对于DECIMAL(_M_,_D_)，标准要求的精度至少为_M_位数字，但允许更多。在MySQL中，DECIMAL(_M_,_D_)和NUMERIC(_M_,_D_)是相同的，两者的精度均准确为_M_位数字。对于依赖DECIMAL数据类型早期处理方式的应用程序，关于移植这类应用程序的更多信息，请参见_MySQL 5.0参考手册。_ ### 24.3. 表达式处理对于精度数学，只要可能，就会使用给定的准确值数值。例如，在比较中所用的数值与给定的值准确相同，无任何变化。在严格的SQL模式下，对于插入具有准确数据类型（DECIMAL或整数）的列的INSERT操作，如果值在列的允许范围内，将插入具有准确值的数值。检索时，所获得的值与插入的值应是相同（如果未采用严格模式，允许INSERT执行截短操作）。对数值表达式的处理取决于表达式包含的值的类型： · 如果存在任何近似值，表达式也是近似的，并将使用浮点算法进行评估。 · 如果不存在近似值，表达式仅包含准确值。如果任一准确值包含小数部分（小数点后面的值），将使用DECIMAL准确算法来计算表达式，其精度为65位数字。术语“准确”受二进制表述方面的限制。例如，1.0/3.0在十进制表述中可近似为.333...，但并不是准确数值，因此(1.0/3.0)*3.0不会被计算为准确的1.0。 · 另外，表达式仅包含整数值。表达式是准确的，并将使用整数算法进行计算，其精度与BIGINT的相同（64比特）。如果数值表达式包含任何字符串，会将其转换为双精度浮点值，表达式是近似的。数值列中的插入操作受SQL模式的影响，它是由sql_mode系统变量控制的（请参见[1.8.2节，“选择SQL模式”](# "1.8.2. Selecting SQL Modes")）。下面介绍了严格模式（由STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式值选择）和RROR_FOR_DIVISION_BY_ZERO。要想打开所有限制，可简单地使用TRADITIONAL模式，它包含严格模式和ERROR_FOR_DIVISION_BY_ZERO： mysql> SET SQL_MODE='TRADITIONAL'; 如果将数值插入具有准确类型的列（DECIMAL或整数），如果值在列允许的范围内，将以准确值形式插入数值。如果数值在其小数部分有过多位，将执行四舍五入操作并给出告警。关于四舍五入的具体介绍，请参见_四舍五入_一节。如果数值在其整数部分有过多位，数值过大，并将按下述方式处理： · 如果未启用严格模式，该数值将被截短为最近的合法值，并发出警告。 · 如果启用了严格模式，将给出溢出错误。不检测下溢，因而下溢处理是不确定的。默认情况下，除0操作会导致NULL结果，不产生告警。启用了ERROR_FOR_DIVISION_BY_ZERO SQL模式后，MySQL会以不同方式处理除0问题： · 如果未启用严格模式，发出警告。 · 如果启用了严格模式，将禁止包含除0操作的插入和更新，并给出错误。换句话讲，对于包含执行除0操作的表达式的插入和更新，将被当作错误对待，但除了严格模式外还需要ERROR_FOR_DIVISION_BY_ZERO。假定下述语句： INSERT INTO t SET i = 1/0; 对于严格模式和ERROR_FOR_DIVISION_BY_ZERO模式的组合，情况如下： <table border="1" cellpadding="0" id="table2"><tr><td> sql_mode值</td> <td> 结果</td> </tr><tr><td> '' (Default)</td> <td> 无告警，无错误：i被设置为NULL。</td> </tr><tr><td> strict</td> <td> 无告警，无错误：i被设置为NULL。</td> </tr><tr><td> ERROR_FOR_DIVISION_BY_ZERO</td> <td> 告警，无错误：i被设置为NULL。</td> </tr><tr><td> strict,ERROR_FOR_DIVISION_BY_ZERO</td> <td> 错误条件，不插入任何行。</td> </tr></table> 将字符串插入数值列时，如果字符串具有非数值内容，将按下述方式将字符串转换为数值： _ · _对于未以数值开始的字符串，在严格模式下，不能将其作为数值使用，并会产生错误，在其他情况下，给出警告。_包括空字符串。_ · 对于以数值开始的字符串，可以进行转换，但尾随的非数值部分将被截去。在严格模式下会导致错误，在其他情况下，给出警告。 ### 24.4. 四舍五入本节讨论了精度数学的四舍五入特性，ROUND()函数，以及插入DECIMAL列时的四舍五入特性。 ROUND()函数的行为取决于其参量是准确的还是近似的： · 对于准确值数值，ROUND()采用“半值向上舍入”规则：如果小数部分的值为.5或更大，如果是正数，向上取下一个整数，如果是负数，向下取下一个整数（换句话讲，以0为界限执行舍入）。如果小数部分的值小于.5，如果是正数，向下取下一个整数，如果是负数，向上取下一个整数。 · 对于近似值数值，结果取决于C库函数。在很多系统上，它意味着ROUND()将使用“舍入至最近的偶数”规则：具有任何小数部分的值均将被舍入为最近的偶数。在下面的示例中，介绍了舍入操作对准确值和近似值的不同处理方式： mysql> SELECT ROUND(2.5), ROUND(25E-1); +------------+--------------+ | ROUND(2.5) | ROUND(25E-1) | +------------+--------------+ | 3 | 2 | +------------+--------------+ 对于向DECIMAL列的插入操作，目标是准确的数据类型，无论要插入的值是准确的还是近似的，将采用“半值向上舍入”规则： mysql> CREATE TABLE t (d DECIMAL(10,0)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t VALUES(2.5),(2.5E0); Query OK, 2 rows affected, 2 warnings (0.00 sec) Records: 2 Duplicates: 0 Warnings: 2 mysql> SELECT d FROM t; +------+ | d | +------+ | 3 | | 3 | +------+ ### 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](http://dev.mysql.com/doc/mysql/en)。原始参考手册为英文版，与英文版参考手册相比，本翻译版可能不是最新的。