MySQLの整数型に関して #MySQL

初めに

MySQLの整数型に関して勉強した内容を投稿する。

環境

■ MySQLのバージョン
5.7.35

■ 今回使用するテーブル設計

CREATE TABLE `new_table` (
  `id` tinyint(1) NOT NULL,
  `TINYINT` tinyint(11) DEFAULT NULL,
  `TINYINT_UNSIGNED` tinyint(11) unsigned DEFAULT NULL,
  `TINYINT_ZEROFILL` tinyint(5) unsigned zerofill DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

※既にid 1 にレコードを挿入した状態。

整数型の種類

MySQLの整数型には「TINYINT型」「SMALLINT型」「MEDIUMINT型」「INT型」「BIGINT型」がある。
それぞれ、ストレージ、最小値、最大値が異なっているので格納する値によって使い分ける必要がある。

詳細は下記を参照。
MySQL公式

TINYINT型

今回は、例としてTINYINT型を使用する。
TINYINT型のストレージは1バイト、最小値は（-128/0）最大値は（127/255）である。
※値は（符号付き/符号なし）で分けている。

最大値を更新

冒頭に記載したテーブルのTINYINT型に、最大値（127/255）を更新してみると、正常に意図した値が格納される。

UPDATE `test_schema`.`new_table` SET `TINYINT`=	128 WHERE `id`='1';
UPDATE `test_schema`.`new_table` SET `TINYINT_UNSIGNED`= 255 WHERE `id`='1';

次に最大値を超える値を更新しようとする。

UPDATE `test_schema`.`new_table` SET `TINYINT`=	129 WHERE `id`='1';
UPDATE `test_schema`.`new_table` SET `TINYINT_UNSIGNED`= 256 WHERE `id`='1';

値が最大値のままになっていることが分かる。
更新前の値が最大値でなくとも、最大値を超えた値を更新しようとすると最大値に更新されるようだ。

TINYINT型以外も同じ挙動を確認した。
また、最小値より小さい値を更新しようとすると、最大値と同じように最小値で更新されることが分かった。

インサート文でも同じように最大値を超えた値を挿入しようとすると、最大値で値が格納されている。

INSERT INTO `test_schema`.`new_table` (`id`, `TINYINT`, `TINYINT_UNSIGNED`) VALUES ('2', 129, 256);

ただし以下のメッセージが返却される。

1 row(s) affected, 2 warning(s): 1264 Out of range value for column 'TINYINT' at row 1 1264 Out of range value for column 'TINYINT_UNSIGNED' at row 1

()内の桁数に関して

()内の桁数はzerofillを指定した場合に有効となる。
※MySQLでは、zerofillを指定すると、自動的にUNSIGNED 属性を追加する。

冒頭のテーブル設計で記載した「TINYINT_ZEROFILL」カラムを使用する。

1を更新してみる。

UPDATE `test_schema`.`new_table` SET `TINYINT_ZEROFILL`=1 WHERE `id`='1';

()内の数値は5だったため、0が1の前に4つ付与されていることが分かる。

外部キー制約

主キーと外部キーを設定する場合に同じ型でないとエラーができるので注意が必要である。

まとめ

整数型を使用する際に、基本的には使用するストレージの容量が小さい型から優先的に使用するべきだと考える。
そのため、基本的な優先順位は以下になる。
1．TINYINT型
2．SMALLINT型
3．MEDIUMINT型
4. INT型
5. BIGINT型

また、負の数を使用しないのであれば基本的にunsigned属性を付与して、よりストレージの容量が小さい型を選択すべきだと考えられる。