?
このドキュメントでは、 php中國語ネットマニュアル リリース
目錄
- 24.1. 數值的類型
- 24.2. DECIMAL數據類型更改
- 24.3. 表達式處理
- 24.4. 四舍五入
- 24.5. 精度數學示例
MySQL 5.1提供了對精度數學的支持，也就是說，數值處理功能，它能給出極其精確的結果，并能對無效值進行高度控制。精度數學基于下述兩種特性：
·???????? SQL模式，控制服務器接受或拒絕無效值的嚴格程度（請參見5.3.2節(jié)，“SQL服務器模式”）。
·???????? 用于定點算法的MySQL庫。
對于數值操作，這些特性具有數種隱含意義：
·???????? 精確計算：對于準確值數值，計算不會引入浮點錯誤。相反，將使用準確的精度。例如，對于數值.0001，會將其當作準確值予以處理，而不是近似值，將其加10000次可獲得準確的結果1，而不是近似于1的值。
·???????? 定義良好的四舍五入特性：對于準確值數值，ROUND()的結果取決于其參量，而不是環(huán)境因素，如底層C庫的工作方式等。
·???????? 平臺無關性：對準確數值的操作在不同平臺上（如Unix和Windows）是相同的。
·???????? 對無效值處理的控制：能夠檢測到溢出和除0情況，并會將其當作錯誤加以處理。例如，能夠將對于某列來說過大的值當作錯誤對待，而不是對該值進行截短使之位于列數據類型的范圍內。同樣，也會將除0當作錯誤，而不是會獲得NULL結果的操作。至于選擇那種方式，它是由系統(tǒng)變量sql_mode的設置決定的（請參見5.3.2節(jié)，“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。
本節(jié)討論了MySQL 5.1中DECIMAL數據類型（以及其同義類型）的特性，尤其是下述方面：
·???????? 數字的最大數。
·???????? 存儲格式。
·???????? 存儲要求。
·???????? 對DECIMAL列上界 的非標準MySQL擴展。
在本節(jié)中，對于為較早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字節(jié)中。對于每個值的整數部分和小數部分，其存儲要求是分別確定的。每9個數字需要4字節(jié)，任何剩余的數字將占用4字節(jié)的一部分。例如，DECIMAL(18,9)列在小數點的每一側均有9位數字，因此，整數部分和小數部分均需要4字節(jié)。DECIMAL(20,10)列在小數點的每一側均有10位數字。對于每一部分，9位數字需要4字節(jié)，剩余的1位數字需要1字節(jié)。
在下表中，給出了關于剩余數字的存儲要求：
|
剩余的數字 |
字節(jié)數 |
|
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執(zhí)行截短操作）。
對數值表達式的處理取決于表達式包含的值的類型：
·???????? 如果存在任何近似值，表達式也是近似的，并將使用浮點算法進行評估。
·???????? 如果不存在近似值，表達式僅包含準確值。如果任一準確值包含小數部分（小數點后面的值），將使用DECIMAL準確算法來計算表達式，其精度為65位數字。術語“準確”受二進制表述方面的限制。例如，1.0/3.0在十進制表述中可近似為.333...，但并不是準確數值，因此(1.0/3.0)*3.0不會被計算為準確的1.0。
·???????? 另外，表達式僅包含整數值。表達式是準確的，并將使用整數算法進行計算，其精度與BIGINT的相同（64比特）。
如果數值表達式包含任何字符串，會將其轉換為雙精度浮點值，表達式是近似的。
數值列中的插入操作受SQL模式的影響，它是由sql_mode系統(tǒng)變量控制的（請參見1.8.2節(jié)，“選擇SQL模式”）。下面介紹了嚴格模式（由STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式值選擇）和RROR_FOR_DIVISION_BY_ZERO。要想打開所有限制，可簡單地使用TRADITIONAL模式，它包含嚴格模式和ERROR_FOR_DIVISION_BY_ZERO：
mysql> SET SQL_MODE='TRADITIONAL';
如果將數值插入具有準確類型的列（DECIMAL或整數），如果值在列允許的范圍內，將以準確值形式插入數值。
如果數值在其小數部分有過多位，將執(zhí)行四舍五入操作并給出告警。關于四舍五入的具體介紹，請參見四舍五入一節(jié)。
如果數值在其整數部分有過多位，數值過大，并將按下述方式處理：
·???????? 如果未啟用嚴格模式，該數值將被截短為最近的合法值，并發(fā)出警告。
·???????? 如果啟用了嚴格模式，將給出溢出錯誤。
不檢測下溢，因而下溢處理是不確定的。
默認情況下，除0操作會導致NULL結果，不產生告警。啟用了ERROR_FOR_DIVISION_BY_ZERO SQL模式后，MySQL會以不同方式處理除0問題：
·???????? 如果未啟用嚴格模式，發(fā)出警告。
·???????? 如果啟用了嚴格模式，將禁止包含除0操作的插入和更新，并給出錯誤。
換句話講，對于包含執(zhí)行除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 |
錯誤條件，不插入任何行。 |
將字符串插入數值列時，如果字符串具有非數值內容，將按下述方式將字符串轉換為數值：
·???????? 對于未以數值開始的字符串，在嚴格模式下，不能將其作為數值使用，并會產生錯誤，在其他情況下，給出警告。包括空字符串。
·???????? 對于以數值開始的字符串，可以進行轉換，但尾隨的非數值部分將被截去。在嚴格模式下會導致錯誤，在其他情況下，給出警告。
本節(jié)討論了精度數學的四舍五入特性，ROUND()函數，以及插入DECIMAL列時的四舍五入特性。
ROUND()函數的行為取決于其參量是準確的還是近似的：
·???????? 對于準確值數值，ROUND()采用“半值向上舍入”規(guī)則：如果小數部分的值為.5或更大，如果是正數，向上取下一個整數，如果是負數，向下取下一個整數（換句話講，以0為界限執(zhí)行舍入）。如果小數部分的值小于.5，如果是正數，向下取下一個整數，如果是負數，向上取下一個整數。
·???????? 對于近似值數值，結果取決于C庫函數。在很多系統(tǒng)上，它意味著ROUND()將使用“舍入至最近的偶數”規(guī)則：具有任何小數部分的值均將被舍入為最近的偶數。
在下面的示例中，介紹了舍入操作對準確值和近似值的不同處理方式：
mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3????????? |?????? ?????2 |
+------------+--------------+
對于向DECIMAL列的插入操作，目標是準確的數據類型，無論要插入的值是準確的還是近似的，將采用“半值向上舍入”規(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中的精度數學查詢結果。
示例1?？赡軙r，將使用給定的準確值：
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所要求的標度執(zhí)行。也就是說，對于具有標度S1和S2的兩個數值X1和X2，結果的標度為S1 + S2：
mysql> SELECT .01 * .01;
+-----------+
| .01 * .01 |
+-----------+
| 0.0001??? |
+-----------+
示例3：四舍五入定義良好：
在MySQL 5.1中，四舍五入操作（例如，使用ROUND()函數）獨立于底層C庫函數的實施，這意味著，在不同平臺上結果是一致的。
在MySQL 5.1中，對于DECIMAL列和準確值數值，采用了“半值向上舍入”規(guī)則。對于小數部分等于或大于0.5的值，以0為分界舍入至最近的整數，如下所示：
mysql> SELECT ROUND(2.5), ROUND(-2.5);
+------------+-------------+
| ROUND(2.5) | ROUND(-2.5) |
+------------+-------------+
| 3????????? | -3????????? |
+------------+-------------+
但是，對于浮點值的舍入采用C庫，在很多系統(tǒng)上，使用“舍入至最近的偶數”規(guī)則。在這類系統(tǒng)上，具有任何小數部分的值均將被舍入為最近的偶數：
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模式處于有效狀態(tài)，除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。原始參考手冊為英文版，與英文版參考手冊相比，本翻譯版可能不是最新的。