MySQL道普請便り

第173回MySQLで整数型を扱ってみる

みなさんはサロゲートキーを使うことはあるでしょうか。何らかのフレームワークを使っている場合には、望んでも望まなくても使わざるをえない場合もありますが、利便性の面で自身で定義する場合もあると思います。その場合にはテーブルにidカラムを用意して、autoincrementを付けてという形で使用するかもしれません。

さて、その際には型は何を使いますか?というところで、今回は、何気なく使っている数字ですが、予期せず使っていて問題が起こってしまうことも多いため、MySQLで扱える整数型について紹介をしていきます。

検証環境

今回は、dockerで建てたMySQLを使用します。以下のコマンドでdockerを建てて、ローカルからアクセスをします。

% docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest
% % mysql -uroot -pmy-secret-pw

執筆時点では、以下の通りMySQL バージョン8.0.28を使用しております。

mysql>  select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.01 sec)

今回のテスト用に、test データベースを以下のクエリで作っておきます。

mysql> create database test;
mysql> use test

整数型

サロゲートキーなどで使われていることも多く、アプリケーション開発を行っていると、なんやかんやで一番目にすることが多くなる数値型になると思います。

文字通り整数を扱うための型になります。型として指定できるのは次のものになります。TINYINT、SMALLINT、MEDIUMINT、INT・INTEGER、BIGINTとなります。それぞれサイズが異なり、以下の表のような値になります。

記憶領域 符号付き 最小値 最大値
TINYINT 1バイト あり -128 127
TINYINT 1バイト なし 0 255
SMALLINT 2バイト あり -32768 32767
SMALLINT 2バイト なし 0 65535
MEDIUMINT 3バイト あり -8388608 8388607
MEDIUMINT 3バイト なし 0 16777215
INT 4バイト あり -2147483648 2147483647
INT 4バイト なし 0 4294967295
BIGINT 8バイト あり -2^63 2^63-1
BIGINT 8バイト なし 0 2^64-1

またTINYINTを真偽値として使用する場合もありますが、その場合は0がfalseで、0以外がtrueとなります。BOOLEANはTINYINT(1)のエイリアスなため、自動でTINYINTに変換がされます。

自身でユーザIDを定義した場合に、うっかりunsignedにし忘れると行が非常に増えるテーブルの場合IDがマイナスになってしまうなどの問題が起こる場合があります。外部キーを指定した時に、そちらではunsignedにしていたりすると面倒なので注意しましょう。

具体的には、符号なしに揃えられてしまうので、JOINした時に予期しない値が取れてしまう可能性があります。 BIGINTを自身で定義しても良いのですが、SERIALで定義を行うと、BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUEという形でIDに適した形にしてくれるので、特にこだわりがない場合はこれを用いるのが良いでしょう。

使用してみる

さて、整数型を使用してみようと思った時に以下のような形でテーブルを作ってみます。

mysql> create table int_test(int_one INT(1), int_max INT(255),int_unsigned INT unsigned ,int_one_zerofill int(1) zerofill, int_max_zerofill int(255) zerofill, serial_test serial , boolean_test boolean);

先述のとおり、booleanは、tinyintのエイリアスで、serialはBIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUEのエイリアスなため、show create tableで確認してみるとboolean_testはtinyintで、serial_testのカラムは符号なしbigintのNOT NULL制約付きのオートインクリメントなカラムで作成されていて、ユニークキーがはられていることがわかります。

mysql> show create table int_test\G
*************************** 1. row ***************************
       Table: int_test
Create Table: CREATE TABLE `int_test` (
  `int_one` int DEFAULT NULL,
  `int_max` int DEFAULT NULL,
  `int_unsigned` int unsigned DEFAULT NULL,
  `int_one_zerofill` int(1) unsigned zerofill DEFAULT NULL,
  `int_max_zerofill` int(255) unsigned zerofill DEFAULT NULL,
  `serial_test` bigint unsigned NOT NULL AUTO_INCREMENT,
  `boolean_test` tinyint(1) DEFAULT NULL,
  UNIQUE KEY `serial_test` (`serial_test`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.03 sec)

基本的な使い方としては カラム名 型名[(表示桁数)] [unsigned] [zerofill]と言った形で使用ができます。型名の後ろにおいてある数字はzerofillされる場合に用いられ、その桁数以下だった場合左側に0を入れてくれるものになります。実際に挙動を試してみましょう。

mysql>  insert into int_test (int_one, int_max,int_unsigned, int_one_zerofill, int_max_zerofill, boolean_test) VALUE (2147483647, 2147483647, 4294967295, 2147483647, 2147483647, true);
Query OK, 1 row affected (0.01 sec)

それぞれのカラムに最大値を入れてみます。

mysql> select * from int_test\G
*************************** 1. row ***************************
         int_one: 2147483647
         int_max: 2147483647
    int_unsigned: 4294967295
int_one_zerofill: 2147483647
int_max_zerofill: 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002147483647
     serial_test: 1
    boolean_test: 1
1 row in set (0.01 sec)

zerofillの値を255に入れたためint_max_zerofillカラムでは255桁の文字が表示されているのがわかります。zerofillを使っていない場合は、数字にあまり意味が無いことがわかります。また、boolean_testはtrueを入れましたが、1に自動的に変換されていることがわかります。

ちなみにそれぞれ上限、下限を超えた場合は、以下のような、範囲外の値をカラムに入れようとした場合に発生するエラーになります。

mysql> insert into int_test (int_one) VALUE (2147483648);
ERROR 1264 (22003): Out of range value for column 'int_one' at row 1
mysql> insert into int_test (int_one) VALUE (-2147483649);
ERROR 1264 (22003): Out of range value for column 'int_one' at row 1

unsignedの場合でも、以下のように同様のエラーが発生します。

mysql> insert into int_test (int_unsigned) VALUE (-1);
ERROR 1264 (22003): Out of range value for column 'int_unsigned' at row 1
mysql> insert into int_test (int_unsigned) VALUE (4294967296);
ERROR 1264 (22003): Out of range value for column 'int_unsigned' at row 1

まとめ

今回はMySQLで扱える整数型に関して紹介しました。基本的なデータ型ではあるのですが、エッジケースでときどき予期せぬ挙動を起こしてしまうこともあるため、十分に設計をして使用するようにしましょう。

特に、あまり出くわすことは無いのですが、idに使っている場合などに符号なしと符号ありが混じっていないか、IDとして使っている場合はSERIALなどに変更ができないか確認しておきましょう。

おすすめ記事

記事・ニュース一覧