この記事の要点

基本構文: 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() あり。インデックス効率は連番より落ちる点に注意。