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)。原始參考手冊為英文版，與英文版參考手冊相比，本翻譯版可能不是最新的。