### 第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> <p><strong><span>剩余的數字</span></strong></p></td> <td> <p><strong><span>字節數</span></strong></p></td> </tr><tr><td> <p><span>0</span></p></td> <td> <p><span>0</span></p></td> </tr><tr><td> <p><span>1</span></p></td> <td> <p><span>1</span></p></td> </tr><tr><td> <p><span>2</span></p></td> <td> <p><span>1</span></p></td> </tr><tr><td> <p><span>3</span></p></td> <td> <p><span>2</span></p></td> </tr><tr><td> <p><span>4</span></p></td> <td> <p><span>2</span></p></td> </tr><tr><td> <p><span>5</span></p></td> <td> <p><span>3</span></p></td> </tr><tr><td> <p><span>6</span></p></td> <td> <p><span>3</span></p></td> </tr><tr><td> <p><span>7</span></p></td> <td> <p><span>4</span></p></td> </tr><tr><td> <p><span>8</span></p></td> <td> <p><span>4</span></p></td> </tr><tr><td> <p><span>9</span></p></td> <td> <p><span>4</span></p></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> <p><span><b> <span>sql_mode</span></b></span><strong><span>值</span></strong></p></td> <td> <p><strong><span>結果</span></strong></p></td> </tr><tr><td> <p> <span>''</span><span> (Default)</span></p></td> <td> <p>無告警,無錯誤:<span><span>i</span>被設置為<span>NULL</span></span>。</p></td> </tr><tr><td> <p><span>strict</span></p></td> <td> <p>無告警,無錯誤:<span><span>i</span>被設置為<span>NULL</span></span>。</p></td> </tr><tr><td> <p> <span>ERROR_FOR_DIVISION_BY_ZERO</span></p></td> <td> <p>告警,無錯誤:<span><span>i</span>被設置為<span>NULL</span></span>。</p></td> </tr><tr><td> <p><span>strict,<span>ERROR_FOR_DIVISION_BY_ZERO</span></span></p></td> <td> <p>錯誤條件,不插入任何行。</p></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)。原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。
- 前言
- 1. 一般信息
- 2. 安裝MySQL
- 3. 教程
- 4. MySQL程序概述
- 5. 數據庫管理
- 6. MySQL中的復制
- 7. 優化
- 8. 客戶端和實用工具程序
- 9. 語言結構
- 10. 字符集支持
- 11. 列類型
- 12. 函數和操作符
- 13. SQL語句語法
- 14. 插件式存儲引擎體系結構
- 15. 存儲引擎和表類型
- 16. 編寫自定義存儲引擎
- 17. MySQL簇
- 18. 分區
- 19. MySQL中的空間擴展
- 20. 存儲程序和函數
- 21. 觸發程序
- 22. 視圖
- 23. INFORMATION_SCHEMA信息數據庫
- 24. 精度數學
- 25. API和庫
- 26. 連接器
- 27. 擴展MySQL
- A. 問題和常見錯誤
- B. 錯誤代碼和消息
- C. 感謝
- D. MySQL變更史
- E. 移植到其他系統
- F. 環境變量
- G. MySQL正則表達式
- H. MySQL中的限制
- I. 特性限制
- J. GNU通用公共許可
- K. MySQL FLOSS許可例外
- 索引