【MySQL】基本的な列のデータ型と制約

データベースへ作成するテーブルのカラム（列）に指定するデータ型と、制約についてまとめます。

データ型の名前や制約の記述はデータベースの製品ごとに異なる部分がありますが、
今回はMySQLでのルールを基本に概ねよく使うものに絞ってメモします。

最後にそれらを含んだ、テーブル定義のSQLを記載します。

環境 :
mysql  Ver 8.0.16 for osx10.13 on x86_64 (Homebrew)
Mac OS X 10.13.2

データ型の種類について

テーブルのカラムは保存したい値に適したデータ型をあらかじめ必ず指定しておく必要があります。
データ型は一度決めたら変更することは出来ず、一つのカラムに複数のデータ型を指定することも出来ません。

以下、保存する値の種類、目的別に合わせてデータ型をまとめます。

文字列を扱うデータ型

名前や文章など文字列データを格納したい場合に以下のデータ型を使用します。

char型

（例）char(30)

（）カッコの中の数字は格納出来る桁数（文字数）を指定します。
指定した数字以上の桁数のデータは格納出来ません。
格納するデータが指定した桁数に満たなかった場合、その分だけ空白が埋められます。（固定長文字列）

varchar型

（例）varchar(30)

char型と同様に（）カッコの中の数字は格納出来る桁数（文字数）を指定します。
こちらも指定した数字以上の桁数のデータは格納出来ません。

格納するデータが指定した桁数に満たない場合データの大きさはchar型とは異なり、
空白を保存せずそのデータの大きさによって可変となります。（可変長文字列）

※（例）varchar(30)の指定に10文字のデータを格納した場合、サイズは10文字分となる。

text型

文章など、長文のテキストデータを格納する際にはこちらのデータ型を利用します。

但し、文字数は無制限というわけではなくMySQLの場合、最大65,535文字で
日本語など全角文字（マルチバイト文字）を格納する場合はこれより更に少なくなります。（公式サイトより）

数値を扱うデータ型

数値を格納する場合、整数か少数かによって以下に紹介するデータ型を使い分ける必要があります。
これらの型を指定した列には文字列を格納することが出来ません。

また、「値」としての数しか保存することが出来ませんので
「090」など0から始まる数字を保存する場合、先頭の0が省略されてしまいます。
0から始まる数字を保存する場合は文字列のデータ型を使用します。

数値を扱うデータ型は正の数だけなく、負の数を扱うこともできます。

int型 （整数）

整数の数値データを格納する場合はint型を指定します。

格納できる数値は最大で※2147483647（21億…）で、
より大きい値を利用する場合はbigint型を使用します。

※符号あり、なしによって変動（後述）

float型 double型 （浮動少数型）

1.4など、少数点を含む数値はfloat型、もしくはdouble型を使用する。
但し、これらは浮動小数点型とよばれ、概数値（おおよその数）を扱うものとなっているので、
金額などより正確な値を扱う必要がある場合は後述する「固定少数型」を採用する必要があります。

（浮動少数型の中ではfloatより、doubleの方が精度が高い）

decimal型　（固定少数型）

小数点の中でも金額データなどより正確な数値が求められる場合はdecimal型を採用します。

（例）decimal(5,2)

例のように型を指定した場合、少数部分が2桁、合計で5桁の値を格納することが出来ます。
-999.99から999.99 の範囲を格納可能となります。（公式より）

（符号なし）UNSIGNEDについて

（例）int unsigned

例のようにunsigned（符号なし）キーワードをデータの型に指定すると
0と正の値しか扱えなくなり、通常（符号あり）よりも扱える値の範囲を大きくする事が出来ます。

intの正の値
符号あり/符号なし
2147483647 / 4294967295

時間、日付を扱うデータ型

日にちや時間などを保存する際に扱うデータ型。

date型（日付）

時間以外の年月日の値を‘YYYY-MM-DD’ 形式で扱う。

time型（時間）

時間の値を‘HH:MM:SS’ 形式で扱う

datetime型、timestamp型（日時）

日時（日付と時刻）を記録する場合、datetime型もしくは、timestamp型を使用します。

インサート時の日時を自動的に記録する

datetime型もしくは、timestamp型を指定したカラムは、データ登録時に値を指定しなくてもデフォルト値（※後述）を「CURRENT_TIMESTAMP」とすることで自動的に現在日時を記録する事が可能となります。

CREATE TABLE times (
  tscolumn TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dtcolumn DATETIME DEFAULT CURRENT_TIMESTAMP
);

DEFAULT（デフォルト値）について

データ追加の際に値に何も指定しなかった場合に自動的に入る値をデフォルト値（初期値）といい、
テーブル作成時カラム定義の際にそれを決める事ができます。
（明示しない場合はNULLがデフォルト値となる）

データ型の後にDEFAULTキーワードと一緒にデフォルト値を記述する。

（例） status VARCHAR(7) DEFAULT ‘public’

上記の例ではstatusカラムに何も指定しない場合、publicという文字列の値が保存される。

列の制約について

データ型を決める以外にもカラムに制約の仕組みを設けることで
NULL（ヌル）の値を許容しない、値の重複を許さないなど無効な値を保存出来なくすることができます。

制約はデータ型と同様に、テーブル作成時にそれぞれのカラムへ定義する事が出来ます。

以下、代表的な制約を順番に解説します。

NOT NULL 制約

（例） title VARCHAR(40) NOT NULL
（基本的に制約の種類はデータ型の後ろに記述します）

NOT NULL 制約を指定する事で、カラムにNULL（ヌル）の値が入る事を禁止します。

NOT NULL制約をつけたカラムは必須項目となり、必ず値を保存しなければなりません。
（お問い合わせフォームの必須項目など）

基本的にカラムへはNOT NUL制約を付けておくことがデータベース設計において定石のようです。

キー(key) 制約 （PK、FK、UK）

リレーショナルデータベースにおいて、欲しいデータを引き出すためレコードを特定するキー（鍵）が必要となり
制約によってそれを作成する事ができます。

PK（PRIMARY KEY）制約

（例）id int PRIMARY KEY

PRIMARY KEYは主キーと呼ばれ、idカラムなどレコードの先頭の列に設けて
レコードをテーブル内で一意に識別、特定する事ができる。

主キーとした列はNOT NULLでかつUNIQUE（後述）となる。

データベース設計においてテーブル内で主キーは必ず一つ存在させることが原則で、
それによってレコードの重複をさせないことが可能となる。

主キーはテーブル内で必ず一つ存在させてかつ、一つだけしか作成できない

UK（UNIQUE KEY）制約

（例）label varchar (30) UNIQUE NOT NULL

（カラムをUNIQUEとする）

UNIQUE KEYはPRIMARY KEYと同様にカラムの値を一意（ユニークな値）にする制約ですが、
PRIMARY KEYがテーブル内で一つしか作成出来ないのに対してUNIQUE KEYはいくつでも作成することが出来ます。

またPRIMARY KEYと異なる点ではUNIQUE KEYを作成しただけではNULL値の禁止は出来ません。
NULL値を許容しない場合は例のようにUNIQUE KEYとNOT NULL制約を同時に作成する必要があります。

FK（FOREIGN KEY）制約

FOREIGN KEYは外部キーと呼ばれ、この制約を作成したカラムは他のテーブルのカラムを参照し
参照しているカラムに存在する値以外は保存することが出来なくなります。
（但し、NULL値は許容されています）
この制約は参照整合性制約（参照制約）、外部キー制約と呼ばれます。

（例）名簿に所属グループ名を保存する場合、参照先のグループ一覧に存在しないグループ名は登録出来ない。

参照先のテーブル（親テーブルという）

参照先に値が存在しないエラー。

mysql> INSERT INTO members(name,`group`) VALUES('山田二郎','バスケット');
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails

テーブル作成時の外部キーの作成方法

MySQLで外部キーを作成する場合、テーブル作成時にカラムでなく
テーブルに対して制約を付ける必要があるようです。（表制約という）

CREATE TABLE members (
	id int PRIMARY KEY AUTO_INCREMENT,
	name varchar(255) NOT NULL,
	`group` varchar(255),
	FOREIGN KEY (`group`) REFERENCES group_master(`group`)
)

FOREIGN KEYでキーにしたい自分のカラムを指定し、REFERENCESキーワードで参照するテーブルと参照カラムを指定する。

※参照先の親テーブルを、参照する側（子テーブル）より先に作成しておく必要があります。

また、MySQL公式サイトではキー作成時にINDEX（インデックス）も同時に作成していましたが
明示しなくても外部キーを作成することが出来ました。

CHECK 制約

チェック（CHECK）制約は指定した条件を満たす値しかカラムへ保存出来なくする制約です。
MySQLはバージョン8からの採用で旧バージョンにはこの制約の機能が備わっていないようです。

（例）price int CHECK(price > 0)
priceカラムへは0より大きい値しか保存出来ない。

（エラー）

mysql> INSERT INTO books(name, author, price)VALUES('PHP入門', '鈴木一郎', 0);
ERROR 3819 (HY000): Check constraint 'books_chk_1' is violated.

（例）テーブル作成 SQL

最後にMySQLでのテーブル作成のCREATE文（SQL）の例を
これまでまとめてきたカラムのデータ型、制約の記述を組み込んでメモします。

部署テーブル

社員テーブル
（社員は部署に必ず所属する）

CREATE文

部署テーブル （group_master）

CREATE TABLE group_master (
 group_id int PRIMARY KEY AUTO_INCREMENT,
 group_name varchar(5) UNIQUE NOT NULL
);

社員テーブル（employee）

CREATE TABLE employee (
 id int PRIMARY KEY AUTO_INCREMENT,
 group_name varchar(5) NOT NULL,
 name varchar(255) NOT NULL,
 tel_number varchar(13) UNIQUE NOT NULL,
 hire_date date NOT NULL,
 created_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (group_name) REFERENCES group_master(group_name)
);

参考にさせていただいたサイト

MySQL公式ドキュメント
バージョン 5.6
https://dev.mysql.com/doc/refman/5.6/ja/
バージョン 8
https://dev.mysql.com/doc/refman/8.0/en/

文字列データ型について
https://qiita.com/marcodi/items/fe35e066d270306e0256
https://wa3.i-3-i.info/word1809.html

制約について
http://web.sevendays-study.com/mysql/ex-day5.html

Follow me!