MySQL 5.1提供了對精度數學的支援,也就是說,數值處理功能,它能給出極其精確的結果,並能對無效值進行高度控制。精度數學基於下述兩種特性:
· SQL模式,控制伺服器接受或拒絕無效值的嚴格程度(請參見5.3.2節,「SQL伺服器模式」)。
· 用於定點算法的MySQL庫。
對於數值操作,這些特性具有數種隱含意義:
· 精確計算:對於準確值數值,計算不會引入浮點錯誤。相反,將使用準確的精度。例如,對於數值.0001,會將其當作準確值予以處理,而不是近似值,將其加10000次可獲得準確的結果1,而不是近似於1的值。
· 定義良好的四捨五入特性:對於準確值數值,ROUND()的結果取決於其參量,而不是環境因素,如底層C庫的工作方式等。
· 平台無關性:對準確數值的操作在不同平台上(如Unix和Windows)是相同的。
· 對無效值處理的控制:能夠檢測到溢出和除0情況,並會將其當作錯誤加以處理。例如,能夠將對於某列來說過大的值當作錯誤對待,而不是對該值進行截短使之位於列數據類型的範圍內。同樣,也會將除0當作錯誤,而不是會獲得NULL結果的操作。至於選擇那種方式,它是由系統變數sql_mode的設置決定的(請參見5.3.2節,「SQL伺服器模式」)。
這類特性的一個重要結果是,MySQL 5.1提供了與標準SQL的高度相容性。
在下面的討論中,介紹了精度數學的數種工作方式(包括與早期應用程式的可能的不相容性)。在最後,給出了一些示範,演示了MySQL 5.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。
本節討論了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字節。
在下資料表中,給出了關於剩餘數字的儲存要求:
剩餘的數字 |
字節數 |
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)列支援的範圍為-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參考手冊。
對於精度數學,只要可能,就會使用給定的準確值數值。例如,在比較中所用的數值與給定的值準確相同,無任何變化。在嚴格的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_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模式的組合,情況如下:
sql_mode值 |
結果 |
'' (Default) |
無告警,無錯誤:i被設置為NULL。 |
strict |
無告警,無錯誤:i被設置為NULL。 |
ERROR_FOR_DIVISION_BY_ZERO |
告警,無錯誤:i被設置為NULL。 |
strict,ERROR_FOR_DIVISION_BY_ZERO |
錯誤條件,不插入任何行。 |
將字串插入數值列時,如果字串具有非數值內容,將按下述方式將字串轉換為數值:
· 對於未以數值開始的字串,在嚴格模式下,不能將其作為數值使用,並會產生錯誤,在其他情況下,給出警告。包括空字串。
· 對於以數值開始的字串,可以進行轉換,但尾隨的非數值部分將被截去。在嚴格模式下會導致錯誤,在其他情況下,給出警告。
本節討論了精度數學的四捨五入特性,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 | +------+
本節給出了一些示範,介紹了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。原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。