第22章 精密計算

目次

22.1. 数値のタイプ
22.2. DECIMAL データ タイプの変更
22.3. 式の取り扱い
22.4. 丸め挙動
22.5. 精密計算の例

MySQL 5.1 は精密計算に対するサポートを提供します。非常に正確な結果をもたらす数値処理と無効な値に対する高度なコントロール。精密計算はこれら2つの特徴に基づいています:

これらの特徴は数値演算に対していくつかの帰結的意味を持っています:

これらの特徴による重要な結果は、MySQL 5.1 はスタンダード SQL に高度に準拠した物を提供するという事です。

以下の部分には、(古いアプリケーションに不適合しない可能性を含めて)精密計算がどのように機能するのかに関するいくつかの局面が含まれています。最後に、MySQL 5.1 がいかに正確に演算を処理するかを明らかにする若干の例を掲載しましたのでご覧ください。SQL モードをコントロールする為の sql_mode システム変数の使用に関する情報については、項4.2.6. 「SQL モード」 をご参照ください。

22.1. 数値のタイプ

正確に演算する為の精密計算の範囲には、正確値データ タイプ(DECIMALタイプと整数タイプ)並びに正確値数値文字が含まれます。近似値データ タイプと数値文字は浮動小数点数として扱われます。

正確な価の数値文字には、整数部分あるいは端数部分もしくは両方が含まれています。それらにサインする事ができます。例: 1, .2, 3.4, -5, -6.78, +9.10.

近似値の数値文字は仮数と指数の付いた科学的な記号で表示されます。いずれかもしくは両方にサインする事ができます。例:1.2E31.2E-3-1.2E3-1.2E-3

同じに見える2つの数は両方とも正確な値か近似値である必要はありません。例えば、2.34 は(固定点の)正確な値ですが、2.34E0 は(浮動点の)近似値です。

DECIMAL データ タイプは固定点タイプで、計算は正確です。MySQL では、DECIMAL タイプは、幾つかの同義語を含んでいます:NUMERICDECFIXED。整数タイプも正確値タイプです。

FLOAT データ タイプおよび DOUBLE データ タイプは浮動点タイプで、計算によって近似値が得られます。MySQL では、FLOAT または DOUBLE を使った同義語のタイプは DOUBLE PRECISION および REAL です。

22.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 では、10進法の値を最高254桁まで持つ事ができました。しかし、計算は浮動小数点を使って実行されていたので、結果は正確でなく、近似値でした。)

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

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

使い残し桁数バイト数
00
11
21
32
42
53
63
74
84
94

(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(MD) に対して、スタンダードは少なくともM 桁の精度を要求していますが、それ以上でも容認されます。MySQL の場合、DECIMAL(M,D) 並びに NUMERIC (M,D) は同じで、両方共正確に M 桁の精度を持っています。

DECIMAL データ タイプの古い処理に依存しているアプリケーションのポーテングに関する詳細情報については、MySQL 5.0 Reference Manual をご参照ください。

22.3. 式の取り扱い

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

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

  • 近似値が存在している場合、式は近似であり、これを、浮動小数点算数を使って評価します。

  • 近似値が存在していない場合、式は正確な値だけを含みます。正確な値に端数部分(少数点の後に続く値)が含まれている場合、式は DECIMAL 正確算数を使って評価され、式は65桁の精度を持ちます。(「exact」 という言葉は、バイナリで表現できる、物の限界を条件と使用しています。例えば、1.0/3.0 は小数表記法で .333... と近似させる事ができますが、正確な数として書かれないので、(1.0/3.0)*3.0 は正確に 1.0 であると評価しません。)

  • そうでなければ、式に整数値だけが含まれます。式は正確で、整数算数を使って評価され、BIGINT (64 ビット)と同じ精度を持っています。

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

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

mysql> SET sql_mode='TRADITIONAL';

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

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

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

  • ストリクト モードが有効でない場合、値は法定値に最も近い値に切り下げられ、警告が生成されます。

  • ストリクト モードが有効である場合、オーバーフロー エラーが発生します。

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

デフォルトによって、ゼロでの分割は 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 モードを組み合わせると発生するものです:

sql_mode結果
'' (デフォルト)警告・エラーなし; iNULL にセット。
ストリクト警告・エラーなし; iNULL にセット。
ERROR_FOR_DIVISION_BY_ZERO警告です。エラーがありません、 iNULL にセット。
ストリクト、ERROR_FOR_DIVISION_BY_ZEROエラー ;行が挿入されていません。

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

  • 数で始まっていない文字列は数として使用する事はできず、これを使用すると、ストリクト モードの中にエラーが発生するか、警報が生成されます。これは空の文字列を含んでいます

  • 数で始まる文字列は変換する事ができますが、添付されている非数値部は切り捨てられます。切り捨て部分にスペース以外の何かが含まれている場合、これによって、ストリクト モードの中にエラーが発生するか、警報が生成されます。

22.4. 丸め挙動

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

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

  • 正確な値の数に対して、ROUND() は 「round half up」 規則を使用します:.5以上の端数を持つ値は、次の整数がポジテブである場合、それに対して切り上げ、ネガティブである場合、それに対して切り下げます。(言い換えると、それはゼロから切り捨てられるという事です。).5以下の端数を持つ値は、次の整数がポジティブである場合、それに対して切り下げ、ネガティブである場合、それに対して切り上げます。

  • 近似値の数に対する結果は C ライブラリによって異なります。多くのシステム上では、これは、ROUND() は 「round to nearest even」 規則を使用する事を意味します:端数部分を持つ値は最も近い偶整数に丸められます。

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

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

22.5. 精密計算の例

このセクションでは、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;

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

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

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

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

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

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

正確な値のカラム(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」 規則を使用している 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.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    |       |
+--------+---------------+------+-----+---------+-------+

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

アダルトレンタルサーバー