みなさんはサロゲートキーを使うことはあるでしょうか。何らかのフレームワークを使っている場合には、望んでも望まなくても使わざるをえない場合もありますが、利便性の面で自身で定義する場合もあると思います。その場合にはテーブルにidカラムを用意して、autoincrementを付けてという形で使用するかもしれません。
さて、その際には型は何を使いますか?というところで、今回は、何気なく使っている数字ですが、予期せず使っていて問題が起こってしまうことも多いため、MySQLで扱える整数型について紹介をしていきます。
検証環境
今回は、dockerで建てたMySQLを使用します。以下のコマンドでdockerを建てて、ローカルからアクセスをします。
執筆時点では、以下の通りMySQL バージョン8.0.28を使用しております。
今回のテスト用に、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に適した形にしてくれるので、特にこだわりがない場合はこれを用いるのが良いでしょう。
使用してみる
さて、整数型を使用してみようと思った時に以下のような形でテーブルを作ってみます。
先述のとおり、booleanは、tinyintのエイリアスで、serialはBIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
のエイリアスなため、show create tableで確認してみるとboolean_testはtinyintで、serial_testのカラムは符号なしbigintのNOT NULL制約付きのオートインクリメントなカラムで作成されていて、ユニークキーがはられていることがわかります。
基本的な使い方としては カラム名 型名[(表示桁数)] [unsigned] [zerofill]
と言った形で使用ができます。型名の後ろにおいてある数字はzerofillされる場合に用いられ、その桁数以下だった場合左側に0を入れてくれるものになります。実際に挙動を試してみましょう。
それぞれのカラムに最大値を入れてみます。
zerofillの値を255に入れたためint_max_zerofill
カラムでは255桁の文字が表示されているのがわかります。zerofillを使っていない場合は、数字にあまり意味が無いことがわかります。また、boolean_testはtrueを入れましたが、1に自動的に変換されていることがわかります。
ちなみにそれぞれ上限、下限を超えた場合は、以下のような、範囲外の値をカラムに入れようとした場合に発生するエラーになります。
unsignedの場合でも、以下のように同様のエラーが発生します。
まとめ
今回はMySQLで扱える整数型に関して紹介しました。基本的なデータ型ではあるのですが、エッジケースでときどき予期せぬ挙動を起こしてしまうこともあるため、十分に設計をして使用するようにしましょう。
特に、あまり出くわすことは無いのですが、idに使っている場合などに符号なしと符号ありが混じっていないか、IDとして使っている場合はSERIALなどに変更ができないか確認しておきましょう。