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

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オプションが自動的に付加されます。

数値型では多くの別名が用意されています。上記の表中の「型名」の欄に複数の型名が書いてある場合、太字が実際に定義される実体で、他はシノニムです。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

※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」として定義されます。

引数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