Mac De Oracle Heterogeneous! #36 Tweet

数値型の検証のつづき（MySQL編の第４回目）。。decimal型のdecimal(38,0)とdecimal(38,16)、それらの型で符号なし。

１）decimal型

１−１）decimal(38,0)と符号なしdecimal(38,0)

精度はdouble型と同じであるとマニュアルに記載されているので、decimal(38,0)の場合だとOracleのnumber(38,0)より精度が劣るようだ。
decimal型をOracleに取り込むだけなら、精度の範囲内なので利用できそうであるが、OracleからMySQLへ登録したり更新する場合は注意が必要だ。decimal(16,0)くらいなら利用できる可能性はあるが実際に確認してから利用するか否かを決めた方がよさそうである。

mysql> insert into
    ->   num_test(r_decimal_38, r_decimal_38_unsigned)
    ->   values(
    ->     99999999999999999999999999999999999999,
    ->     0
    ->   );
Query OK, 1 row affected (0.00 sec)

mysql> insert into
    ->   num_test(r_decimal_38, r_decimal_38_unsigned)
    ->   values(
    ->     -99999999999999999999999999999999999999,
    ->     99999999999999999999999999999999999999
    ->   );
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into
    ->   num_test(r_decimal_38, r_decimal_38_unsigned)
    ->   values(100000000000000000000000000000000000000,-1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into
    ->   num_test(r_decimal_38,r_decimal_38_unsigned)
    ->   values(-100000000000000000000000000000000000000,100000000000000000000000000000000000000);
Query OK, 1 row affected (0.00 sec)

mysql> select
    ->   r_decimal_38,
    ->   r_decimal_38_unsigned
    -> from
    ->   num_test
    -> limit
    ->   21,-1;
+-----------------------------------------+----------------------------------------+
| r_decimal_38                            | r_decimal_38_unsigned                  |
+-----------------------------------------+----------------------------------------+
|  99999999999999997748809823456034029568 |                                      0 |
| -99999999999999997748809823456034029568 | 99999999999999997748809823456034029568 |
|  99999999999999997748809823456034029568 |                                      0 |
| -99999999999999997748809823456034029568 | 99999999999999997748809823456034029568 |
+-----------------------------------------+----------------------------------------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.39 sec)

mysql> 

MySQL4.0.25では正常に見えるが実は、丸め処理が行われている。MySQL4.1.13aで同じインサート文を発行すると２件のワーニングが返される。内容は以下の通り out of range。そして、r_decimal_38の精度は３８桁なのだが３９桁になってしまった。MySQLのバージョン毎に対応を変えるというか、変えられるような作り込みが必要になる可能性もでてきた。

mysql>
mysql> insert into
    ->   num_test(r_decimal_38, r_decimal_38_unsigned)
    ->   values(
    ->     99999999999999999999999999999999999999,
    ->     0
    ->   );
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert into
    ->   num_test(r_decimal_38, r_decimal_38_unsigned)
    ->   values(
    ->    -99999999999999999999999999999999999999,
    ->    99999999999999999999999999999999999999
    ->   );
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1264 | Data truncated; out of range for column 'r_decimal_38' at row 1          |
| Warning | 1264 | Data truncated; out of range for column 'r_decimal_38_unsigned' at row 1 |
+---------+------+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.06 sec)


mysql> edit
    -> ＼¥g
+-----------------------------------------+----------------------------------------+
| r_decimal_38                            | r_decimal_38_unsigned                  |
+-----------------------------------------+----------------------------------------+
| 100000000000000035527741686413195739136 |                                      0 |
| -99999999999999999999999999999999999999 | 99999999999999999999999999999999999999 |
+-----------------------------------------+----------------------------------------+
2 rows in set (0.00 sec)

mysql> 

１−２）decimal(38,16)と符号なしdecimal(38,16)

これは問題無さそうだと思い見逃しそうになったのだが、よくよく結果をみてみると、decimal(38.16）で最小値はよいのだが、最大値がまずい。精度が２２桁なのに、最大値が２３桁になっている。（これも不具合だろうか？。Generic Connectivity経由ではdecimal型も利用しないほうがよいかもしれない。まだ、テストしていないので使えるか不明だが。。）
以下、MySQL4.0.25での実行結果。

mysql> insert into
    ->   num_test(r_decimal_38_16, r_decimal_38_16_unsigned)
    ->   values
    ->   (-9999999999999999999999.9999999999999999,0),
    ->   ( 9999999999999999999999.9999999999999999,9999999999999999999999.9999999999999999);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> commit;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into
    ->   num_test(r_decimal_38_16, r_decimal_38_16_unsigned)
    ->   values(-10000000000000000000000.99999999999999995,-1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into
    ->   num_test(r_decimal_38_16, r_decimal_38_16_unsigned)
    ->   values(10000000000000000000000.99999999999999994,10000000000000000000000.99999999999999995);
Query OK, 1 row affected (0.00 sec)

mysql> select
    ->  r_decimal_38_16,
    ->  r_decimal_38_16_unsigned
    -> from
    ->  num_test
    -> limit
    ->   23,-1;
+------------------------------------------+-----------------------------------------+
| r_decimal_38_16                          | r_decimal_38_16_unsigned                |
+------------------------------------------+-----------------------------------------+
| -9999999999999999999999.9999999999999999 |                      0.0000000000000000 |
| 10000000000000000000000.0000000000000000 | 9999999999999999999999.9999999999999999 |
| -9999999999999999999999.9999999999999999 |                      0.0000000000000000 |
| 10000000000000000000000.0000000000000000 | 9999999999999999999999.9999999999999999 |
+------------------------------------------+-----------------------------------------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.38 sec)

mysql> 

以下、MySQL4.1.13aでの実行結果。精度が１桁多くなる現象もあるが値も微妙に違う。

mysql> insert into
    ->   num_test(r_decimal_38_16, r_decimal_38_16_unsigned)
    ->   values
    ->   (-9999999999999999999999.9999999999999999,0),
    ->   ( 9999999999999999999999.9999999999999999,9999999999999999999999.9999999999999999);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> 
mysql> commit;
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> insert into
    ->   num_test(r_decimal_38_16, r_decimal_38_16_unsigned)
    ->   values(-10000000000000000000000.99999999999999995,-1);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> 
mysql> insert into
    ->   num_test(r_decimal_38_16, r_decimal_38_16_unsigned)
    ->   values(10000000000000000000000.99999999999999994,10000000000000000000000.99999999999999995);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select
    ->   r_decimal_38_16,
    ->   r_decimal_38_16_unsigned
    -> from
    ->   num_test
    -> limit
    ->   25,99;
+------------------------------------------+-----------------------------------------+
| r_decimal_38_16                          | r_decimal_38_16_unsigned                |
+------------------------------------------+-----------------------------------------+
| -9999999999999999999999.9999999999999999 |                      0.0000000000000000 |
| 10000000000000004194304.0000000000000000 | 9999999999999999999999.9999999999999999 |
| -9999999999999999999999.9999999999999999 |                      0.0000000000000000 |
| 10000000000000002097152.0000000000000000 | 9999999999999999999999.9999999999999999 |
+------------------------------------------+-----------------------------------------+
4 rows in set (0.00 sec)

mysql> 

長いので次回につづく。