MySQLのカラム型

文字列型

MySQLのカラム型には文字列型、数値型、日付と時刻型の３つのカテゴリに分類することができる。
文字列型について一覧を以下に示す。Mは最大表示サイズを表す。[]はオプション。

種別	型	データ格納範囲	説明
固定長	CHAR(M) [BINARY]	0～255文字	値は指定された長さになるように右側にスペースが追加される値の取り出し時には、右側のスペースが取り除かれる
固定長	CHAR	CHAR(1)のシノニム
可変長	VARCHAR(M) [BINARY]	0～65532バイト	最大長までの右側のスペースは、値の格納時に取り除かれる 1テーブルのデータ型合計サイズの上限が64KBという制限がある為実際はもっと少ない
	TINYBLOB/TINYTEXT	255バイト	バイナリオブジェクトを格納するカラム。
	BLOB/TEXT	64KB	TINYBLOB/TINYTEXYよりも格納できるサイズが大きい。
	MEDIUMBLOB/MEDIUMTEXT	16MB	BLOB/TEXTよりも格納できるサイズが大きい。
	LONGBLOB/LONGTEXT	4GB	MEDIUMBLOB/MIDIUMTEXTよりも格納できるサイズが大きい。

MySQL文字列型の特徴

最大長を超える値を割り当てると、カラムのサイズに合わせて値が切り捨てられる。
但し、MySQL5.0以降は初期化パラメータsql_modeを設定することでエラー扱いにできる。
長さ0の文字列はNULLとは見なされずに、0バイトの文字として扱われる。

CHAR型とVARCHAR型

BINARY属性が指定されている場合、大文字・小文字を区別してソート・比較される。(詳細は後述)

BLOB型とTEXT型

TEXT型は大文字・小文字を区別しない。つまり、TEXT型はVARCHAR型・BLOB型はVARCHAR BINARY型とみなすことができる。
VARCHAR 型のカラムでは値の格納時に後続のスペースが削除されるが、 BLOB 型と TEXT 型のカラムではこの削除は行われない。
BLOB 型と TEXT 型のカラムには、DEFAULT 値は設定できない。

GROUP BYまたは、ORDER BYを使用する場合、以下のようにカラムの値を固定長のオブジェクトに変換しなければならない。

SELECT comment FROM tbl_name,SUBSTRING(comment,20) AS substr ORDER BY substr;

また、フィールドの位置を指定、もしくは別名を使用することでグループ化操作ができる。

SELECT id,SUBSTRING(blob_col,1,100) FROM tbl_name GROUP BY 2;
SELECT id,SUBSTRING(blob_col,1,100) AS b FROM tbl_name GROUP BY b;

列値の大文字・小文字の区別について

ソートや文字列比較の際、デフォルトでは半角英字の大文字・小文字を区別しない。
区別したい場合は、BINARY属性やBLOB系のカラムを利用するか、以下のようにSQL文で「BINARY」を指定する。

SELECT col1 FROM tbl_name WHERE BINARY col1='value';

数値型

数値型について一覧を以下に示す。Mは最大表示サイズを表す。Dは小数点以下の桁数。[]はオプション。

型	データ格納範囲	使用バイト数	説明
BIT[(M)]	1～64ビット		Mにはビット数を指定する。
TINYINT[(M)] [UNSIGNED] [ZEROFILL]	-128～127 もしくは 0～255	1バイト	非常に小さな整数。
BOOL, BOOLEAN			TINYINT(1)のシノニム。 0ならfalse、0以外ならtrue。
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]	-32768～32767 もしくは 0～65535	2バイト	小さな整数。
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]	-8388608～838607 もしくは 0～16777215	3バイト	中間サイズの整数。
INT[(M)] [UNSIGNED] [ZEROFILL]	-2147483648～2147483647 もしくは 0～4294967295	4バイト	通常サイズの整数。
INTEGER[(M)] [UNSIGNED] [ZEROFILL]			INT のシノニム。
BIGINT[(M)] [UNSIGNED] [ZEROFILL]	-9223372036854775808 ～9223372036854775807 もしくは 0～18446744073709551615	8バイト	大きい整数。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]	-3.402823466E+38 ～-1.175494351E-38、0、及び1.175494351E-38～3.402823466E+38		単精度浮動小数点数。
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]	-1.7976931348623157E+308 ～-2.2250738585072014E-308、0、及び2.2250738585072014E-308～1.7976931348623157E+308		倍精度浮動小数点数。
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]	DOUBLEと同じ	4.0/4.1:M+2バイト (D=0の場合、M+1バイト) 5.0:4.0/4.1の約半分	アンパック浮動小数点数。
DEC, NUMERIC, FIXED			いずれもDECIMALのシノニム。

MySQL数値型の特徴

最大値を超える(最小値を下回る)値を格納しようとした場合、エラーにならず、その型の最大値(最小値)が格納される。
但し、MySQL5.0以降は初期化パラメータsql_modeを設定することでエラー扱いにできる。
'abc'などの文字列型を格納しようとしてもエラーにならず、0が代わりに格納されてしまう。
'2006-10-31'のように先頭は数字だが途中から数値でない文字が入るような値を格納しようとすると先頭の数値として解釈できる部分のみ(この場合は2006)が格納されてしまう。
DECIMAL型の最大値はMが65、Dが30。指定無しだとDECIMAL(10,0)になる。この場合、BIGINTのデフォルト(19)よりも小さい。整数のみなら使用領域が少ないINT系のほうを選択すればよい。

有効桁数について

負の値はM-D桁まで。正の値については負の符号１バイト分も数値領域として割り当てることができる。(M-D+1)
例えば、DECIMAL(3,1)では、負の整数部分3-1=2桁、正の整数部分3-1+1=3桁、少数1桁になるので、-99.9～999.9までとなる。

オプションについて

UNSIGNEDを指定すると、符号なしの整数だけを扱うようになる。(扱える数値範囲が大きくなる)
ZEROFILLを指定すると、表示される際に左からゼロを最大長になるまでつけられる。
カラムに対して ZEROFILL を指定すると、そのカラムに UNSIGNED 属性が自動で追加される。
整数値の減算で、どちらか一方の整数値が UNSIGNED 型の場合、キャストが行われるので結果の値は符号なしになる。

日付／時刻型

日付／時刻型について一覧を以下に示す。

型	有効範囲	表示フォーマット	使用バイト
YEAR[(2\|4)]	1901～2155	YYYY	1バイト
DATE	1000-01-01～9999-12-31	YYYY-MM-DD	3バイト
DATETIME	1000-01-01 00:00:00～9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	8バイト
TIME	-838:59:59～838:59:59	HH:MM:SSまたは、HHH:MM:SS	3バイト
TIMESTAMP	1970-01-01 00:00:00～2037年の一定の時点	YYYY-MM-DD HH:MM:SS	4バイト

MySQL日付／時刻型の特徴

OracleのTIMESTAMP型はミリ秒まで扱えるが、MySQLは秒単位までしか扱えない。
MySQLのTIMESTAMP型は、NULL値を挿入したりほかの列の値が更新されたりすると、自動的に現在時刻に更新される。
TIME型は24時間を超える値を格納できる為、経過時間や２つのイベント間の間隔を表現するために使用される。

値の格納方法

SQL関数STR_TO_DATEを使用しない限り、「YYYY-MM-DD 24HH:MI:SS」または「YYYYMMDD24HHMISS」というフォーマットでなければならない。
(YYYY:年４桁、MM:月、DD:日、24HH:24時間制の時、MI:分、SS:秒)
違反した場合は、「0000-00-00 00:00:00」のようにゼロが格納される。

mysql> CREATE TABLE tbl1(col1 DATETIME);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tbl1 VALUES ('2006-11-03 18:36:43');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tbl1 VALUES ('20061103183643');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tbl1 VALUES ('11/03/2006 18:36:43');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO tbl1 VALUES ('2006/11/03 18:36:43');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT col1 FROM tbl1;
+---------------------+
| col1                |
+---------------------+
| 2006-11-03 18:36:43 |
| 2006-11-03 18:36:43 |
| 0000-00-00 00:00:00 |
| 2006-11-03 18:36:43 |
+---------------------+
4 rows in set (0.00 sec)

不正値をエラーにする

MySQL5.0から初期化パラメータsql_modeを指定することで以下のように不正値をエラーにすることができるようになった。

mysql> INSERT INTO tbl1 VALUES ('11/03/2006 18:36:43');
ERROR 1292 (22007): Incorrect datetime value: '11/03/2006 18:36:43' for column 'col1' at row 1

初期化パラメータファイル(my.cnf)に以下を追加すればよい。詳細はMySQLマニュアルを参照。

[mysqld]
sql_mode="STRICT_TRANS_TABLES"

セッション中でも以下のように無効や設定が可能

mysql> set sql_mode=""
mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

mysql> set sql_mode="STRICT_TRANS_TABLES";
mysql> select @@session.sql_mode;
+---------------------+
| @@session.sql_mode  |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

インデックスへ戻る