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 までしか入りません。
つまり、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を触ってきてると、整数値型の上限低くない?と思っちゃいました。 まあ、そもそもうちの使い方(データ連携)もおかしいような気もしますが…。