MySQL 函数和运算符：精确计算

来自Wikioe
跳到导航 跳到搜索


关于

MySQL 提供了对精确 math 的支持：数值处理可产生极其精确的结果，并对无效值进行高度控制。


精度 math 基于以下两个功能：

  1. SQL 模式控制服务器对接受或拒绝无效数据的严格程度。
  2. 用于定点运算的 MySQL 库。


这些功能对数值运算有一些影响，并提供与标准 SQL 的高度一致性：

  1. 精确计算：对于精确值数字，计算不会引入浮点误差。而是使用精确的精度。
    例如，MySQL 将诸如“.0001”之类的数字视为一个精确值，而不是一个近似值，将其相加 10,000 次将产生一个准确的1的结果，而不是一个仅“接近”于 1 的值。
  2. 明确定义的舍入行为：对于精确值的数字，“ROUND()”的结果取决于其参数，而不取决于环境因素(例如基础 C 库的工作方式)。
  3. 平台独立性：在不同的平台(例如 Windows 和 Unix)上，精确数值的操作相同。
  4. 控制对无效值的处理：可以检测到溢出和零除，可以将其视为错误。
    例如，您可以将对于列而言太大的值视为错误，而不是将其值截断到列数据类型的范围之内。同样，您可以将零除视为错误，而不是产生结果NULL的运算。选择哪种方法由服务器 SQL 模式的设置决定。

数值类型

精确值运算的精度 math 范围包括精确值数据类型（整数和“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”。

DECIMAL 数据类型特征

DECIMAL列的声明语法为“DECIMAL(M,D)”。自变量的值范围如下：

  1. “M”是最大位数(精度)。范围是 1 到 65。【如果省略“M”，则默认值为 10】
    • 注意：“M”是总位数，而非小数点前的位数。
  2. “D”是小数点右边的位数(小数位数)。它的范围是 0 到 30，并且不得大于“M”。【如果省略“D”，则默认值为 0】
  3. “M”的最大值 65 表示对 DECIMAL 值的计算最多可精确到 65 位数字。此 65 位精度的限制也适用于精确值数字字面量，因此此类字面量的最大范围与以前有所不同。


DECIMAL 列的值使用二进制格式存储，该格式将 9 个十进制数字打包为 4 个字节【？？？】。每个值的整数和小数部分的存储要求分别确定。九位数字的每个倍数需要 4 个字节，剩余的任何剩余数字都需要 4 字节的一部分。下表列出了剩余数字所需的存储量。

剩余数字 字节数
0 0
1–2 1
3–4 2
5–6 3
7–9 4

例如，

“DECIMAL(18,9)”列的小数点两侧各有 9 位数字【共18位，即：小数点前9位，小数点后9位】，因此整数部分和小数部分每个都需要 4 个字节。
“DECIMAL(20,6)”列具有十四个整数数字和六个小数位数【共18位，即：小数点前14位，小数点后6位】。整数位中的 9 位需要 4 个字节，其余 5 位需要 3 个字节。六个小数位需要 3 个字节。


  • DECIMAL 列不存储前面的“+”、“-”符号，及前面的“0”。对于负数，不存储字面的“-”字符。
    如果将“+0003.1”插入“DECIMAL(5,1)”列，则将其存储为3.1。
  • DECIMAL 列不允许大于列定义所隐含范围的值。
    例如，“DECIMAL(3,0)”列支持 -999 到 999 的范围。 “DECIMAL(M,D)”列最多允许小数点左边的“M - D”位数字。


SQL 标准要求“NUMERIC(M,D)”的精度为精确“M”个数字。对于“DECIMAL(M,D)”，该标准要求至少“M”个数字的精度，但允许更高的精度。在 MySQL 中，“DECIMAL(M,D)”和“NUMERIC(M,D)”相同，并且都具有精确的“M”数字精度。

表达式处理

对于精确 math，将尽可能使用给定的精确值数字。

  1. 在严格的 SQL 模式下，对于 INSERT 插入具有精确数据类型（DECIMAL或整数）的列，如果数字在列范围内，则会插入一个带有其精确值的数字。检索时，该值应与插入的值相同。
  2. 如果未启用严格的 SQL 模式，则 INSERT 的截断是允许的。


数值表达式的处理，取决于表达式包含哪种类型的值：

  1. 如果表达式中存在任何近似值，则该表达式为近似值，并使用浮点算法对其求值。
  2. 如果表达式中没有近似值，则表达式仅包含精确值。
    1. 如果表达式中任何精确值包含小数部分，则使用“DECIMAL”精确算术对表达式求值，并且精度为 65 位数。【“精确”程度受二进制可以表示的限制】
      例如，“1.0/3.0”可以用十进制表示法近似为“.333...”，但不能写为确切的数字，因此“(1.0/3.0)*3.0”的计算结果并不完全是1.0。
    2. 否则，表达式仅包含整数值。该表达式是精确的，并且使用整数算术求值，并且精度与“BIGINT(64 位)”相同。
  • 如果数字表达式包含任何字符串，则将它们转换为双精度浮点值，并且该表达式为近似值。


插入数字列受 SQL 模式影响，该模式由“sql_mode”系统变量控制。

  1. 如果将数字插入精确类型列（DECIMAL或整数），则在列范围和精度范围内，将以其精确值插入数字。
  2. 如果该值的小数位数过多，则四舍五入并生成一个近似值。【即使在严格模式下，由于小数部分舍入而导致的截断也不是错误。】
  3. 如果该值在整数部分中包含太多数字，则它太大(超出范围)，并按以下方式处理：
    1. 如果未启用严格模式，则该值将被截断为最接近的合法值并生成警告。
    2. 如果启用严格模式，则会发生溢出错误。

对于将字符串插入数字列，如果字符串具有非数字内容，则按以下方式处理从字符串到数字的转换：

  1. 不以数字开头的字符串不能用作数字，并且在严格模式下会产生错误，否则会发出警告。这包括空字符串。
  2. 可以转换以数字开头的字符串，但尾随的非数字部分将被截断。如果截断的部分包含空格以外的其他内容，则在严格模式下会产生错误，否则会发出警告。


关于“sql_mode”系统变量：包括严格模式（由“STRICT_ALL_TABLES”或“STRICT_TRANS_TABLES”模式值选择）和“ERROR_FOR_DIVISION_BY_ZERO”。要打开所有限制，您可以简单地使用“TRADITIONAL”模式，其中包括严格模式值和“ERROR_FOR_DIVISION_BY_ZERO”： 

SET sql_mode='TRADITIONAL';


对于 DECIMAL 字面量，除了 65 位的精度限制外，字面量文本的长度也有限制。如果该值超过大约 80 个字符，则会发生意外的结果。例如： 

mysql> SELECT CAST(0000000000000000000000000000000000000000000000000000000000000000000000000000000020.01 AS DECIMAL(15,2)) as val;
+------------------+
| val              |
+------------------+
| 9999999999999.99 |
+------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '20'      |
| Warning | 1264 | Out of range value for column 'val' at row 1 |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)


默认情况下，被零除会产生“NULL”的结果，并且没有警告。通过适当设置 SQL 模式，可以限制除以零：启用“ERROR_FOR_DIVISION_BY_ZERO” SQL 模式后，MySQL 处理零除的方式不同：

  1. 如果未启用严格模式，则会发生警告。
  2. 如果启用严格模式，则禁止插入和除以零的更新，并且会发生错误。

即：涉及执行零除表达式的插入和更新可被视为错误，但这除了严格模式外还需要“ERROR_FOR_DIVISION_BY_ZERO”。

假设我们有以下语句： 

INSERT INTO t SET i = 1/0;

严格模式和“ERROR_FOR_DIVISION_BY_ZERO”模式的组合会发生这种情况：

sql_mode Value Result
(默认) 没有警告，没有错误； i设置为NULL。
strict 没有警告，没有错误； i设置为NULL。
ERROR_FOR_DIVISION_BY_ZERO 警告，没有错误； i设置为NULL。
strict,ERROR_FOR_DIVISION_BY_ZERO 错误条件；没有插入行。

舍入行为

“ROUND()”根据其参数是精确值还是近似值，采取不同的舍入规则：

  1. 对于精确值数字，“ROUND()”使用“从零开始舍入”或“向最接近的舍入”规则：
    1. 小数部分为“.5”或更大的值时：如果为正数，则入到下一个整数；如果为负数，则舍到下一个整数。（换句话说，从零开始舍入）
    2. 小数部分小于“.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> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'd' at row 1 |
| Note  | 1265 | Data truncated for column 'd' at row 2 |
+-------+------+----------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT d FROM t;
+------+
| d    |
+------+
|    3 |
|    3 |
+------+
2 rows in set (0.00 sec)
  • “SHOW WARNINGS”语句显示由于舍入小数部分而通过截断生成的字符。即使在严格的 SQL 模式下，这种截断也不是错误。
取自“http://wiki.eijux.com/index.php?title=MySQL_函数和运算符：精确计算&oldid=6521”