🐬

MySQLのtinyint(1)型には-128~127まで登録できます！

chilitreat

2024/01/16に公開

MySQL

tech

はじめに

こんにちは shiratahです。
今回SQL ServerからMySQLへのDB移行を行う中で、「MySQLのtinyint(1)型は、Booleanを登録する用のデータ型なので、0か１しか格納できない」 という噂話を聞いたので、実際に検証しました。

結論はもうタイトルに書いてますがお付き合いください。

まとめ

忙しい人向けに検証結果のまとめです。

tinyint(M) のMは表示幅なので列に格納できる値の範囲指定ではない
MySQLのtinyint(1)型は-128~127まで登録可能（tinyint型でも同じ）
MySQLのbool型は実質 tinyint(1)型
MySQL 8.0.16で入ったCHECK制約を使うことでより厳密に登録できる値の制御が可能

MySQLにおいてtinyint(1)型の (1) は不要なので、シンプルにtinyint型を使おう！

以下ではこれらを実際に手を動かしながら検証していきます。

検証すること

「MySQLのtinyint(1)型は、Booleanを登録する用のデータ型なので、0か１しか登録できない」 を検証するために、以下の3つの観点を確認してみます。

No.	検証内容	予想
1.	tinyint型に格納できる値は-128~127である？	桁数を指定しない場合、tinyint型の最大長まで格納できる（みんな知ってる、仕様通りの動作）
2.	tinyint(1)型に格納できる値は0, 1のみである？	桁数を指定した場合、その桁の範囲まで格納できるはず（今回の検証の本題）
3.	bool型に格納できる値は0, 1のみである？	tinyint(1)型よりもっとフラグらしい型なので0, 1しか格納できないはず

検証環境

検証には、ウェブ上でSQLを手軽に試せるDB Fiddleを使います。
検証に使用するMySQLは、mysql v8.0 を使用します。

下記リンクから実際に検証に使ったクエリを動かせるので実際に手を動かしたい人はご活用ください。

DDL

全然味気のないテーブルですが今回は検証ができれば良いので、以下のDDLで作成したテーブルで検証します。

CREATE TABLE test (
  flag1 tinyint unsigned,     -- 1. の検証用
  flag2 tinyint(1) unsigned,  -- 2. の検証用
  flag3 bool                 -- 3. の検証用
);

作成したテーブルにデータがINSERTできるか試してみます。

# フラグがオン
INSERT INTO test(flag1, flag2, flag3) VALUES (1, 1, 1);
# フラグがオフ
INSERT INTO test(flag1, flag2, flag3) VALUES (0, 0, 0);
SELECT * FROM test;

結果

flag1（tinyint unsigned）	flag2（tinyint(1) unsigned）	flag3（bool）
1	1	1
0	0	0

よくある使い方ですね、ではここから実際に検証していきましょう。

tinyint(1)型に127をINSERTしてみる

まず迷信　「tinyint(1)型は0, 1しか格納できない」が本当に迷信なのか試してみましょう。
せっかくなのでtinyint型の最大長の127をINSERTしてみます。

INSERT INTO test(flag1, flag2, flag3) VALUES (127, 127, 127);
SELECT * FROM test;

結果

flag1（tinyint unsigned）	flag2（tinyint(1) unsigned）	flag3（bool）
127	127	127

格納できた！！！！！！！！！！！！！！

早速迷信が迷信になったのですが、「2. unsigned tinyint(1)型に格納できる値は0, 1のみである？」はすでに間違いであることが判明しました。

そしてしれっと、bool型にも127が格納できていることから、「3. bool型に格納できる値は0, 1のみである？」も間違いであることが判明しました。

検証結果

これまでの検証をまとめると以下のようになります。

No.	検証内容	結果
1	tinyint型に格納できる値は-128~127である	正しい(というか仕様通り)
2	tinyint(1)型に格納できる値は0, 1のみである	間違い(127を格納できた)
3	bool型に格納できる値は0, 1のみである	間違い(tinyint(1)型と同様に127を格納できた)

では、これまでtinyint型を使った際に指定してた(1)は何だったのか？ですが、答えはMySQL 8.0リファレンスマニュアル　11.1.1 数値データ型の構文の冒頭に書いてありました。

整数データ型の場合、M は最大表示幅を示します。最大表示幅は 255 です。表示幅は、セクション11.1.6「数値型の属性」で説明されているように、型が格納できる値の範囲とは無関係です。

数値型の表示幅とは何か？

こちらもMySQL 8.0リファレンスマニュアル　11.1.6 数値型の属性　に書いてありました。

MySQL では、整数データ型の基本キーワードに続く括弧内で、その型の表示幅をオプションで指定する拡張をサポートしています。たとえば、INT(4) は、4 桁の表示幅の INT を指定しています。このオプションの表示幅は、左側をスペースでパディングすることによって、カラムに対して指定された幅よりも狭く整数値を表示するために、アプリケーションで使用される場合があります。 (つまり、この幅は結果セットで返されるメタデータの中にあります。使用されるかどうかはアプリケーションによって決まります。)

ゼロ埋め有り設定で表示幅指定5（tinyint(5)型）とした場合、　格納した1という値をテーブルから取り出すと 00001という値が取得できるといった塩梅です。

またこのゼロ埋め処理については、MySQL 8.0.17では非推奨となっています。
数値のゼロ埋めしたい場合普通アプリケーション側で対応するよなぁ、と思っていたので自分の感覚は間違ってなかったようです。

MySQL 8.0.17 では、ZEROFILL 属性は数値データ型では非推奨です。将来のバージョンの MySQL ではサポートされなくなる予定です。この属性の効果を生成する別の方法の使用を検討してください。たとえば、アプリケーションでは、LPAD() 関数を使用して、必要な幅まで数値をゼロ埋めたり、書式設定された数値を CHAR カラムに格納したりできます。

bool型は、実質tinyint型？

先ほどの検証で、bool型で定義した列に１２７(tinyint型の最大長)が格納できたため、MySQLのbool型はいわゆるJavaScriptのようなプログラミング言語のBoolean型とは異なるものであることがわかりました。

この動作については、MySQL 8.0リファレンスマニュアル　11.1.1 数値データ型の構文　に書いてありました。

BOOL、BOOLEAN

これらの型は TINYINT(1) のシノニムです。ゼロの値は false と見なされます。ゼロ以外の値は true と見なされます。

「シノニム」、いまいち聞きなれない単語ですが、　「同義語」という意味らしいです。エイリアスと言い換えても良いかもしれません。

では、ここでテーブル定義をDESCRIBE（DESC）してみます。

DESC test;

結果

Field	Type	Null	Default
flag1	tinyint	YES	null
flag2	tinyint(1)	YES	null
flag3	tinyint(1)	YES	null

DDLでは、flag3カラムの型は、boolと指定したにもかかわらず、tinyint(1)が返ってきたので、bool型は、実質tinyint(1)型と言えそうですね。

余談: tinyint型に格納できる値を0か1に制限したい場合

たとえば、MySQLに接続するアプリケーション側で必ずBoolean型にキャストしてからINSERTを実行することで、tinyint(1)型に0か1しか格納されないようにすることはできると思います。

しかし、時にはアプリケーションの実装時にキャストを忘れてそのままINSERTしまうこともあると思います。そういった場合は、どのようにして列に不正な値が含まれてしまうのを防ぐことはできるのでしょうか？

こちらについては、MySQL 8.0.16で入ったCHECK制約を使うことでより厳密に登録できる値の制御が可能なようです（未検証）

結論

再掲になりますが、以下4点が今回の検証で分かったことです。

tinyint(M) のMは表示最大幅なので列に格納できる値の範囲指定ではない
MySQLのtinyint(1)型は-128~127まで登録可能（tinyint型でも同じ）
MySQLのbool型は実質 tinyint(1)型
MySQL 8.0.16で入ったCHECK制約を使うことでより厳密に登録できる値の制御が可能

MySQLにおいてtinyint(1)型の (1) は不要なので、シンプルにtinyint型を使おう！

最後に

今回の検証で、char型やvarchar型では、varchar(20)のように格納できる文字列の長さを指定するため、数値型もとりあえずtinyint(1)のように長さをつけておくかー、と雑な理解をしていたのが自信を持って(1)は不要だよねと言えるようになったのでよかったです。（自分がこれまで担当してきたサービスでも　tinyint(1)、int(11)と指定してるものが多い気がします🙇）

この記事は、誰かに　「tinyintじゃなくて、tinyint(1)な？」と指摘されたらカウンターとして使ってください。

それでは良いお年を！

株式会社アイスタイルPublication

コスメ・美容の総合サイト@cosme（アットコスメ）や関連サービスの企画・運営をしています。