?
This document uses PHP Chinese website manual Release
目錄
- 24.1. 數(shù)值的類型
- 24.2. DECIMAL數(shù)據(jù)類型更改
- 24.3. 表達式處理
- 24.4. 四舍五入
- 24.5. 精度數(shù)學(xué)示例
MySQL 5.1提供了對精度數(shù)學(xué)的支持，也就是說，數(shù)值處理功能，它能給出極其精確的結(jié)果，并能對無效值進行高度控制。精度數(shù)學(xué)基于下述兩種特性：
·???????? SQL模式，控制服務(wù)器接受或拒絕無效值的嚴(yán)格程度（請參見5.3.2節(jié)，“SQL服務(wù)器模式”）。
·???????? 用于定點算法的MySQL庫。
對于數(shù)值操作，這些特性具有數(shù)種隱含意義：
·???????? 精確計算：對于準(zhǔn)確值數(shù)值，計算不會引入浮點錯誤。相反，將使用準(zhǔn)確的精度。例如，對于數(shù)值.0001，會將其當(dāng)作準(zhǔn)確值予以處理，而不是近似值，將其加10000次可獲得準(zhǔn)確的結(jié)果1，而不是近似于1的值。
·???????? 定義良好的四舍五入特性：對于準(zhǔn)確值數(shù)值，ROUND()的結(jié)果取決于其參量，而不是環(huán)境因素，如底層C庫的工作方式等。
·???????? 平臺無關(guān)性：對準(zhǔn)確數(shù)值的操作在不同平臺上（如Unix和Windows）是相同的。
·???????? 對無效值處理的控制：能夠檢測到溢出和除0情況，并會將其當(dāng)作錯誤加以處理。例如，能夠?qū)τ谀沉衼碚f過大的值當(dāng)作錯誤對待，而不是對該值進行截短使之位于列數(shù)據(jù)類型的范圍內(nèi)。同樣，也會將除0當(dāng)作錯誤，而不是會獲得NULL結(jié)果的操作。至于選擇那種方式，它是由系統(tǒng)變量sql_mode的設(shè)置決定的（請參見5.3.2節(jié)，“SQL服務(wù)器模式”）。
這類特性的一個重要結(jié)果是，MySQL 5.1提供了與標(biāo)準(zhǔn)SQL的高度兼容性。
在下面的討論中，介紹了精度數(shù)學(xué)的數(shù)種工作方式（包括與早期應(yīng)用程序的可能的不兼容性）。在最后，給出了一些示例，演示了MySQL 5.1是如何精確處理數(shù)值操作的。
對于準(zhǔn)確值操作，精度數(shù)學(xué)的范圍包括準(zhǔn)確值的數(shù)據(jù)類型（DECIMAL和整數(shù)類型）以及準(zhǔn)確值數(shù)值文字。對于近似值數(shù)據(jù)類型和數(shù)值文字，仍會將其當(dāng)作浮點數(shù)值予以處理。
準(zhǔn)確值數(shù)值文字具有整數(shù)部分和小數(shù)部分，或兩者。它們可以是有符號的。例如：1、.2、3.4、-5、-6.78、+9.10。
近似值數(shù)值文字采用科學(xué)計數(shù)法表示，包含尾數(shù)和指數(shù)。任意部分或兩者均可以是帶符號的。例如，1.2E3、1.2E-3、-1.2E3、-1.2E-3。
對于看上去類似的數(shù)值，不需要均為準(zhǔn)確值或近似值。例如，2.34是準(zhǔn)確值（定點）數(shù)值，而2.34E0是近似值（浮點）數(shù)值。
DECIMAL數(shù)據(jù)類型是定點類型，其計算是準(zhǔn)確的。在MySQL中，DECIMAL類型有多個同義詞：NUMERIC、DEC、FIXED。整數(shù)類型也是準(zhǔn)確值類型。
FLOAT和DOUBLE數(shù)據(jù)類型是浮點類型，其計算是近似的。在MySQL中，與FLOAT或DOUBLE同義的類型是DOUBLE PRECISION和REAL。
本節(jié)討論了MySQL 5.1中DECIMAL數(shù)據(jù)類型（以及其同義類型）的特性，尤其是下述方面：
·???????? 數(shù)字的最大數(shù)。
·???????? 存儲格式。
·???????? 存儲要求。
·???????? 對DECIMAL列上界 的非標(biāo)準(zhǔn)MySQL擴展。
在本節(jié)中，對于為較早MySQL版本編寫的應(yīng)用程序，在相應(yīng)的地方指出了可能的不兼容性。
DECIMAL列的聲明語法是DECIMAL(M,D)。在MySQL 5.1中，參量的取值范圍如下：
·???????? M是數(shù)字的最大數(shù)（精度）。其范圍為1～65（在較舊的MySQL版本中，允許的范圍是1～254）。
·???????? D是小數(shù)點右側(cè)數(shù)字的數(shù)目（標(biāo)度）。其范圍是0～30，但不得超過M。
對于M，最大值65意味著，對DECIMAL值的計算能精確到65位數(shù)字。這種65位數(shù)字的精度限制也適用于準(zhǔn)確值數(shù)值文字，因此，這類文字值的最大范圍不同于以前的范圍（在較早的MySQL版本中，十進制值能達到254位。不過，采用的是浮點計算，因而是近似計算而不是準(zhǔn)確計算）。
在MySQL 5.1中，采用二進制格式保存DECIMAL列的值，將9個十進制數(shù)字打包在4字節(jié)中。對于每個值的整數(shù)部分和小數(shù)部分，其存儲要求是分別確定的。每9個數(shù)字需要4字節(jié)，任何剩余的數(shù)字將占用4字節(jié)的一部分。例如，DECIMAL(18,9)列在小數(shù)點的每一側(cè)均有9位數(shù)字，因此，整數(shù)部分和小數(shù)部分均需要4字節(jié)。DECIMAL(20,10)列在小數(shù)點的每一側(cè)均有10位數(shù)字。對于每一部分，9位數(shù)字需要4字節(jié)，剩余的1位數(shù)字需要1字節(jié)。
在下表中，給出了關(guān)于剩余數(shù)字的存儲要求：
|
剩余的數(shù)字 |
字節(jié)數(shù) |
|
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列不保存前導(dǎo)“+”字符或前導(dǎo)“0”數(shù)字。如果將+0003.1插入DECIMAL(5,1)列，將保存為3.1。為了適應(yīng)該變化，必須更改依賴于早期行為的應(yīng)用程序。
在MySQL 5.1中，DECIMAL列不允許保存大于列定義中隱含范圍的值。例如，DECIMAL(3,0)列支持的范圍為-999～999。對于DECIMAL(M,D)列，小數(shù)點左側(cè)最多允許M –D位數(shù)字（它與依賴于早期MySQL版本的應(yīng)用程序不兼容，允許保存額外數(shù)字代替“+”號）。
SQL標(biāo)準(zhǔn)要求，NUMERIC(M,D)的精度必須準(zhǔn)確為M位數(shù)字。對于DECIMAL(M,D)，標(biāo)準(zhǔn)要求的精度至少為M位數(shù)字，但允許更多。在MySQL中，DECIMAL(M,D)和NUMERIC(M,D)是相同的，兩者的精度均準(zhǔn)確為M位數(shù)字。
對于依賴DECIMAL數(shù)據(jù)類型早期處理方式的應(yīng)用程序，關(guān)于移植這類應(yīng)用程序的更多信息，請參見MySQL 5.0參考手冊。
對于精度數(shù)學(xué)，只要可能，就會使用給定的準(zhǔn)確值數(shù)值。例如，在比較中所用的數(shù)值與給定的值準(zhǔn)確相同，無任何變化。在嚴(yán)格的SQL模式下，對于插入具有準(zhǔn)確數(shù)據(jù)類型（DECIMAL或整數(shù)）的列的INSERT操作，如果值在列的允許范圍內(nèi)，將插入具有準(zhǔn)確值的數(shù)值。檢索時，所獲得的值與插入的值應(yīng)是相同（如果未采用嚴(yán)格模式，允許INSERT執(zhí)行截短操作）。
對數(shù)值表達式的處理取決于表達式包含的值的類型：
·???????? 如果存在任何近似值，表達式也是近似的，并將使用浮點算法進行評估。
·???????? 如果不存在近似值，表達式僅包含準(zhǔn)確值。如果任一準(zhǔn)確值包含小數(shù)部分（小數(shù)點后面的值），將使用DECIMAL準(zhǔn)確算法來計算表達式，其精度為65位數(shù)字。術(shù)語“準(zhǔn)確”受二進制表述方面的限制。例如，1.0/3.0在十進制表述中可近似為.333...，但并不是準(zhǔn)確數(shù)值，因此(1.0/3.0)*3.0不會被計算為準(zhǔn)確的1.0。
·???????? 另外，表達式僅包含整數(shù)值。表達式是準(zhǔn)確的，并將使用整數(shù)算法進行計算，其精度與BIGINT的相同（64比特）。
如果數(shù)值表達式包含任何字符串，會將其轉(zhuǎn)換為雙精度浮點值，表達式是近似的。
數(shù)值列中的插入操作受SQL模式的影響，它是由sql_mode系統(tǒng)變量控制的（請參見1.8.2節(jié)，“選擇SQL模式”）。下面介紹了嚴(yán)格模式（由STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式值選擇）和RROR_FOR_DIVISION_BY_ZERO。要想打開所有限制，可簡單地使用TRADITIONAL模式，它包含嚴(yán)格模式和ERROR_FOR_DIVISION_BY_ZERO：
mysql> SET SQL_MODE='TRADITIONAL';
如果將數(shù)值插入具有準(zhǔn)確類型的列（DECIMAL或整數(shù)），如果值在列允許的范圍內(nèi)，將以準(zhǔn)確值形式插入數(shù)值。
如果數(shù)值在其小數(shù)部分有過多位，將執(zhí)行四舍五入操作并給出告警。關(guān)于四舍五入的具體介紹，請參見四舍五入一節(jié)。
如果數(shù)值在其整數(shù)部分有過多位，數(shù)值過大，并將按下述方式處理：
·???????? 如果未啟用嚴(yán)格模式，該數(shù)值將被截短為最近的合法值，并發(fā)出警告。
·???????? 如果啟用了嚴(yán)格模式，將給出溢出錯誤。
不檢測下溢，因而下溢處理是不確定的。
默認(rèn)情況下，除0操作會導(dǎo)致NULL結(jié)果，不產(chǎn)生告警。啟用了ERROR_FOR_DIVISION_BY_ZERO SQL模式后，MySQL會以不同方式處理除0問題：
·???????? 如果未啟用嚴(yán)格模式，發(fā)出警告。
·???????? 如果啟用了嚴(yán)格模式，將禁止包含除0操作的插入和更新，并給出錯誤。
換句話講，對于包含執(zhí)行除0操作的表達式的插入和更新，將被當(dāng)作錯誤對待，但除了嚴(yán)格模式外還需要ERROR_FOR_DIVISION_BY_ZERO。
假定下述語句：
INSERT INTO t SET i = 1/0;
對于嚴(yán)格模式和ERROR_FOR_DIVISION_BY_ZERO模式的組合，情況如下：
|
sql_mode值 |
結(jié)果 |
|
'' (Default) |
無告警，無錯誤：i被設(shè)置為NULL。 |
|
strict |
無告警，無錯誤：i被設(shè)置為NULL。 |
|
ERROR_FOR_DIVISION_BY_ZERO |
告警，無錯誤：i被設(shè)置為NULL。 |
|
strict,ERROR_FOR_DIVISION_BY_ZERO |
錯誤條件，不插入任何行。 |
將字符串插入數(shù)值列時，如果字符串具有非數(shù)值內(nèi)容，將按下述方式將字符串轉(zhuǎn)換為數(shù)值：
·???????? 對于未以數(shù)值開始的字符串，在嚴(yán)格模式下，不能將其作為數(shù)值使用，并會產(chǎn)生錯誤，在其他情況下，給出警告。包括空字符串。
·???????? 對于以數(shù)值開始的字符串，可以進行轉(zhuǎn)換，但尾隨的非數(shù)值部分將被截去。在嚴(yán)格模式下會導(dǎo)致錯誤，在其他情況下，給出警告。
本節(jié)討論了精度數(shù)學(xué)的四舍五入特性，ROUND()函數(shù)，以及插入DECIMAL列時的四舍五入特性。
ROUND()函數(shù)的行為取決于其參量是準(zhǔn)確的還是近似的：
·???????? 對于準(zhǔn)確值數(shù)值，ROUND()采用“半值向上舍入”規(guī)則：如果小數(shù)部分的值為.5或更大，如果是正數(shù)，向上取下一個整數(shù)，如果是負(fù)數(shù)，向下取下一個整數(shù)（換句話講，以0為界限執(zhí)行舍入）。如果小數(shù)部分的值小于.5，如果是正數(shù)，向下取下一個整數(shù)，如果是負(fù)數(shù)，向上取下一個整數(shù)。
·???????? 對于近似值數(shù)值，結(jié)果取決于C庫函數(shù)。在很多系統(tǒng)上，它意味著ROUND()將使用“舍入至最近的偶數(shù)”規(guī)則：具有任何小數(shù)部分的值均將被舍入為最近的偶數(shù)。
在下面的示例中，介紹了舍入操作對準(zhǔn)確值和近似值的不同處理方式：
mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3????????? |?????? ?????2 |
+------------+--------------+
對于向DECIMAL列的插入操作，目標(biāo)是準(zhǔn)確的數(shù)據(jù)類型，無論要插入的值是準(zhǔn)確的還是近似的，將采用“半值向上舍入”規(guī)則：
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 | +------+
本節(jié)給出了一些示例，介紹了MySQL 5.1中的精度數(shù)學(xué)查詢結(jié)果。
示例1。可能時，將使用給定的準(zhǔn)確值：
mysql> SELECT .1 + .2 = .3;
+--------------+
| .1 + .2 = .3 |
+--------------+
|??????????? 1 |
+--------------+
但是，對于浮點值，結(jié)果是不準(zhǔn)確的：
mysql> SELECT .1E0 + .2E0 = .3E0;
+--------------------+
| .1E0 + .2E0 = .3E0 |
+--------------------+
|????????????????? 0 |
+--------------------+
查看準(zhǔn)確值和近似值處理差異的另一個方法是，增加1個小的數(shù)值，并多次累加。請考慮下述存儲程序，它將.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的合計應(yīng)為1，但僅對decimal計算來說是這樣。浮點計算會引入小的誤差：
+--------+------------------+
| d????? | f??????????????? |
+--------+------------------+
| 1.0000 | 0.99999999999991 |
+--------+------------------+
示例2。乘法是按照標(biāo)準(zhǔn)SQL所要求的標(biāo)度執(zhí)行。也就是說，對于具有標(biāo)度S1和S2的兩個數(shù)值X1和X2，結(jié)果的標(biāo)度為S1 + S2：
mysql> SELECT .01 * .01;
+-----------+
| .01 * .01 |
+-----------+
| 0.0001??? |
+-----------+
示例3：四舍五入定義良好：
在MySQL 5.1中，四舍五入操作（例如，使用ROUND()函數(shù)）獨立于底層C庫函數(shù)的實施，這意味著，在不同平臺上結(jié)果是一致的。
在MySQL 5.1中，對于DECIMAL列和準(zhǔn)確值數(shù)值，采用了“半值向上舍入”規(guī)則。對于小數(shù)部分等于或大于0.5的值，以0為分界舍入至最近的整數(shù)，如下所示：
mysql> SELECT ROUND(2.5), ROUND(-2.5);
+------------+-------------+
| ROUND(2.5) | ROUND(-2.5) |
+------------+-------------+
| 3????????? | -3????????? |
+------------+-------------+
但是，對于浮點值的舍入采用C庫，在很多系統(tǒng)上，使用“舍入至最近的偶數(shù)”規(guī)則。在這類系統(tǒng)上，具有任何小數(shù)部分的值均將被舍入為最近的偶數(shù)：
mysql> SELECT ROUND(2.5E0), ROUND(-2.5E0);
+--------------+---------------+
| ROUND(2.5E0) | ROUND(-2.5E0) |
+--------------+---------------+
|??????????? 2 |??????????? -2 |
+--------------+---------------+
示例4。在嚴(yán)格模式下，插入過大的值會導(dǎo)致溢出和錯誤，而不是截短至合法值。
當(dāng)MySQL未運行在嚴(yán)格模式下時，將截短至合法值：
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)
但是，如果嚴(yán)格模式起作用，將出現(xiàn)溢出狀況：
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。在嚴(yán)格模式下并具有ERROR_FOR_DIVISION_BY_ZERO設(shè)置時，除0會導(dǎo)致錯誤，而不是產(chǎn)生NULL結(jié)果。
在非嚴(yán)格模式下，除0將得出NULL結(jié)果：
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)
但是，如果恰當(dāng)?shù)?span>SQL模式處于有效狀態(tài)，除0將導(dǎo)致錯誤：
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中（引入精度數(shù)學(xué)之前），準(zhǔn)確值和近似值文字均會被轉(zhuǎn)換為雙精度浮點值：
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中，近似值文字仍會被轉(zhuǎn)換為浮點值，但準(zhǔn)確值文字將被當(dāng)作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。如果聚合函數(shù)的參量是準(zhǔn)確的數(shù)值類型，其結(jié)果也是準(zhǔn)確的數(shù)值類型，標(biāo)度至少為參量的標(biāo)度。
考慮下述語句：
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中引入精度數(shù)學(xué)之前）中的結(jié)果：
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??? |?????? |
+--------+--------------+------+-----+---------+-------+
無論參量類型是什么，結(jié)果為double。
在MySQL 5.1中的結(jié)果：
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??? |?????? |
+--------+---------------+------+-----+---------+-------+
僅對浮點參量，其結(jié)果為double。對于準(zhǔn)確類型參量，結(jié)果也為準(zhǔn)確類型。
這是MySQL參考手冊的翻譯版本，關(guān)于MySQL參考手冊，請訪問dev.mysql.com。原始參考手冊為英文版，與英文版參考手冊相比，本翻譯版可能不是最新的。