３－４.MySQLのテーブルで使用できる列の型

2019年2月17日2021年2月6日

リレーショナルデータベースにおいてテーブル定義する場合はどのようなデータを格納するかしっかり理解して適切な型を割り当てる必要があります。

やみくもに大きな値が格納できる型を設定するとストレージを無駄に消費するうえに処理の負担も増大し結果的にコストがかかることになります。

MySQLで定義できる型には「数値」や「文字列」や「日付」などなど様々です。

まずはそれぞれの型について理解を深めたうえで格納するデータに合わせて適切な設計を心掛けましょう。

１．数値型
２．文字列型
３．日付と時間型
４．論理値型

１．数値型

まずは数値を格納するための型について見て行きましょう。

１）整数型

数値型はその名の通り数値を格納するための型です。
扱う数値の精度によりいくつかの数値型があります。

型の定義 ※[]内の記述は省略可能	UNSIGNEDを省略した場合に格納できる範囲	UNSIGNEDを指定した場合に格納できる範囲	必要サイズ（バイト）
TINYINT [(length)] [UNSIGNED] [ZEROFILL]	-128 〜 127	0 〜 255	1
SMALLINT [(length)] [UNSIGNED] [ZEROFILL]	-32768 〜 32767	0 〜 65535	2
MEDIUMINT [(length)] [UNSIGNED] [ZEROFILL]	-8388608 〜 8388607	0 〜 16777215	3
INT [(length)] [UNSIGNED] [ZEROFILL]	-2147483648 〜 2147483647	0 〜 4294967295	4
BIGINT [(length)] [UNSIGNED] [ZEROFILL]	-9223372036854775808 〜 9223372036854775807	0 〜 18446744073709551615	8

■数値項目の定義においてカッコで指定する数値について

INT (3)のように型の後ろにカッコで数値を指定するのを見かけると思います。
これは勘違いしやすいのですが格納する数値の精度ではなく単に表示出力するときの表示桁数です。

数値の前にゼロを埋める「ZEROFILL」オプションを指定したときに良く分かります。

例）数値項目「SALESPRICE」を前詰めゼロで表示桁数7桁で定義してデータをインサート、SELECTしてみましょう。

mysql> CREATE TABLE TEST01 (SALESPRICE INT(7) ZEROFILL);

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO TEST01 VALUES(123);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO TEST01 VALUES(123456789);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT SALESPRICE FROM TEST01;
+------------+
| SALESPRICE |
+------------+
|    0000123 |
|  123456789 |
+------------+
2 rows in set (0.00 sec)

mysql>

この例では設定された桁数である「7桁」を超える値をインサートしましたが特に問題無く処理されています。

この例でわかる通り設定した桁数は表示に使われる値であり、桁を超えても問題ありませんし格納する値の範囲にも影響はありません。

■「UNSIGNED」指定。負の値の有無による違い

上記で示した格納できる数値の範囲についてですが、例えば「TINYINT」の場合格納できる数値の範囲は、

・「-128 〜 127」
・「0 〜 255」

とあります。これが初級者の方にはよく分からない場合が多く見受けられます。
「なぜ2種類あるの？」と疑問に思いつつも深く考えずにスルーしがちです。
どちらが該当するかは「UNSIGEND」を指定するかしないかです。

負の数を格納しない場合は「UNSIGEND」を指定することで０と正の数値を格納する項目になります。値の範囲は「0 〜 255」となります。
負の数を格納したい場合は「UNSIGEND」を指定しないことで値の範囲が「-128 〜 127」となるのです。

２）固定小数点型

固定小数点型は「DECIMAL」と「NUMERIC」の2つがありますがMySQLでのNUMERICはDECIMALと同じ（シノニム）です。

型の定義
※[]内の記述は省略可能

指定方法

必要サイズ
（バイト）

DECIMAL [(length[,decimals])] [UNSIGNED] [ZEROFILL]

lemgth：整数部と小数部を合わせた桁(最小1,最大65,省略時10）
decimals：小数部の桁(最小0,最大値30,省略時0)

整数部、小数部共に桁が9桁毎に４バイトが必要。

以下の型はDECIMALのシノニム
・NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL] ・DEC [(length[,decimals])] [UNSIGNED] [ZEROFILL] ・FIXED [(length[,decimals])] [UNSIGNED] [ZEROFILL]

DECIMALでデータ格納に必要なバイト数

整数部と小数部それぞれサイズを計算します。
桁数を９で区切った余りで以下のとおりです。

桁を９で割った余り	必要バイト数
0	0
1,2	1
3,4	2
5,6	3
7,8,9	4

格納に必要なバイト数を求めてみる

例えば「DECIMAL (18,5)」だった場合は
整数部は１３桁です。なので９桁の４バイトと余り４桁の２バイトとなり、整数部は６バイトが必要です。
小数部は５桁なので必要バイト数は３バイトです。
合計して９バイトが必要バイト数となります。

３）浮動小数点型

浮動小数点型は以下の2つがあります。

FLOATは単精度浮動小数点型でDOUBLEは倍精度浮動小数点型です。
小数点以下で正確に扱える桁数はFLOATが７桁でDOUBLEで１５桁です。
通常はDOUBLEを使う事が推奨されます。（MySQLの内部ではDOUBLEで処理されるため）

型の定義 ※[]内の記述は省略可能	UNSIGNEDを省略した場合に格納できる範囲	UNSIGNEDを指定した場合に格納できる範囲	必要サイズ（バイト）
FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]	-3.402823466E+38 ～ -1.175494351E-38、 0、 1.175494351E-38 ～ 3.402823466E+38	0、 1.175494351E-38 ～ 3.402823466E+38	4
DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]	-1.7976931348623157E+308 ～ -2.2250738585072014E-308、0、 2.2250738585072014E-308 ～ 1.7976931348623157E+308	0、 2.2250738585072014E-308 ～ 1.7976931348623157E+308	8

スポンサードサーチ

２．文字列型

文字を扱うための型です。
主に使用される型のみ抜粋しています。以下の種類があります。
他の形式がいくつかありますがあまり使用しません。必要に応じて公式HP「文字列型の概要」を参照ください。

型の定義	説明	最大長
CHAR（length)	固定長の文字列を格納するための型です。 lengthは桁を文字数で表し、格納する文字列が定義した桁に満たない場合は空白が埋められます。	255
VARCHAR（length)	可変長の文字列を格納するための型です。 lengthは最大長を文字数で表します。lengthの最大値は65,535です。	65535
TEXT	長い文字列を格納するのに使用されます。	65535

スポンサードサーチ

３．日付と時間型

日付や時刻を格納するための型です。

型の定義	説明	例
DATE	日付を格納する型です。	‘2021-01-01’
DATETIME	日付と時刻を格納する型です。	‘2021-01-01 12:34:56’
TIMESTAMP	タイムスタンプ値を格納します。更新時に値が指定されない場合タイムスタンプが設定されます。	‘2021-01-01 12:34:56’
TIME	時刻を格納する型です。	’12:34:56′
YEAR	年を格納する型です。	‘2021’