Knowledge‎ > ‎

MySQLデータ型一覧 (詳細)


MySQLのデータ型についてです。公式ドキュメントでは「数値型・日時型・文字列型」の2種に分類しています。BIT型が数値型に分類されていたり、BLOBが文字列扱いされていることが釈然としないのですが、このページも一応、大きな分類は公式ドキュメントにしたがって行っています。よく使う型のみをシンプルにまとめた情報をお求めの方は、MySQLデータ型一覧を参照してください。

数値型

中分類 型名 小分類 値の範囲 符号なしの範囲 必要サイズ
整数型 TINYINT, INT1 1バイト整数 -128~127 0~255 1 byte
SMALLINT, INT2 2バイト整数 -32768~32767 0~65535 2 byte
MEDIUMINT, INT3 3バイト整数 -8388608~8388607 0~16777215 (1677万) 3 byte
INT, INTEGER, INT4 4バイト整数 -2147483648~2147483647 0~4294967295 (43億) 4 byte
BIGINT, INT8 8バイト整数 -9223372036854775808~
9223372036854775807
0~18446744073709551615
(1844京)
8 byte
科学計算用小数点数 FLOAT
FLOAT(p) p:0~24 ※1
FLOAT4
単精度浮動小数点数
-3.402823466E+38~
3.402823466E+38
0~3.402823466E+38
4.0より古いと符号無しにはできない
4 bytes
DOUBLE
FLOAT(p) p:25~53 ※1
FLOAT8
DOUBLE PRECISION
REAL
倍精度浮動小数点数 -1.7976931348623157E+308~
1.7976931348623157E+308
0~1.7976931348623157E+308
4.0より古いと符号無しにはできない
8 bytes
FLOAT(M,D) (桁指定)単精度
浮動小数点数
M:1~255
D:0~30 かつ M ≧ D
負の値が使えなくなる
4.0より古いと符号無しにはできない
4 bytes
DOUBLE(M,D)
DOUBLE PRECISION(M,D)
REAL(M,D)
(桁指定)倍精度
浮動小数点数
M:1~255
D:0~30 かつ M ≧ D
負の値が使えなくなる
4.0より古いと符号無しにはできない
8 bytes
事務計算用小数点数 DECIMAL(M,D)
DEC(M,D)
NUMERIC(M,D)
FIXED(M,D)
固定小数点数 M:1~65 ※2
D:0~30 かつ M ≧ D
負の値が使えなくなる
4.0より古いと符号無しにはできない
※3
その他 BIT(M) BIT型 M:1~64 UNSIGNEDは指定できない 約 (M+7)/8 bytes
BOOL
BOOLEAN
TINYINT(1)
真偽値型 -128~127 BOOL, BOOLEANの場合にはUNSIGNEDは指定できない 1 byte
FLOAT, DOUBLE, DECIMALの(M,D)は、Mが全体の桁数、Dが小数部の桁数を表します。FLOAT, DOUBLEで桁数指定を省略すると、内部では別の型になります。DECIMALで桁数指定を省略すると「10,0」になります。またMだけ指定した場合にはDは0になります。いずれも小数部がない状態になりDECIMALを使用する意味がなくなりますので、必ず必要な桁数を考えて指定するようにしましょう。

数値型ではUNSIGNEDオプションを付加することで、負の数を禁止することができます。整数型で使用すると、その分正の整数で使用できる範囲が広がり、最大値が約2倍になります。また、ZEROFILLオプションを指定すると、上位の桁から「表示桁数」に足りない桁が、0で埋められます。例えば「INT ZEROFILL」で値が12345の場合は「0000012345」になります。「表示桁数」のデフォルト値は以下の表のとおりです。表示桁数は「INT(8)」等とすることで、任意の値を設定できます。が、ZEROFILLオプションや表示桁数の指定は、プログラムからDBにアクセスする際には関係ないのであまり使いません。なお、ZEROFILLオプションを指定すると、UNSIGNEDオプションが自動的に付加されます。

UNSIGNEDなし UNSIGNEDあり
TINYINT 4 3
SMALLINT 6 5
MEDIUMINT 9 8
INT 11 10
BIGINT 20 20


数値型では多くの別名が用意されています。上記の表中の「型名」の欄に複数の型名が書いてある場合、太字が実際に定義される実体で、他はシノニムです。MySQLでは真偽値型が用意されていません。しかしあまり気にせずシノニムである「BOOL」を指定すれば(内部的にはTINYINT(1)になりますが)、特に意識せずに真偽値型と同じように使えます。

※1. FLOAT(p)は、pに0~24を指定したときにはFLOAT、25~53を指定したときにはDOUBLEになります。例えば0だろうと24だろうと全く同じFLOATになり、格納できる値の範囲に一切影響しません。マニュアルには「ODBC互換として用意している」と書いてあります。数字を指定するだけ無駄なので、FLOAT, DOUBLEと指定した方がいいでしょう。

※2. 5.0.3~5.0.5は64。それ以前のバージョンでは、DOUBLE型と同じでした。

※3. DECIMALの必要容量は複雑で、私には日本語では説明できません…。5.0.3以降のバージョンでは式で書くとこんな感じです。
整数部 = M - D
小数部 = D
int(整数部 / 9) * 4 +
int(((整数部 % 9) + 1) / 2) +
int(小数部 / 9) * 4 +
int(((小数部 % 9) + 1) / 2)
5.0.2以前はこうでした。こっちなら簡単なのに。計算方法が大きく変わっていることから、情報の持ち方が大きく変わっていることが容易に推測できます。
M < D のとき:D + 2
D > 0 のとき:M + 2
D = 0 のとき:M + 1

日時型

型名 値の範囲 必要サイズ
5.6.4より前

5.6.4以降
YEAR ※1 年型 4桁の場合:'1901' to '2155'
2桁の場合:'70'~'69'。1970~2069の意味。値としては0~99
1 byte 1 byte
DATE 日付型、年月日型 '1000-01-01' to '9999-12-31' 3 bytes 3 bytes
TIME ※2 時刻型 '-838:59:59' to '838:59:59' 3 bytes 3 bytes + fractional seconds storage
DATETIME ※2 日時型 '1000-01-01 00:00:00' to '9999-12-31 23:59:59' 8 bytes 5 bytes + fractional seconds storage
TIMESTAMP ※2 タイムスタンプ型 '1970-01-01 00:00:01' to '2038-01-19 03:14:07' 4 bytes 4 bytes + fractional seconds storage
※1. 「YEAR(2)」は、5.5.27の時点で廃止されました。それ以降のバージョンで「YEAR(2)」を指定するとwarningが発生し、自動的に「YEAR(4)」になります。ちなみに「YEAR(1)」でも「YEAR(4)」でも「YEAR」でもすべて「YEAR(4)」になります。おとなしく「YEAR」にしておけばいいでしょう。そもそも2桁の年は表現できる範囲が少なく、かつぱっと見分かりにくく問題の多い型です。5.5.27より前のバージョンでも使うべきではないでしょう。

※2. MySQL 5.6.4以降では、TIME, DATETIME, TIMESTAMPで最大6桁の「Fractional Seconds」つまり「秒の小数部」を付加できます。指定は「DATETIME(6)」などとします。「DATETIME」「DATETIME(0)」はどちらも「DATETIME」として定義されます。

Fractional Seconds Precision Storage Required
0 0 bytes
1~2 1 byte
3~4 2 bytes
5~6 3 bytes

文字列型

中分類 型名 小分類 値の範囲 必要サイズ
文字列型 CHAR(M) 固定長文字列 255文字以下 M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set.
VARCHAR(M) 可変長文字列 6万5535バイト(64KB)以下 L + 1 bytes if column values require 0 ~ 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYTEXT テキスト型 255バイト以下 L + 1 bytes, where L < 2^8
TEXT 6万5535バイト(64KB)以下 L + 2 bytes, where L < 2^16
MEDIUMTEXT 1677万7215バイト(16MB)以下 L + 3 bytes, where L < 2^24
LONGTEXT 42億9496万7295バイト(4GB)以下 L + 4 bytes, where L < 2^32
列挙型 ENUM('value1','value2',...) ENUM型 65,535候補。1つ選択 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...) SET型 64候補。複数選択可 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) ※
バイナリ型 BINARY(M) 固定長バイナリ型 255バイト以下 M bytes, 0 <= M <= 255
VARBINARY(M) 可変長バイナリ型 6万5535バイト(64KB)以下 L + 1 bytes if column values require 0 ~ 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB バイナリ・ラージ・オブジェクト
Binary Large OBject
255バイト以下 L + 1 bytes, where L < 2^8
BLOB 6万5535バイト(64KB)以下 L + 2 bytes, where L < 2^16
MEDIUMBLOB 1677万7215バイト(16MB)以下 L + 3 bytes, where L < 2^24
LONGBLOB 42億9496万7295バイト(4GB)以下 L + 4 bytes, where L < 2^32
引数Mには、CHAR, VARCHARは文字数、BINARY, VARBINARYはバイト数を指定します。ただし、VARCHARの上限値は65535バイトです。この値を指定文字エンコーディングの最大バイト数で割った値がMの上限値となります。例えばUTF-8の場合21845が上限値です。

「必要サイズ」中のMとLは、Mは型定義のときに指定する引数の値そのもの、Lは各レコードごとに実際に格納されるデータの長さをあらわします。

※SET型の必要容量の説明には「The size of a SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A SET can have a maximum of 64 members.」とあります。私には候補数に7を足して8で割る意味がわかりません。そんなことをしたら、64候補あった場合、「(64+7)/8」で8.875となりround upしたら9バイトになってしまい、前述の説明と矛盾します。理論的に必要なbit数を計算しても単純に「N/8 bytes」で足りるはずですので、公式ドキュメントの間違いではないかと思っています。

データ型の選定

例えばデータが数十件しかないようなマスタのIDにINTを使ったとします。本来ならTINYINTで十分です。TINYINTだと1レコードにつき1バイト、INTだと1レコードにつき4バイト使います。つまり1レコードにつき3バイトの差があります。このマスタを参照するトランザクションデータが10億件あったとすると、それだけで無駄に2.8GBの容量を食うことになります。無駄な定義をしているカラムが他にもたくさんあったら、データが1000億件だったらと考えると恐ろしいほどの数字になります。一方で10万件であれば293KB程度の容量の違いなので、特に気にする必要のない程度の差でしょう。

カラムの型の選定は、初心者や個人の趣味や小規模開発などの場合には、整数を扱うならINT、小数を扱うならDECIMALと考えておけば十分でしょう。大した差も出ないのにどの型を使うか考える時間が無駄です。一方で大規模開発の場合には、データ量の増加によるディスクやメモリの容量がネックになる場合があるので、必要な量をきちんと考えて型を選ぶようにしましょう。

文字列型ではCHARかBINARYだけを使用するようにし、レコード中で使用する型すべてを固定長の型にすると、DBの処理速度を向上できるといい好んでCHAR型を使おうとする人がいます。確かに論理的には、全表走査時の速度を上げられるでしょうし、レコードの更新の際は、元のデータ領域に収まりきれず場所移動などということも発生しませんし、追加・削除を繰り返しても削除した場所に必ずきれいに新しいレコードを収められるので、データ領域が歯抜けになることもなく、使っているうちにだんだん遅くなることもないでしょう。

しかし実際に速くなるかどうか、速くなるのだとしてもどの程度速くなるかは、サーバーのCPU・メモリ・ディスクの種類や性能と、レコードの長さや構成、それからデータ数によって変わります。本当に速くなるのかどうか、どの程度速くなるのかは、各自の本番のシステム・レコード構成・データ数そのものもしくは相当量で確かめましょう。確かめもせずに言い伝えのみを信じても意味がありません。

実際には型の違いで発生する速度の差は無視できるほど小さいはずで、ハードウェア構成の違いやプログラム処理の内容の方がはるかに大きいはずであり、むしろ多くのデータ数を格納できるようにより省スペースの型を選ぶことの方が重要になるはずです。

余談

DECIMALの必要容量に関しての情報のまとめがやや大変でした。公式ドキュメントの説明はやや難しく、この必要容量の計算について他の日本語サイトでは5.0.3以降の情報が正しく書いてあるサイトは一つも見つかりませんでした。必要容量については何も書いていないサイトや、書いてあっても5.0.2以前の情報しか書いていサイトばかりでした。ちなみに5.0.2は2004年12月1日のリリース、5.0.3は2005年3月23日のリリースです。なので、5.0.2以前の情報ということはすでに10年以上前の情報ということになります。理由はよく分かりませんが、MySQLは新鮮な情報はネット上には少ないんだなぁと感じました。あまり使われていないということもないと思うのですが、なぜなんでしょう?。

2014/12/09