Webサービスを構築するためのDB(データベース)の設計(MySQL)
MySQLでおすすめの照合順序
データベースでの「照合順序（Collation）」とは、文字列同士の比較や並び順を決めるルールのことです。
例えば、
- 「a」と「A」を同じとみなすか区別するか
- 日本語の「あ」と「ア」をどのように並べるか
- 絵文字や多言語で正しいソートができるか
といった動作に大きく影響します。
utf8mb4_0900_ai_ci
- MySQL 8.0以降で推奨
- Unicode 9.0ベースの最新照合順序
- utf8mb4なので絵文字も格納可能
- ai = accent-insensitive（アクセント無視）
- ci = case-insensitive（大文字小文字無視）
utf8mb4_unicode_ci（MySQL 5.7以前）
MySQL 5.7以前を使用している場合は、utf8mb4_unicode_ciが安定しており推奨されます。
ただし8.0以降では utf8mb4_0900_ai_ciの方が
- ソート精度が高い
- パフォーマンス面でも最適化
されています。
int(11)やbigint(20)など型サイズの意味と最大値
MySQLでテーブルを設計する際、カラム型にint(11)やbigint(20)など数字を指定しますが、
- この数字（表示幅）は最大値に影響するのか？
- 各型の最大値はどのくらい？
と疑問に思ったことはないでしょうか。
数字（表示幅）は最大値に影響しない
例えばint(11)の (11) は表示幅であり、最大値や格納できる値の範囲には一切影響しません。
INT(11) UNSIGNEDと
INT(255) UNSIGNEDで格納できる数値の最大値は同じです。
各数値型の最大値一覧
| 型 | 最大値（符号なし UNSIGNED） | 備考 |
|---|---|---|
| TINYINT(1) | 255 | (符号ありの場合は -128～127) |
| INT(11) | 4,294,967,295 | (UNSIGNEDの場合。符号ありは ±2,147,483,647) |
| BIGINT(20) | 18,446,744,073,709,551,615 | (UNSIGNEDの場合。符号ありは ±9,223,372,036,854,775,807) |
POINT
- (1)、(11)、(20) はあくまで 表示幅
- 現在はMySQLのZEROFILLと組み合わせない限り無視されるため、基本的に表示幅指定は不要です。
文字列型（TEXT系）の最大サイズ
| 型 | 最大サイズ | 備考 |
|---|---|---|
| TEXT | 65,535バイト（約64KB） | 2の16乗 |
| MEDIUMTEXT | 16,777,215バイト（約16MB） | 2の24乗 |
| LONGTEXT | 4,294,967,295バイト（約4GB） | 2の32乗 |
日付型の選択
| 型 | 特徴 | 注意点 |
|---|---|---|
| DATETIME | 1000-01-01 00:00:00 から 9999-12-31 23:59:59 | 日付管理はこちらを優先 |
| TIMESTAMP | 1970-01-01 00:00:01 UTC から 2038-01-19 03:14:07 UTC | 2038年問題があるため注意 |
カラムの設計の例
| 用途 | SQL例 | 型説明 | ポイント |
|---|---|---|---|
| ID（標準） | id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY | BIGINT(20) UNSIGNED | 符号なしで0から始まる。表示幅20は意味なし（省略可）。 |
| INT型ID（小規模サービス向け） | id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY | INT(11) UNSIGNED | 最大値4,294,967,295。中小規模サービス向け。 |
| UUID型ID | id CHAR(36) | CHAR(36) | UUID（xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx形式）を格納。分散DB向け。 |
| 日付型（DATETIME） | created_at DATETIME DEFAULT CURRENT_TIMESTAMP | DATETIME | 1000-01-01～9999-12-31対応。2038年問題なし。 |
| 日付型（TIMESTAMP） | updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | TIMESTAMP | 1970-2038年対応。自動更新に便利だが2038年問題あり。 |
| 短文テキスト | title VARCHAR(255) | VARCHAR(255) | 可変長文字列。上限255文字だがutf8mb4ではバイト数に注意。 |
| 長文テキスト | content TEXT | TEXT | 最大65,535バイト（約64KB）。ブログ記事本文などに。 |
| 中長文テキスト | content MEDIUMTEXT | MEDIUMTEXT | 最大16MBまで対応。 |
| 超長文テキスト | content LONGTEXT | LONGTEXT | 最大4GBまで対応。 |
| ENUM型（ステータス管理） | status ENUM(‘draft’, ‘published’, ‘archived’) DEFAULT ‘draft’ | ENUM | 限定的な状態値を持つカラム。検索性も良好。 |
| TINYINT型フラグ | is_active TINYINT(1) UNSIGNED DEFAULT 0 | TINYINT(1) UNSIGNED | 0か1のフラグ用途。(1)は表示幅で意味なし。 |
型選定のポイント
- IDはBIGINT UNSIGNEDを推奨
大規模サービスでも枯渇しない。 - DATETIME優先
TIMESTAMPは2038年問題に注意。 - TEXT型は用途ごとにTEXT / MEDIUMTEXT / LONGTEXTを選ぶ
- ENUMは限定状態管理に便利
状態追加時のALTER制約に注意。
まとめ
- (n) の数字は表示幅であり、最大値には影響しない
- INTとBIGINTの違いは格納可能範囲
- テキスト量によってTEXT系を使い分ける
- 日付はDATETIME優先、TIMESTAMPは2038年問題に注意