MySQLのデータ型の概要をオンラインマニュアルを読んでまとめました。
※目次をクリックすると目次の下部にコンテンツが表示されます。
データ型選択の指針、注意点
・使用するデータサイズを満たす最低限のサイズのデータ型を選択する。
小さいデータ型の方が速く動作し、ディスク、メモリ、CPUキャッシュの使用量も軽減できる。多くの場合、処理するCPUサイクルも少なくてすむ。
・シンプルなデータ型を使用する。
数値は文字セット（utf8など）やcollation（照合順序）を取り扱う必要がある文字と比べてシンプルで負荷が少ない。
同様に日時は文字列ではなく、MySQLビルトインのデータ型を使用する。
・必要が無ければNull可としない。
Null可のカラムがあると、インデックスの作成、インデックス統計、変数の比較などの処理が複雑になり、クエリーの最適化が難しくなる。
そしてNull可にすると余分な保存スペースも必要になる。
・互換性のためINTEGER、BOOL、NUMERICなど多くのエイリアスの型をサポートしているので注意。エイリアスの型でテーブルを定義する事ができ、実体の型はSHOW CREATE TABLEで確認する事が出来る。
小さいデータ型の方が速く動作し、ディスク、メモリ、CPUキャッシュの使用量も軽減できる。多くの場合、処理するCPUサイクルも少なくてすむ。
・シンプルなデータ型を使用する。
数値は文字セット（utf8など）やcollation（照合順序）を取り扱う必要がある文字と比べてシンプルで負荷が少ない。
同様に日時は文字列ではなく、MySQLビルトインのデータ型を使用する。
・必要が無ければNull可としない。
Null可のカラムがあると、インデックスの作成、インデックス統計、変数の比較などの処理が複雑になり、クエリーの最適化が難しくなる。
そしてNull可にすると余分な保存スペースも必要になる。
・互換性のためINTEGER、BOOL、NUMERICなど多くのエイリアスの型をサポートしているので注意。エイリアスの型でテーブルを定義する事ができ、実体の型はSHOW CREATE TABLEで確認する事が出来る。
整数の型
〇型の種類と保存スペース
型 保存スペース
TINYINT 8ビット
SMALLINT 16ビット
MEDIUMINT 24ビット
INT 32ビット
BIGINT 64ビット
〇UNSIGNED属性
負の値を使用しない。
〇注意点
・INT(11)のように指定する事も出来るが、一部の例外を除いて意味を持たない。格納や演算において、INT(1)もINT(10)も違いはない。
型 保存スペース
TINYINT 8ビット
SMALLINT 16ビット
MEDIUMINT 24ビット
INT 32ビット
BIGINT 64ビット
〇UNSIGNED属性
負の値を使用しない。
〇注意点
・INT(11)のように指定する事も出来るが、一部の例外を除いて意味を持たない。格納や演算において、INT(1)もINT(10)も違いはない。
実数の型
●DECIMAL
〇構文
DECIMAL(M,D)
・Mは最大桁数(精度)で、Dは小数点以下の桁数。
・Mに対する最大値は65で、DECIMAL値に関する計算が65桁まで正確。
・固定点型で計算は正確なので金銭データ等の正確な精度を必要とするものを格納するために利用。
・10進数の各桁の値をそのままの形で保存。
・4バイトの中に9個の10進数をパックするバイナリフォーマットを使って格納。
例えばDECIMAL(20,6)の場合、整数部分は14桁、小数部分は6桁となり、整数桁では、9桁分の4バイトと残り5桁分の3バイトが必要になり、小数6桁には3バイトが必要。
●FLOAT、DOUBLE
・FLOATは4バイト、DOUBLEは8バイト。
・浮動小数点型で、計算によって近似値が得られる。近似数値データ値を表すために利用。２進数で表現するので誤差が生じる場合がある。
・同じ範囲の数値を扱う場合でもDECIMALより少ない領域で保存できる。
〇構文
DECIMAL(M,D)
・Mは最大桁数(精度)で、Dは小数点以下の桁数。
・Mに対する最大値は65で、DECIMAL値に関する計算が65桁まで正確。
・固定点型で計算は正確なので金銭データ等の正確な精度を必要とするものを格納するために利用。
・10進数の各桁の値をそのままの形で保存。
・4バイトの中に9個の10進数をパックするバイナリフォーマットを使って格納。
例えばDECIMAL(20,6)の場合、整数部分は14桁、小数部分は6桁となり、整数桁では、9桁分の4バイトと残り5桁分の3バイトが必要になり、小数6桁には3バイトが必要。
●FLOAT、DOUBLE
・FLOATは4バイト、DOUBLEは8バイト。
・浮動小数点型で、計算によって近似値が得られる。近似数値データ値を表すために利用。２進数で表現するので誤差が生じる場合がある。
・同じ範囲の数値を扱う場合でもDECIMALより少ない領域で保存できる。
文字列の型
●VARCHAR
・可変長の文字列を保存。
・必要な分の領域で済むので固定長の場合より少なくて済む。（MyISAMでROW_FORMAT=FIXEDを指定してテーブル定義した場合は例外）
・1バイトまたは2バイトの長さ接頭辞が付いたデータとして格納される。
長さ接頭辞は、値に含まれるバイト数を示す。
255バイト以下の値を格納するカラムでは1バイト長の接頭辞を使用し、255バイトよりも大きい値を格納するカラムでは2バイト長の接頭辞を使用する。
・rowは可変長である事に注意。
rowのサイズが増大し、元のスペースにおさまらなくなった場合、MyISAMの場合はrowのフラグメントが起こり、InnoDBではrowがおさまるようにページ分割が発生する。
・カラム長の最大値が平均より著しく大きく、更新の頻度が少ない場合はVARCHARの使用が有用。
●CHAR
・固定長。格納時に、指定された長さになるよう右側にスペースが埋め込まれる。
取り出し時には、PAD_CHAR_TO_FULL_LENGTH SQLモードが有効になっていないかぎり、末尾のスペースが削除される。
・非常に短い文字列、またはすべての文字列の長さが同程度の場合に有用（例、パスワードのMD5の値）。
非常に短い文字列の場合は、VARCHARは長さ接頭辞の分だけ無駄が大きくなる。
・データが頻繁に更新される場合はVARCHARよりCHARが望ましい。CHARの場合は固定長なのでフラグメントが起こらない。
●BINARYとVARBINARY型
・BINARYおよびVARBINARY型は、CHARおよびVARCHAR型に似ているが、非バイナリ列ではなく、バイナリ列を格納。
キャラクタセットを持たず、ソートと比較は値の中の数値バイトに基づく。
・BINARY値が格納されるとき、特定の長さまでパッド値で右側が詰められる。パッド値は 0x00。
ゼロバイト値は挿入時には右側が0x00で詰められ、選択時に後続バイトは削除されない。
すべてのバイトは、ORDER BYとDISTINCT操作を含め、比較において重要。
0x00バイトとスペースは比較において異なり、0x00 ●BLOBとTEXT型
・BLOBとTEXTは様々な大きさのデータを保持することができる。BLOBはバイナリ、TEXTは非バイナリ。
・4つのBLOB型は、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。
これらは、保持することができる最大長さだけが異なっている。
・4つのTEXT型は、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。
・他のデータ型と異なりオブジェクトとして扱い、保存の仕方も異なっている。
・カラムのmax_sort_lengthバイトだけがソートに利用される。
・すべてのデータをインデックスする事が出来ず、インデックス接頭辞長を指定しなければいけない。
●ENUM型
・ENUMはテーブルを作成する際、カラム仕様の中で明確に列挙された許容値リストから選択された値を持つ文字列オブジェクト。
列挙値は引用された文字列直定数である必要がある。
・リスト内の値の数によるが１～２バイトの非常にコンパクトなサイズで保存できる。
フィールド定義リスト内の各項目の位置に対応する数値として内部では保存していて、この数値と列挙値をマッピングするルックアップテーブルを.frmフィル内に用意している。
このルックアップテールにアクセスする分のオーバーヘッドがある。
・ENUM値は、内部では定義した順番の番号で保存されていて、ソートはこの番号の値によって行われる。
たとえば、ENUM(‘a’, ‘b’) では ‘a’ が ‘b’ の前にソートされるが、ENUM(‘b’, ‘a’) では ‘b’ が ‘a’ の前にソートされる
・列挙値リストは固定されているので、リストに追加する場合はALTER TABLEでテーブル定義を変更する必要がある。
・可変長の文字列を保存。
・必要な分の領域で済むので固定長の場合より少なくて済む。（MyISAMでROW_FORMAT=FIXEDを指定してテーブル定義した場合は例外）
・1バイトまたは2バイトの長さ接頭辞が付いたデータとして格納される。
長さ接頭辞は、値に含まれるバイト数を示す。
255バイト以下の値を格納するカラムでは1バイト長の接頭辞を使用し、255バイトよりも大きい値を格納するカラムでは2バイト長の接頭辞を使用する。
・rowは可変長である事に注意。
rowのサイズが増大し、元のスペースにおさまらなくなった場合、MyISAMの場合はrowのフラグメントが起こり、InnoDBではrowがおさまるようにページ分割が発生する。
・カラム長の最大値が平均より著しく大きく、更新の頻度が少ない場合はVARCHARの使用が有用。
●CHAR
・固定長。格納時に、指定された長さになるよう右側にスペースが埋め込まれる。
取り出し時には、PAD_CHAR_TO_FULL_LENGTH SQLモードが有効になっていないかぎり、末尾のスペースが削除される。
・非常に短い文字列、またはすべての文字列の長さが同程度の場合に有用（例、パスワードのMD5の値）。
非常に短い文字列の場合は、VARCHARは長さ接頭辞の分だけ無駄が大きくなる。
・データが頻繁に更新される場合はVARCHARよりCHARが望ましい。CHARの場合は固定長なのでフラグメントが起こらない。
●BINARYとVARBINARY型
・BINARYおよびVARBINARY型は、CHARおよびVARCHAR型に似ているが、非バイナリ列ではなく、バイナリ列を格納。
キャラクタセットを持たず、ソートと比較は値の中の数値バイトに基づく。
・BINARY値が格納されるとき、特定の長さまでパッド値で右側が詰められる。パッド値は 0x00。
ゼロバイト値は挿入時には右側が0x00で詰められ、選択時に後続バイトは削除されない。
すべてのバイトは、ORDER BYとDISTINCT操作を含め、比較において重要。
0x00バイトとスペースは比較において異なり、0x00 ●BLOBとTEXT型
・BLOBとTEXTは様々な大きさのデータを保持することができる。BLOBはバイナリ、TEXTは非バイナリ。
・4つのBLOB型は、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。
これらは、保持することができる最大長さだけが異なっている。
・4つのTEXT型は、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。
・他のデータ型と異なりオブジェクトとして扱い、保存の仕方も異なっている。
・カラムのmax_sort_lengthバイトだけがソートに利用される。
・すべてのデータをインデックスする事が出来ず、インデックス接頭辞長を指定しなければいけない。
●ENUM型
・ENUMはテーブルを作成する際、カラム仕様の中で明確に列挙された許容値リストから選択された値を持つ文字列オブジェクト。
列挙値は引用された文字列直定数である必要がある。
・リスト内の値の数によるが１～２バイトの非常にコンパクトなサイズで保存できる。
フィールド定義リスト内の各項目の位置に対応する数値として内部では保存していて、この数値と列挙値をマッピングするルックアップテーブルを.frmフィル内に用意している。
このルックアップテールにアクセスする分のオーバーヘッドがある。
・ENUM値は、内部では定義した順番の番号で保存されていて、ソートはこの番号の値によって行われる。
たとえば、ENUM(‘a’, ‘b’) では ‘a’ が ‘b’ の前にソートされるが、ENUM(‘b’, ‘a’) では ‘b’ が ‘a’ の前にソートされる
・列挙値リストは固定されているので、リストに追加する場合はALTER TABLEでテーブル定義を変更する必要がある。
日付と時刻型
●DATETIME
・日付と時刻をYYYYMMDDHHMMSSフォーマットの整数でタイムゾーンに依存せずに保存。
・1001～9999年、秒単位。
・８バイトのスペースで保存。
・’YYYY-MM-DD HH:MM:SS’のフォーマットで表示。
●TIMESTAMP
・1970年1月1日0時（GMT）からの積算秒数。（UNIXのタイムスタンプと同じ。）
・保存スペースが４バイトと少なくて済む。
・’1970-01-01 00:00:01’～’2038-01-19 03:14:07’の範囲。
・表示はタイムゾーンの設定に依存。
・明示的に値を指定せずに登録、更新した場合は、NULLではなく現在時刻が自動で設定される。
・日付と時刻をYYYYMMDDHHMMSSフォーマットの整数でタイムゾーンに依存せずに保存。
・1001～9999年、秒単位。
・８バイトのスペースで保存。
・’YYYY-MM-DD HH:MM:SS’のフォーマットで表示。
●TIMESTAMP
・1970年1月1日0時（GMT）からの積算秒数。（UNIXのタイムスタンプと同じ。）
・保存スペースが４バイトと少なくて済む。
・’1970-01-01 00:00:01’～’2038-01-19 03:14:07’の範囲。
・表示はタイムゾーンの設定に依存。
・明示的に値を指定せずに登録、更新した場合は、NULLではなく現在時刻が自動で設定される。
BIT型
・一つ又は複数のtrue/falseの値を一つのカラムに保持。ビットフィールド値を格納。
・BIT(M)の型は、M ビット値の格納ができる。Mの範囲は1から64まで。
・InnoDBなどはこのビット数が収まる最小の数値型として保存する。
・ビット値を指定するには、b’value’ 表記を使用することができる。
value は、0と1で書かれた2進値。たとえば、b’111′ と b’10000000′ はそれぞれ7と128を表す。
・BIT型は数値型ではなく文字列型として扱われるので注意する。例えば、b’00111001’（57）をSELECT文で取得して表示するとアスキーコードの57として解釈され、”9″が表示されるが、数値コンテキストとして扱うと数値の57が表示される。
・BIT(M)の型は、M ビット値の格納ができる。Mの範囲は1から64まで。
・InnoDBなどはこのビット数が収まる最小の数値型として保存する。
・ビット値を指定するには、b’value’ 表記を使用することができる。
value は、0と1で書かれた2進値。たとえば、b’111′ と b’10000000′ はそれぞれ7と128を表す。
・BIT型は数値型ではなく文字列型として扱われるので注意する。例えば、b’00111001’（57）をSELECT文で取得して表示するとアスキーコードの57として解釈され、”9″が表示されるが、数値コンテキストとして扱うと数値の57が表示される。
SET型
●概要
・SETはゼロ、またはそれ以上の値を持つことができる文字列オブジェクトであり、それらはそれぞれテーブルが作成されたときに指定された許容値リストから選択する
・SET型のカラム値が複数のメンバーで構成される場合は、各メンバーをコンマで区切って指定。
例）mysetテーブルの”col”カラムをSET型で、メンバーを’a’, ‘b’, ‘c’, ‘d’にする
CREATE TABLE myset (col SET(‘a’, ‘b’, ‘c’, ‘d’));
・定義したセットメンバーに対応する格納値の低位ビットを利用してSET値を数値で格納する。
例）
・SETはゼロ、またはそれ以上の値を持つことができる文字列オブジェクトであり、それらはそれぞれテーブルが作成されたときに指定された許容値リストから選択する
・SET型のカラム値が複数のメンバーで構成される場合は、各メンバーをコンマで区切って指定。
例）mysetテーブルの”col”カラムをSET型で、メンバーを’a’, ‘b’, ‘c’, ‘d’にする
CREATE TABLE myset (col SET(‘a’, ‘b’, ‘c’, ‘d’));
・定義したセットメンバーに対応する格納値の低位ビットを利用してSET値を数値で格納する。
例）
SETメンバー 10 進値 バイナリ値 a 1 0001 b 2 0010 c 4 0100 d 8 1000
このカラムに値9を割り当てた場合、2進数では1001となるため、SET値の最初と4番目のメンバーである ‘a’ と ‘d’ が選択され、結果として得られる値は ‘a,d’ になる。
●用途、メリットデメリット
・たくさんのtrue/falseの値を持つ項目を一つのカラムにまとめて保存する事が出来る。
・ストレージスペースを効率的に使用でき、FIND_IN_SET()やFIELD()関数で簡単に使用できる。
・カラムの定義を変更する際にはALTER TABLEが必要で、大きなテーブルだと負荷が大きい。
・SET型カラム上の検索ではインデックスを使用できない。
BOOLEAN型
MySQLのオンラインマニュアルを見ると、BOOL、BOOLEAN型はTINYINT(1) と同義とありました。
ゼロの値は偽、ゼロ以外の値は真とみなすと記載されています。
そして、TRUE、FALSEを指定して登録する事もできるようですが、TRUEとFALSEは、1と0の別名として定義されているようです。
ゼロの値は偽、ゼロ以外の値は真とみなすと記載されています。
そして、TRUE、FALSEを指定して登録する事もできるようですが、TRUEとFALSEは、1と0の別名として定義されているようです。