MySQLでお手軽テーブル定義

さくらインターネットのコントロールパネルから使えるphpMyAdminというMySQLのデータベース管理ツールが簡単で便利そうなので、MySQLでテーブルを作ってみました。属性の指定時に、プルダウンでいろいろ選択肢がでてきてどれを選べばよいか迷って調べたので、その内容をメモとして残しておきます。

テーブル名の命名規則

大文字小文字の混在は避けて、小文字とアンダースコアで名前をつけるのが一般的のようです。

特に、経験豊富な方のブログ記事を見てみると、先頭にテーブルと分かるような接頭辞をつけています。

そこで、私も真似して、なるべくタイプが楽なように次のようにテーブル名をつけることにしました。

t_table_name

文字コード

UTF8の中にもいろいろな種類があってどれを使えば良いのか悩みます。

  • utf8_bin
  • utf8_general_ci
  • utf8_unicode_ci
  • utf8mb4_bin
  • utf8mb4_general_ci
  • utf8mb4_unicode_ci

etc…

調べたところ、utf8_binはアルファベットの大文字小文字を区別するようです。

utf8_general_ci, utf8_unicode_ci は、共に大文字小文字の区別なしで、uf8_unicode_ciの方が正確、utf8_general_ciの方が速いようです。（デフォルトは、utf8_general_ci）

マニュアルには、日本語のことが書かれていませんが、uf8_unicode_ciを指定すると、半角と全角を同一扱いにし、また濁点の有無を区別せずに同一の文字とみなすようです。これらについて、utf8_general_ciは素直に別の文字とみなすようです。大雑把にはuf8_unicode_ciが良さそうですが、濁点を認識しないのはちょっと使いにくいですね。

（参考：end0tknrのkipple – mysqlのcollateを使って大文字-小文字や全角-半角を無視した検索）

（参考：SQL5.5リファレンスマニュアル – Unicode Character Sets）

mb4は、4バイトの文字コードで、JIS第3・第4水準漢字が扱えるようになるらしい。

ぱっと見マイナーな漢字が多そうなので特に必要なさそうな気もしますが、たまに一般的な単語の文字数カウントで問題になることがあるみたい。

（参考：inquisitor – Twitter時代の文字の数え方）

たまにしか表面化しない問題のために毎回1.5倍の領域が使われてしまいそうなのが少し気になるところですが、新し物好きな私は、utf8mb4_general_ciを選んでしまうのでありました。

文字型の違い

文字列を格納するのに、char, varchar, text, …と、たくさんの文字型があってどれを使うか迷います。

マニュアルからそれぞれの特徴を抜粋。

  • CHAR 型のカラムの長さは、テーブルの作成時に宣言した長さ（1から255）に固定される
  • CHAR 型の値は、格納時に、指定された長さになるよう右側にスペースが埋め込まれる
  • CHAR 値の取り出し時には、後続のスペースが削除される
  • VARCHAR 型のカラムの値は可変長の文字列で 0 〜 65,535 の間で最大長を宣言する
  • VARCHAR 型の値は、「必要な文字数＋長さを記録するための 1 バイト」の領域に格納され、値に埋め込み処理が行われることはない
  • VARCHAR 型の値は、値の格納時、後続のスペースは削除される
  • TEXT 型のカラムでは値の格納時に後続のスペースが削除されない
  • TEXT 型のカラムには、DEFAULT 値は設定できない
  • TEXT 型のカラムで、GROUP BY または ORDER BY を使用する場合、カラムの値を固定長のオブジェクトに変換する必要がある（SUBSTRING 関数を使うなどして）
  • CHAR 型、 VARCHAR 型に、そのカラムの最大長を超える値を割り当てると、カラムのサイズに合わせて値が切り捨てられ、TEXT 型のカラムの場合は、ストリクトモードを利用してその値を拒否させる事ができる。
値 CHAR(4) 要求ストレージ VARCHAR(4) 要求ストレージ
'' ' ' 4バイト '' 1バイト
'ab' 'ab ' 4 バイト 'ab' 3バイト
'abcd' 'abcd' 4 バイト 'abcd' 5バイト
'abcdefgh' 'abcd' 4バイト 'abcd' 5バイト

（参考：SQL5.1リファレンスマニュアル – 文字列タイプ）

65,535バイト以内の比較的長い文字列ならvarcharが一番扱いが楽で効率が良さそうです。

数値タイプ

タイプ バイト 最小値 最大値
(Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

※BOOLEAN は、TINYINTとして扱われる。0=>false, !0 => true

（参考：SQL5.1リファレンスマニュアル – 数値タイプ）

こちらの記事もどうぞ