[SQLearning][MySQL]データ型・制約の指定

SQL ゼロからはじめるデータベース操作を教材にしてMySQLを学ぶ
SQL学習連載「SQLearning」の第3回です。
前回: [SQLearning][MySQL]テーブルの作成・確認・削除
第3回は「データ型・制約の指定」の指定についてです。

データ型の指定

以下の CREATE 文を例にデータ型と制約の指定について解説します。

CREATE TABLE products (
    id          int(11)      NOT NULL AUTO_INCREMENT COMMENT '商品ID',
    name        varchar(255) NOT NULL UNIQUE         COMMENT '商品名',
    description text                                 COMMENT '商品の説明',
    price       int(11)      NOT NULL                COMMENT '商品の値段',
    tax_free    tinyint(1)   NOT NULL DEFAULT 0      COMMENT '非課税フラグ',
    reg_date    datetime     NOT NULL                COMMENT '登録日',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT '商品テーブル';

int(11)

数値型です。カッコは表示幅です。値の範囲に影響を与えるものではありません。
ZEROFILL を続いて指定することによってスペースで調整される表示幅を 0 で埋めることができます。
値の範囲は -2147483648 ~ 2147483647 ですが、UNSIGNED をつければ 0 ~ 4294967295 になります。
MySQL :: MySQL 5.1 リファレンスマニュアル :: 10.2 数値タイプ

varchar

可変長の文字列型です。カッコは文字数です。(※4.1以前まではバイト数でした)

MySQL :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 10.4.1 The CHAR and VARCHAR Types
(Before MySQL 4.1, the length is interpreted as number of bytes.)

MySQL 5.0.3以前は varchar は 255 バイトまででした。それ以降のバージョンでは 65535 バイト、
メタデータを除き 65532 バイトまで入れることができます。

MySQL :: MySQL 5.0 Reference Manual :: 11.1.6.1 The CHAR and VARCHAR Types
The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

文字コードが utf8 の場合は1文字3バイトなのでカッコに指定できるのは(65532/3=)21844くらいまでです。(*1)
それ以上の数を指定すると MySQLが自動的に mediumtext などに変換します。
(*1)メタデータの領域があるのか僕の環境では varchar(21835) までしか varchar として作成できませんでした。

text

可変長の文字列型です。varchar とは異なりデフォルト値を設定できません。
用途としては varchar とほとんど同じですが varchar がテーブルにインラインに格納されているのに対して
text は文字列を別ファイルとして持ち、カラムはファイルへのポインタを持っているようです。
この件については複雑な問題ですが以下の投稿によくまとまっています。
Takahiko HORIUCHI - Google+ - RDBMS に MySQL を採用したシステムで、VARCHAR の代わりに TEXT…
varchar(10000)とかで指定するくらいなら text にした方がパフォーマンス的に良いようです。

tinyint(1)

MySQL によって BOOL / BOOLEAN が内部変換された数値型です。
主にフラグ系の値を格納するのに使います。値の範囲は -127 ~ 128 です。

datetime

日時を格納する日付型です。'YYYY-MM-DD HH:MM:SS'形式で日時を保存します。

データ型は詳しく書くとリファレンスマニュアル並みの分量になるのでココでは簡単に紹介するだけに留めます。
詳しく知りたい方はMySQL :: MySQL 5.1 リファレンスマニュアル :: 10 データタイプを読むといいでしょう。

カラムの制約の指定

NULL と NOT NULL

description は NULL を許容するので NULL を指定していますが、
他のカラムは NULL を入れたくないので NOT NULL を指定します。

DEFAULT

デフォルト値を指定したい場合は DEFAULT [指定したい値] と記述します。
discount カラムはフラグを 0, 1 で管理したいのでデフォルト false で 0 を指定する、などのように使います。

AUTO_INCREMENT

id などのように自動で連番を振りたい場合は AUTO_INCREMENT を指定します。

UNIQUE

すべての値が一意でなければならない制限を指定します。
INSERT または UPDATE 時に既存の値と同じ値になる場合はエラーが発生します。
NULL を許容する場合、すべての NULL値は異なる値として扱われます。

COMMENT

カラムに対するコメントを記述することができます。
SQL 文のコメントとは異なり、SHOW CREATE TABLE 文で CREATE TABLE 文を表示した時にも表示されます。
phpMyAdmin などを使用する場合はコメントが表示されるのでフラグ系の各値の意味を記述しておくと便利です。

テーブルの制約の指定

PRIMARY KEY ( [カラム名] )

このテーブルの主キーを設定することができます。

テーブルの設定

ENGINE

データベースのストレージエンジンを指定することができます。
InnoDB と MyISAM が有名ですが 5.5 では InnoDB がデフォルトなようです。
以下のクエリでストレージエンジンの種類を確認すすることができます。

mysql> SHOW ENGINES;

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.4.13 SHOW ENGINES 構文
また、以下のクエリで各テーブルが使用しているストレージエンジンを確認することができます。

mysql> SHOW TABLE STATUS;

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.4.27 SHOW TABLE STATUS 構文

DEFAULT CHARSET

使用する文字セットを指定することができます。日本語を扱うなら utf8(utf8_general_ci)が一般的です。
以下のクエリでサポートしている文字セットを確認することができます。

mysql> SHOW CHARACTER SET;

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.4.2 SHOW CHARACTER SET 構文
実行結果
MySQL :: MySQL 5.1 リファレンスマニュアル :: 9.10 MySQL でサポートされるキャラクタセットと照合順序
MySQL :: MySQL 5.5 Reference Manual :: 10.1.14 Character Sets and Collations That MySQL Supports

COLLATE

照合順序を設定することができます。文字列を比較する際に順位を決める設定です。
DEFAULT CHARSET が utf8 の場合、省略すると utf8_general_ci が設定されます。
ちなみに utf8_general_ci の ci は case-insensitive の略で、大文字小文字を区別しないという意味なので
大文字小文字を区別したい場合は utf8_bin などを指定します。
以下のようなクエリで照合順序の一覧を表示します。