第24章:精度數學

目錄

24.1. 數值的類型
24.2. DECIMAL數據類型更改
24.3. 表達式處理
24.4. 四舍五入
24.5. 精度數學示例

MySQL 5.1提供了對精度數學的支持,也就是說,數值處理功能,它能給出極其精確的結果,并能對無效值進行高度控制。精度數學基于下述兩種特性:

·         SQL模式,控制服務器接受或拒絕無效值的嚴格程度(請參見5.3.2節,“SQL服務器模式”)。

·         用于定點算法的MySQL庫。

對于數值操作,這些特性具有數種隱含意義:

·         精確計算:對于準確值數值,計算不會引入浮點錯誤。相反,將使用準確的精度。例如,對于數值.0001,會將其當作準確值予以處理,而不是近似值,將其加10000次可獲得準確的結果1,而不是近似于1的值。

·         定義良好的四舍五入特性:對于準確值數值,ROUND()的結果取決于其參量,而不是環境因素,如底層C庫的工作方式等。

·         平臺無關性:對準確數值的操作在不同平臺上(如UnixWindows)是相同的。

·         對無效值處理的控制:能夠檢測到溢出和除0情況,并會將其當作錯誤加以處理。例如,能夠將對于某列來說過大的值當作錯誤對待,而不是對該值進行截短使之位于列數據類型的范圍內。同樣,也會將除0當作錯誤,而不是會獲得NULL結果的操作。至于選擇那種方式,它是由系統變量sql_mode的設置決定的(請參見5.3.2節,“SQL服務器模式”)。

這類特性的一個重要結果是,MySQL 5.1提供了與標準SQL的高度兼容性。

在下面的討論中,介紹了精度數學的數種工作方式(包括與早期應用程序的可能的不兼容性)。在最后,給出了一些示例,演示了MySQL 5.1是如何精確處理數值操作的。

24.1. 數值的類型

對于準確值操作,精度數學的范圍包括準確值的數據類型(DECIMAL和整數類型)以及準確值數值文字。對于近似值數據類型和數值文字,仍會將其當作浮點數值予以處理。

準確值數值文字具有整數部分和小數部分,或兩者。它們可以是有符號的。例如:1.23.4-5-6.78+9.10

近似值數值文字采用科學計數法表示,包含尾數和指數。任意部分或兩者均可以是帶符號的。例如,1.2E31.2E-3-1.2E3-1.2E-3

對于看上去類似的數值,不需要均為準確值或近似值。例如,2.34是準確值(定點)數值,而2.34E0是近似值(浮點)數值。

DECIMAL數據類型是定點類型,其計算是準確的。在MySQL中,DECIMAL類型有多個同義詞:NUMERICDECFIXED。整數類型也是準確值類型。

FLOATDOUBLE數據類型是浮點類型,其計算是近似的。在MySQL中,與FLOATDOUBLE同義的類型是DOUBLE PRECISIONREAL

24.2.?DECIMAL數據類型更改

本節討論了MySQL 5.1DECIMAL數據類型(以及其同義類型)的特性,尤其是下述方面:

·         數字的最大數。

·         存儲格式。

·         存儲要求。

·         DECIMAL列上界 的非標準MySQL擴展。

在本節中,對于為較早MySQL版本編寫的應用程序,在相應的地方指出了可能的不兼容性。

DECIMAL列的聲明語法是DECIMAL(M,D)。在MySQL 5.1中,參量的取值范圍如下:

·         M是數字的最大數(精度)。其范圍為165(在較舊的MySQL版本中,允許的范圍是1254)。

·         D是小數點右側數字的數目(標度)。其范圍是030,但不得超過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字節。

在下表中,給出了關于剩余數字的存儲要求:

剩余的數字

字節數

0

0

1

1

2

1

3

2

4

2

5

3

6

3

7

4

8

4

9

4

與某些較早的MySQL版本不同,在MySQL 5.1中,DECIMAL列不保存前導“+”字符或前導“0”數字。如果將+0003.1插入DECIMAL(5,1)列,將保存為3.1。為了適應該變化,必須更改依賴于早期行為的應用程序。

MySQL 5.1中,DECIMAL列不允許保存大于列定義中隱含范圍的值。例如,DECIMAL(3,0)列支持的范圍為-999999。對于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模式”)。下面介紹了嚴格模式(由STRICT_ALL_TABLESSTRICT_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模式的組合,情況如下:

sql_mode

結果

'' (Default)

無告警,無錯誤:i被設置為NULL

strict

無告警,無錯誤:i被設置為NULL

ERROR_FOR_DIVISION_BY_ZERO

告警,無錯誤:i被設置為NULL

strict,ERROR_FOR_DIVISION_BY_ZERO

錯誤條件,不插入任何行。

將字符串插入數值列時,如果字符串具有非數值內容,將按下述方式將字符串轉換為數值:

·         對于未以數值開始的字符串,在嚴格模式下,不能將其作為數值使用,并會產生錯誤,在其他情況下,給出警告。包括空字符串。

·         對于以數值開始的字符串,可以進行轉換,但尾隨的非數值部分將被截去。在嚴格模式下會導致錯誤,在其他情況下,給出警告。

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;

從邏輯上講,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對于準確類型參量,結果也為準確類型。


這是MySQL參考手冊的翻譯版本,關于MySQL參考手冊,請訪問dev.mysql.com。原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。

广西11选五走势图彩经网