41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

decimal(n,0)の使い道

MySQLの数値型

整数型 (真数値)だと - INTEGER - INT - SMALLINT - TINYINT - MEDIUMINT - BIGINT

固定小数点型 (真数値)だと - DECIMAL - NUMERIC

浮動小数点型 (概数値) - FLOAT - DOUBLE

ビット値型 - BIT

という風になっています。

社内のDBでdecimal(31,0)というのがいた。

mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE from information_schema.columns where table_schema = 'xxxxxxxxxxxxxxxx';
+------------------+-------------------------+------------------+---------------+
| TABLE_SCHEMA     | TABLE_NAME              | COLUMN_NAME      | COLUMN_TYPE   |
+------------------+-------------------------+------------------+---------------+
| xxxxxxxxxxxxxxxx | xxxxxxx_xxxxxxxxx       | xxxxxxxxxxxxx_id | decimal(31,0) |
+------------------+-------------------------+------------------+---------------+
1 rows in set (0.01 sec)

これ、連携元のデータがvarchar(31)なんですが、連携先ではこいつをキーにしたいのでxxxxxxxxxxxxx_idとして数字型にして使ってるようです。 (そもそも数字型である理由はよくわからん)

僕は勉強(確認)不足だったなーとあとで反省したのでこれを書いているんですが、bigintでいいじゃん!と思い込んでました。 だって、bigなint なんだからてっきり整数型のかなりの桁のところまで補ってくれるんだと勝手に思ってたんです。

だから、なんで固定小数点型の小数点を使わない形でdecimalなんて使ってるんだ?変なの、とも。

bigintじゃ桁不足。

先に言っちゃうと、bigint は
-9223372036854775808~9223372036854775807
もしくは
0~18446744073709551615 までしか入りません。

f:id:next4us-ti:20190412224526p:plain

dev.mysql.com

つまり、19桁もしくは20桁の途中までということです。

OneのDBではvarchar(31)

当然bigintじゃ入らない、ということになります。

検証してみます

まず符号付きでbigint を検証します。

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table if exists ketasu;
Query OK, 0 rows affected (0.25 sec)

mysql> CREATE TABLE `ketasu`(
    -> `keta_bigint` bigint NOT NULL,
    -> `keta_varchar20` varchar(20) NOT NULL,
    -> `keta_decimal19` decimal(19,0) NOT NULL
    -> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.36 sec)

※マイナス記号が入るのでvarchar(20)にしています

データを入れてみます。

mysql> insert into `ketasu` values(-9223372036854775808,'-9223372036854775808',-9223372036854775808);
Query OK, 1 row affected (0.07 sec)

これは範囲内なので当然入ります。 でも、次のが入りません。

mysql> insert into `ketasu` values(-9223372036854775809,'-9223372036854775809',-9223372036854775809);
ERROR 1264 (22003): Out of range value for column 'keta_bigint' at row 1

bigint であふれてるから駄目だよというメッセージが出ています。

同じように正の数値でもbigintのカラムであふれます。

mysql> insert into `ketasu` values(9223372036854775807,'9223372036854775807',9223372036854775807);
Query OK, 1 row affected (0.06 sec)

mysql> insert into `ketasu` values(9223372036854775808,'9223372036854775808',9223372036854775808);
ERROR 1264 (22003): Out of range value for column 'keta_bigint' at row 1

符号無しでもやってみましょう。

mysql> drop table if exists ketasu_not_fugo;
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE `ketasu_not_fugo`(
    -> `keta_bigint` bigint UNSIGNED NOT NULL,
    -> `keta_varchar20` varchar(20) NOT NULL,
    -> `keta_decimal20` decimal(20,0) NOT NULL
    -> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.29 sec)

やはりbigintで桁あふれしました。

mysql> insert into `ketasu_not_fugo` values(18446744073709551615,'18446744073709551615',18446744073709551615);
Query OK, 1 row affected (0.05 sec)

mysql> insert into `ketasu_not_fugo` values(18446744073709551616,'18446744073709551616',18446744073709551616);
ERROR 1264 (22003): Out of range value for column 'keta_bigint' at row 1

decimal(n,0)の使い道

varchar等の文字列などでbigintでは入りきらない桁の番号の場合、小数点以上の部分については65桁までOKのようです。
https://dev.mysql.com/doc/refman/5.6/ja/precision-math-decimal-characteristics.html

文字列型から数値型に変換したいときはdecimal(n,0)を使うといいです(いいのか?)

でも

正直気持ち悪いんですよね。 Oracle等のRDBMSを触ってきてると、整数値型の上限低くない?と思っちゃいました。 まあ、そもそもうちの使い方(データ連携)もおかしいような気もしますが…。