第22章 精密計算

正確に演算する為の精密計算の範囲には、正確値データ タイプ（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 データ タイプ(並びにその同義語)の持つ特徴について説明しています。

MySQL の古いバージョン用に書かれたアプリケーションに適合しない可能性のある問題については、このセクション全体で指摘されます。

DECIMAL カラムに対する宣言構文は DECIMAL(M,D) です。MySQL 5.1 における引数値の範囲は次の通りです:

M に対する最大値が65である事は、DECIMAL 値に関する計算が65桁まで正確である事を意味します。精度を６５桁とするこの限界は正確値数値リテラルにも適用されるので、このようなリテラルの最大範囲は以前とは異なります。(古いバージョンの MySQL では、１０進法の値を最高２５４桁まで持つ事ができました。しかし、計算は浮動小数点を使って実行されていたので、結果は正確でなく、近似値でした。）

MySQL 5.1 では、DECIMAL カラムの為の値は、４バイトの中に９個の１０進数をパックするバイナリ フォーマットを使って格納されます。各値の整数部と端数部に対する格納要件は別々に決定されます。９桁の倍ごとに４バイト、使い残しの桁には４バイトの分数分の容量がそれぞれ必要です。例えば、DECIMAL(18,9) カラムは少数点のいずれかの側に９桁のスペースを持っているので、整数部分と端数部分には各々４バイト必要です。DECIMAL(20,10) カラムは小数点のいずれかの側に10桁のスペースを持っています。各々の部分には、9桁に対して４バイト、残りの桁に対して１バイトがそれぞれ必要です。

使い残しの桁に必要な格納容量を以下のテーブルに列記します:

(5.0.3より前の)古いバージョンの MySQL とは異なり、MySQL 5.1 内の DECIMAL カラムは主要な + 文字または主要な 0 桁数を格納しません。DECIMAL(5,1) カラムの中に +0003.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 Reference Manual をご参照ください。

精密計算では、出来るだけ正確な値の数を附与して使用します。例えば、比較における数字を、値を変更しないで附与して正確に使用します。厳密な SQL モードでは、カラムの中に正確なデータ タイプ(DECIMAL あるいは整数)を利用してカラム内に INSERT する為に、もしカラム範囲内に収まる場合は、その数値が正確な値を使って挿入されます。検索された場合は、挿入した時と同じに値である必要があります。(ストリクト モードなしで、INSERT の為の切り捨てが許容されています。)

数値表現の取り扱い方法は表現にどんな種類の値が含まれているかによって異なります。

数値表現に文字列が含まれている場合、それらはダブル精度浮動小数点の値に換えられ、その式は近似となります。

数値カラムへの挿入は SQL モードによって影響され、sql_modeシステム変数によって制御されます。.（「SQL モード」 を参照してください。）以下の部分で、(STRICT_ALL_TABLES モード値もしくは STRICT_TRANS_TABLES モード値によって選択された) ストリクト モード並びに ERROR_FOR_DIVISION_BY_ZERO について述べます。すべての制約をオンにするには、ストリクト モード値と ERROR_FOR_DIVISION_BY_ZERO を両方共含む TRADITIONAL モ－ドを簡単に使用する事ができます。

mysql> SET sql_mode='TRADITIONAL';

正確タイプ カラム(DECIMAL もしくは整数)に数を挿入すると、カラムの範囲に収まる場合、それは正確な値と一緒に挿入されます。

値に桁数の多い端数部分が含まれている場合には、切り捨てが起こり､警告が生成されます。切り捨ては、セクション 「丸め挙動」 で述べた通りに行われます。

整数部分の桁数が多すぎ、値が大き過ぎる場合、以下の通り処理されます。

アンダーフローは検出されないので、アンダーフローの処理は定義されていません。

デフォルトによって、ゼロでの分割は NULL の結果をもたらしますが、警告は生成されません。ERROR_FOR_DIVISION_BY_ZERO を使って SQL モードを有効にすると、MySQL はゼロでの分割を別途処理します。

言い換えると、ゼロでの分割を実施する式を含む挿入と更新はエラーとして処理する事ができますが、これには、ストリクト モードの他に、ERROR_FOR_DIVISION_BY_ZERO が必要です。

我々がこのステートメントを発行すると仮定して下さい:

INSERT INTO t SET i = 1/0;

これは、ストリクト モードに ERROR_FOR_DIVISION_BY_ZERO モードを組み合わせると発生するものです:

数値カラムへの文字列の挿入に関しては、もし文字列が非数値内容を含んでいたら文字列から数値への変換は次のように行われます。

このセクションでは、ROUND() 関数、そして正確値タイプ（DECIMALと整数）を利用したのカラムへの挿入の為の正確計算の丸めについて説明します。

ROUND() 関数は、引数が正確なものであるか、近似値であるかによって異なった丸めを実行します。

以下の例は、正確な値の丸めと近似値の丸めの違いを示しています。

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+

DECIMAL または整数カラムへの挿入の場合、目標は正確なデータ タイプであるので、挿入すべき値が正確か近似であるかを問わず、丸めには 「round half up,」 が使われます｡

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 |
+--------------------+

正確値の扱いと近似値の扱いの違いを調べる別の方法は、合計に何回も小さい数字を加える事です。変数に .0001 を1,000回加える、以下のストアド プロシージャを考慮してみてください。

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 両方に対する合計は論理的には１であるべきですが、それは小数計算についてだけ言える事です。浮動点計算は小さなエラーを引き起こします:

+--------+------------------+
| d      | f                |
+--------+------------------+
| 1.0000 | 0.99999999999991 |
+--------+------------------+

例2。乗算はスタンダード SQL によって要求されたスケールで実施されます。すなわち、スケール S1 と S2 を持つ２つの数、X1 と X2 に対して、結果のスケールは S1 + S2 です。

mysql> SELECT .01 * .01;
+-----------+
| .01 * .01 |
+-----------+
| 0.0001    |
+-----------+

例3。丸め挙動はよく定義されています。

丸め挙動 (例えば、ROUND() 関数を使用)は基礎をなしている Ｃ ライブラリのインプリメンテーションから独立していて、それは、結果はプラットホームが変わっても一貫している事を意味します。

正確な値のカラム(DECIMALと整数)と正確な値の数に対する丸めには、「round half up」 規則が使われます。.5 以上の端数を持つ値は、ここに示すように、ゼロから最も近い整数に丸められます。

mysql> SELECT ROUND(2.5), ROUND(-2.5);
+------------+-------------+
| ROUND(2.5) | ROUND(-2.5) |
+------------+-------------+
| 3          | -3          |
+------------+-------------+

しかしながら、浮動小数点値の丸めには、多くのシステム上で 「round to nearest even」 規則を使用している Ｃ ライブラリが使用されます。このようなシステム上の端数付きの値は最も近い偶整数に丸められます。

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.01 sec)

mysql> INSERT INTO t SET i = 128;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT i FROM t;
+------+
| i    |
+------+
|  127 |
+------+
1 row in set (0.00 sec)

しかし、ストリクト モードが有効であると、オーバーフロー条件が発生します。

mysql> SET sql_mode='STRICT_ALL_TABLES';
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;
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 をセットすると、ゼロによる除算がエラーを引き起こし、NULL の結果は得られません。

非ストリクト モードにすると、ゼロによる除算が NULL の結果をもたらします:

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SET i = 1 / 0;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT i FROM t;
+------+
| i    |
+------+
| NULL |
+------+
1 row in set (0.03 sec)

しかし、適当な SQL モードを有効にすると、ゼロによる除算はエラーとなります。

mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
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 = 1 / 0;
ERROR 1365 (22012): Division by 0

mysql> SELECT i FROM t;
Empty set (0.01 sec)

例6。MySQL 5.0.3より前のバージョン(精密計算が導入る前)では、正確値リテラルと近似値リテラルは両方共、ダブル精密浮動点値に変換されます。

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 4.1.18-log |
+------------+
1 row in set (0.01 sec)

mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESCRIBE t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | double(3,1) |      |     | 0.0     |       |
| b     | double      |      |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

MySQL 5.0.3以降のバージョンでは、近似値リテラルは浮動点に変換されますが、正確値リテラルは DECIMAL として処理されます。

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.6-alpha-log |
+-----------------+
1 row in set (0.11 sec)

mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESCRIBE t;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| a     | decimal(2,1) unsigned | NO   |     | 0.0     |       |
| b     | double                | NO   |     | 0       |       |
+-------+-----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

例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 5.0.3 (精密計算が 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    |       |
+--------+--------------+------+-----+---------+-------+

引数のタイプに関係なく、結果はダブルとなります。

MySQL 5.0.3による結果:

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    |       |
+--------+---------------+------+-----+---------+-------+

結果は、浮動点引数に対してだけダブルとなります。正確なタイプの引数の場合、結果も正確なタイプの物となります。