MySQLテーブル設計のための、よく使うデータ型まとめ

１．１　テーブル（表）とは？

MySQLはリレーショナルデータベース（関係データベース）であり、複数のテーブル（表）とそのリレーション（関係）の集まりでデータベースが構成されています。

テーブルは縦の行（レコード）と横の列（カラム）からなる二次元の「表」であり、固定された列に対して、任意の数の行が追加される構造になっています。

１．２　テーブル定義の設計

テーブルの各列に格納できるデータの型やサイズなどの決まりごとを、テーブル定義と呼びます。リレーショナルデータベースを作る際には、このテーブル定義をまず決定する必要があります。このテーブル定義を記載した設計書のことを、「テーブル定義書」と呼びます。

またリレーショナルデータベースには、重複する行（レコード）を複数登録することはできません。そのためにデータを特定するための「キー」を設定します。

１．３　データ型の決定

データ項目の属性には、次のようなものを選択することができます。

大は小を兼ねると思いがちですが、必要最小限のデータ型を選定することで、余計な容量を取らず、結果としてパフォーマンスを向上することができます。

それぞれの型の詳細は、次項にてご紹介します。

１．４　データの大きさの決定

データのサイズは主にデータ型によって決まりますが、最大サイズが指定可能な型もあります。サイズ指定が可能な型については、事項にて解説します。

１．５　キーの決定

重複データでないことを判定するための、キーとなる項目を設定します。キーには、例えば社員データであれば社員番号、商品データであれば商品コードなど、常に一意に定まるものを使用します。

なおキーは１つだけでなく、複数の項目の組み合わせで指定することも可能です。（例えば、伝票番号＋枝番で１つとするなど）

２．１　数値型（整数型）

※mは桁数を指定する整数

数値型には大きく分けて、「整数型」と「浮動小数点型」の２種類があります。その名の通り「整数型」には整数しか入力することができず、「浮動小数点型」には小数点以下を持つデータも入力することができます。

■UNSIGNED

デフォルトは正負の符号を持たせることができますが、「UNSIGNED」オプションを付けると符号を禁止することができます。さらに負の数を使用しないことで、正の数は２倍の大きさまで使用することができます。

■ZEROFILL

「ZEROFILL」オプションを付けると、入力した数字に対して最大桁数まで前方を自動的にゼロ埋めして登録されます。例えば「TYNYINT ZEROFILL」を指定した場合、「32」と入力すると「032」と登録されます。ZEROFILLを使用する場合、負の数は使用できない（UNSIGNEDを指定したのと同じ）状態になります。

２．２　数値型（浮動小数点型）

※mは全体の桁数、dは小数部の桁数を指定する整数

■UNSIGNED

整数型同様に負の数の入力を禁止することができますが、範囲は２倍にはなりません。なお桁数を指定した場合は自動的にUNSIGNED同然となり、負の数を入力することができなくなります。

■ZEROFILL

こちらも整数型同様に、ゼロ埋めして入力することができます。

２．３　文字列型（テキスト型）

CHAR型とTEXT型はどちらも文字列を格納する型ですが、しいて言うならCHAR型は値の範囲を指定することができ、TEXT型は固定となっています。長大な文字列を格納する必要がある際に、TEXT型を使用する場面が多いようです。

２．４　バイナリ型

バイナリ型を設定した場合、送られてきたデータはバイナリデータとして登録されます。

２．５　日付型／時刻型

日付型の基本フォーマットは表のように‘YYYY-MM-DD’となっており、西暦年は4桁、各項目は「-（ハイフン）」で区切りを表しますが、次のように省略することもできます。

日付型の基本フォーマットは’HH:MM:SS’であり、区切り文字は「:」です。

２桁年については、年のみの型と同じく1970年～2069年に置き換えて処理が行われます。さらに区切り文字が使われている場合は1桁の月日や時刻は2桁で表すことも可能であるため、最短では次のように入力することもできます。

また、日付の区切り文字には「-」でなく、「/」「.」「@」も使用可能です。

さらに囲み記号の「‘’」については、省略することも可能です。

日付と時刻の型表記は組み合わせて使用できますが、方式は統一する必要があります。（1桁日の0を省略する場合、1桁時刻の0も省略しなければならない）

２．６　その他の型

MySQLのデータ型には、これらのほか列挙型（ENUM、SET）や、長大なバイナリデータを格納できるBLOB型などがあります。

３．１　テーブル化したいデータを整理し、必要な項目を決める

今回は、次のような商品購入データをテーブル化したいと思います。

まず、データを整理していきます。リレーショナルデータベースのテーブルを整理するには、「正規化」という方法があります。このデータに対して第２正規化まで行うと、次のように４つのテーブルに整理できました。

上記正規化の途中経過や変換手順について、詳しくは次の関連記事をご覧下さい。

関連記事：リレーショナルデータベース正規形の種類と正規化手順

３．２　キーを決める

次に、各レコードの重複判定を行うためのキーを設定します。各表で、他のレコードと絶対に重複しないデータが入っている項目をキーに設定します。

今回は下図のように、「注文番号」「ユーザID」「商品番号」をキーに設定しました。

３．３　データの種類を考える

各項目に、それぞれどんなデータが格納されるのかを考えます。４つは量が多いので「購入履歴」と「ユーザデータ」の２つを例にすると、図のようになります。

３．４　データの大きさを決める

注文番号やユーザIDなどのコード類について、命名規則が定まっている場合は必要な大きさを決定します。

３．５　テーブル型を決める

データの種類とデータ長について決まったら、最適なデータ型を選択します。

以上で、ごく基本的なテーブル定義が出来上がりました。

テーブル定義作成の方法とデータ型の種類について、いかがでしたでしょうか。

次回は今回作成したテーブル定義を元に、実際にSQL文を作って実行していきたいと思います。
よければ合わせてご覧下さい。

関連記事：MySQLでテーブルを作成・削除・変更する方法