タイトル: テーブル作成
SEOタイトル: SQL CREATE TABLE 完全ガイド — テーブル作成と制約
| この記事の要点 |
- 基本構文:
CREATE TABLE table_name (col1 type1, col2 type2, ...) - 主要型: INT / BIGINT / VARCHAR(n) / TEXT / DATE / DATETIME / DECIMAL(p,s) / BOOLEAN / JSON
- 制約: PRIMARY KEY / AUTO_INCREMENT / NOT NULL / DEFAULT / UNIQUE / FOREIGN KEY
- 外部キー: ON DELETE CASCADE / SET NULL / RESTRICT
- MySQL では基本 ENGINE = InnoDB + CHARSET = utf8mb4
- CREATE TABLE IF NOT EXISTS / CREATE TABLE ... LIKE / CREATE TABLE ... AS SELECT
|
基本構文
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
age TINYINT UNSIGNED,
profile TEXT,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
主要なカラム型
| 型 | サイズ・範囲 | 用途 |
|---|
TINYINT | 1B / -128〜127 | 状態フラグ |
INT | 4B / 約 ±21 億 | 一般的な ID / 数量 |
BIGINT | 8B / 約 ±9.2×10^18 | 大規模 ID / Unix epoch ms |
DECIMAL(p,s) | 固定小数 | 金額 (DECIMAL(10,2) = 整数 8 桁 + 小数 2 桁) |
FLOAT / DOUBLE | 浮動小数 | 科学計算 (金額には使わない) |
CHAR(n) | n B 固定 | 長さが揃う文字列 (郵便番号など) |
VARCHAR(n) | 最大 n 文字 (≤65535B) | 名前 / メール / 短文テキスト |
TEXT | 最大 64KB | 長文 (ブログ本文) |
MEDIUMTEXT | 最大 16MB | 記事本文 |
LONGTEXT | 最大 4GB | ログ / 大量テキスト |
DATE | 3B | 誕生日 |
DATETIME | 8B | イベント時刻 (TZ 持たない) |
TIMESTAMP | 4B / 1970〜2038 | レコードのタイムスタンプ (TZ 持つ) |
BOOLEAN (= TINYINT(1)) | 1B | 真偽値 |
JSON | 可変 | 半構造データ |
ENUM('a','b') | 1〜2B | 固定列挙 |
主要な制約
CREATE TABLE orders (
-- 主キー (自動採番)
id BIGINT AUTO_INCREMENT PRIMARY KEY,
-- NOT NULL
user_id BIGINT NOT NULL,
-- DEFAULT
status VARCHAR(20) NOT NULL DEFAULT 'pending',
-- CHECK (MySQL 8.0+ / PostgreSQL)
amount DECIMAL(10,2) NOT NULL CHECK (amount >= 0),
-- UNIQUE
order_no VARCHAR(50) NOT NULL UNIQUE,
-- 複数列 UNIQUE
UNIQUE KEY uq_user_order (user_id, order_no),
-- インデックス
KEY idx_user_created (user_id, created_at),
-- 外部キー
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB CHARSET = utf8mb4;
FOREIGN KEY の参照アクション
| アクション | 親が削除/更新されたら子は |
|---|
CASCADE | 連動して削除/更新 |
SET NULL | 外部キー列に NULL を入れる (NOT NULL では使えない) |
SET DEFAULT | DEFAULT 値にする (InnoDB は非対応) |
RESTRICT (= NO ACTION) | 子があるなら親の操作を拒否 (デフォルト) |
テーブル作成の便利構文
-- 既にあれば作らない
CREATE TABLE IF NOT EXISTS users ( ... );
-- 既存テーブルと同じ構造を作る (データは入らない)
CREATE TABLE users_backup LIKE users;
-- 既存データから新テーブルを作る (制約は引き継がない)
CREATE TABLE users_old AS
SELECT * FROM users WHERE created_at < '2020-01-01';
-- 一時テーブル (接続切れたら自動削除)
CREATE TEMPORARY TABLE tmp_sum AS
SELECT user_id, SUM(amount) AS total
FROM orders GROUP BY user_id;
ENGINE と CHARSET の意味
| 項目 | 推奨 | 理由 |
|---|
| ENGINE | InnoDB | トランザクション / 外部キー / 行ロック対応。MySQL 5.5+ で既定 |
| ENGINE | MyISAM (旧) | テーブルロック・トランザクション無し。新規利用非推奨 |
| CHARSET | utf8mb4 | 4 バイト UTF-8 = 絵文字対応。utf8 は 3 バイトで絵文字非対応 |
| COLLATE | utf8mb4_unicode_ci or utf8mb4_0900_ai_ci | 言語ソート / 大小区別の方針。日本語混在なら _unicode_ci |
命名のコツ
- テーブル名は複数形・スネークケース (
users / order_items)
- 外部キー名は
fk_子テーブル_親テーブル (fk_orders_user)
- UNIQUE は
uq_テーブル_カラム
- インデックスは
idx_テーブル_カラム
- 主キーは原則
id + BIGINT (将来 INT が枯渇するリスク回避)
変更系コマンド
-- カラム追加
ALTER TABLE users ADD COLUMN nickname VARCHAR(50);
-- カラム削除
ALTER TABLE users DROP COLUMN nickname;
-- カラム名・型変更 (MySQL)
ALTER TABLE users CHANGE name full_name VARCHAR(200) NOT NULL;
-- 型のみ変更
ALTER TABLE users MODIFY email VARCHAR(320);
-- インデックス追加
CREATE INDEX idx_email ON users (email);
-- テーブル削除
DROP TABLE users;
DROP TABLE IF EXISTS users;
FAQ
Q: VARCHAR と TEXT どちらを使えば？
A: 255 文字以内は VARCHAR、長文は TEXT。VARCHAR にもインデックス張れる、ソートも速い。
Q: DATETIME と TIMESTAMP の違い
A: TIMESTAMP は UTC で保存され接続のタイムゾーンに変換、範囲 1970〜2038。DATETIME はタイムゾーン非依存で 1000〜9999。長期保存系は DATETIME 推奨。
Q: 主キーを UUID にしたい
A: CHAR(36) (文字列) または BINARY(16) (バイナリ)。MySQL 8 なら関数 UUID_TO_BIN() / BIN_TO_UUID() あり。インデックス効率は連番より落ちる点に注意。
