【phpmyadmin】MySQLでのDB･テーブル作成･カラム定義の詳細｜サンプル

1.データベース作成

-- データベースの作成（基本）
CREATE DATABASE sample_company;

-- 文字セットと照合順序を指定したデータベース作成（推奨）
CREATE DATABASE sample_company
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 存在チェック付きでデータベース作成
CREATE DATABASE IF NOT EXISTS sample_company
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- データベースの使用開始
USE sample_company;

-- 作成したデータベースの確認
SHOW DATABASES;

-- データベースの文字セット確認
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'sample_company';

スポンサーリンク

2.テーブル作成

-- 包括的なテーブル作成例（全ての要素を含む）
CREATE TABLE users (
    -- 主キー（AUTO_INCREMENT）
    id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ユーザーID（主キー）',
    
    -- 文字列型のカラム
    username VARCHAR(50) NOT NULL COMMENT 'ユーザー名（ログイン用）'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    -- メールアドレス（ユニーク制約付き）
    email VARCHAR(100) NOT NULL COMMENT 'メールアドレス'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    -- パスワード（ハッシュ化されたもの）
    password VARCHAR(255) NOT NULL COMMENT 'パスワード（ハッシュ化）'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    -- 名前（日本語対応）
    first_name VARCHAR(50) DEFAULT NULL COMMENT '名'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    last_name VARCHAR(50) DEFAULT NULL COMMENT '姓'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    -- 数値型
    age TINYINT(3) UNSIGNED DEFAULT NULL COMMENT '年齢（0-255）',
    salary DECIMAL(10,2) DEFAULT 0.00 COMMENT '給与',
    
    -- 列挙型
    gender ENUM('male', 'female', 'other') DEFAULT NULL COMMENT '性別',
    status ENUM('active', 'inactive', 'suspended', 'deleted') 
        DEFAULT 'active' COMMENT 'アカウント状態',
    
    -- 日付・時刻型
    birth_date DATE DEFAULT NULL COMMENT '生年月日',
    hire_date DATETIME DEFAULT NULL COMMENT '入社日時',
    last_login_at TIMESTAMP NULL DEFAULT NULL COMMENT '最終ログイン日時',
    
    -- 自動更新される日時
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
        COMMENT '更新日時',
    
    -- テキスト型
    bio TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 
        DEFAULT NULL COMMENT '自己紹介文',
    
    -- JSON型（MySQL 5.7以降）
    preferences JSON DEFAULT NULL COMMENT 'ユーザー設定（JSON形式）',
    
    -- バイナリ型
    profile_image MEDIUMBLOB DEFAULT NULL COMMENT 'プロフィール画像',
    
    -- ブール型（TINYINT(1)として実装）
    is_active BOOLEAN DEFAULT TRUE COMMENT 'アクティブフラグ',
    is_verified BOOLEAN DEFAULT FALSE COMMENT '認証済みフラグ',
    
    -- 主キーの設定
    PRIMARY KEY (id),
    
    -- ユニークインデックス
    UNIQUE KEY idx_username (username),
    UNIQUE KEY idx_email (email),
    
    -- 通常のインデックス
    KEY idx_status (status),
    KEY idx_created_at (created_at),
    KEY idx_name (last_name, first_name),
    
    -- 複合インデックス
    KEY idx_status_created (status, created_at)
    
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci 
  COMMENT='ユーザー管理テーブル';

-- より複雑なテーブル例（外部キー制約付き）
CREATE TABLE user_profiles (
    profile_id INT(11) NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL,
    
    -- 住所関連
    postal_code VARCHAR(10) DEFAULT NULL COMMENT '郵便番号',
    prefecture VARCHAR(20) DEFAULT NULL COMMENT '都道府県',
    city VARCHAR(50) DEFAULT NULL COMMENT '市区町村',
    address1 VARCHAR(100) DEFAULT NULL COMMENT '住所1',
    address2 VARCHAR(100) DEFAULT NULL COMMENT '住所2（建物名等）',
    
    -- 連絡先
    phone VARCHAR(20) DEFAULT NULL COMMENT '電話番号',
    mobile VARCHAR(20) DEFAULT NULL COMMENT '携帯電話',
    
    -- SNS
    twitter_handle VARCHAR(50) DEFAULT NULL COMMENT 'Twitterハンドル',
    facebook_url VARCHAR(200) DEFAULT NULL COMMENT 'Facebook URL',
    
    -- 設定項目
    timezone VARCHAR(50) DEFAULT 'Asia/Tokyo' COMMENT 'タイムゾーン',
    language VARCHAR(10) DEFAULT 'ja' COMMENT '言語設定',
    
    -- メタ情報
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    PRIMARY KEY (profile_id),
    
    -- 外部キー制約
    FOREIGN KEY (user_id) REFERENCES users(id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    
    -- インデックス
    UNIQUE KEY idx_user_id (user_id),
    KEY idx_postal_code (postal_code),
    KEY idx_prefecture_city (prefecture, city)
    
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci 
  COMMENT='ユーザープロフィール詳細テーブル';

-- パーティション分割テーブルの例
CREATE TABLE access_logs (
    log_id BIGINT NOT NULL AUTO_INCREMENT,
    user_id INT(11) DEFAULT NULL,
    ip_address VARCHAR(45) NOT NULL COMMENT 'IPアドレス（IPv6対応）',
    user_agent TEXT DEFAULT NULL COMMENT 'ユーザーエージェント',
    request_uri VARCHAR(500) DEFAULT NULL COMMENT 'リクエストURI',
    http_method VARCHAR(10) DEFAULT 'GET' COMMENT 'HTTPメソッド',
    response_code SMALLINT(3) DEFAULT NULL COMMENT 'レスポンスコード',
    response_time DECIMAL(8,3) DEFAULT NULL COMMENT 'レスポンス時間（秒）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (log_id, created_at),
    KEY idx_user_id (user_id),
    KEY idx_ip_address (ip_address),
    KEY idx_created_at (created_at)
    
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci
  COMMENT='アクセスログテーブル'
  
-- 月単位でパーティション分割
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404),
    PARTITION p202404 VALUES LESS THAN (202405),
    PARTITION p202405 VALUES LESS THAN (202406),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

スポンサーリンク

3.カラム定義の詳細な基準と決め方

1. カラム名（Name）の基準

命名規則

  • スネークケース使用: user_id, created_at, first_name
  • 予約語を避ける: order → user_order, group → user_group
  • 略語は避ける: usr → user, addr → address
  • 複数形・単数形の統一: テーブル名は複数形、カラム名は単数形

推奨命名パターン

ID系: id, user_id, product_id
名前系: name, title, username, first_name, last_name
日時系: created_at, updated_at, deleted_at, published_at
フラグ系: is_active, is_verified, has_permission
状態系: status, state, type, category

2. データ型（Type）の選び方

数値型

型用途範囲備考
TINYINTフラグ、小さな数値-128〜127boolean代用に1使用
SMALLINT小さな整数-32,768〜32,767
MEDIUMINT中程度の整数-8,388,608〜8,388,607
INT一般的な整数-2,147,483,648〜2,147,483,647主キーに最適
BIGINT大きな整数-9,223,372,036,854,775,808〜…ログID、タイムスタンプ
DECIMAL(M,D)精密な小数M=総桁数, D=小数点以下金額、パーセンテージ
FLOAT単精度浮動小数点約7桁の精度科学計算
DOUBLE倍精度浮動小数点約15桁の精度高精度計算

文字列型

型用途最大長備考
VARCHAR(n)可変長文字列65,535文字一般的なテキスト
CHAR(n)固定長文字列255文字固定長コード（郵便番号等）
TEXT長文テキスト65,535文字説明文、コメント
MEDIUMTEXTより長いテキスト16,777,215文字記事本文
LONGTEXT最長テキスト4,294,967,295文字大容量テキスト

日付・時刻型

型用途形式備考
DATE日付のみYYYY-MM-DD生年月日
TIME時刻のみHH:MM:SS営業時間
DATETIME日付と時刻YYYY-MM-DD HH:MM:SSイベント日時
TIMESTAMPタイムスタンプYYYY-MM-DD HH:MM:SS自動更新対応
YEAR年のみYYYY卒業年

3. 長さ/値（Length/Values）の決め方

VARCHAR長さの目安

-- メールアドレス: 最大320文字（RFC準拠）だが実用的には100-150
email VARCHAR(150)

-- 名前: 日本語考慮で50文字程度
name VARCHAR(50)

-- パスワードハッシュ: bcryptは60文字、将来性考慮で255
password VARCHAR(255)

-- URL: 2048文字が一般的な上限
url VARCHAR(2048)

-- 電話番号: 国際番号考慮で20文字
phone VARCHAR(20)

DECIMAL精度の設定

-- 金額: 10桁全体、2桁小数（99,999,999.99まで）
price DECIMAL(10,2)

-- パーセンテージ: 5桁全体、2桁小数（999.99%まで）
percentage DECIMAL(5,2)

-- 重量（kg）: 8桁全体、3桁小数（99999.999kgまで）
weight DECIMAL(8,3)

4. デフォルト値（Default）の設定指針

一般的なデフォルト値

-- 文字列: NULLまたは空文字
name VARCHAR(50) DEFAULT NULL
title VARCHAR(100) DEFAULT ''

-- 数値: 0または意味のある初期値
score INT DEFAULT 0
count BIGINT DEFAULT 0

-- 日時: 現在時刻または NULL
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- ブール値: FALSE（0）が一般的
is_active BOOLEAN DEFAULT FALSE
is_verified BOOLEAN DEFAULT FALSE

-- 列挙型: 最も一般的な値
status ENUM('active', 'inactive') DEFAULT 'inactive'

5. 照合順序（Collation）の選択

日本語環境での推奨設定

-- Unicode対応（推奨）
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

-- 大文字小文字を区別しない検索用
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

-- バイナリ比較（大文字小文字区別）
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

用途別照合順序

  • utf8mb4_unicode_ci: 一般的なテキスト、名前、説明文
  • utf8mb4_general_ci: 高速検索が必要な場合
  • utf8mb4_bin: パスワード、トークン、厳密比較が必要

6. 属性（Attributes）の使い分け

-- UNSIGNED: 負数を使わない数値（ID、カウント等）
user_id INT UNSIGNED

-- ZEROFILL: 先頭ゼロ埋め（商品コード等）
product_code INT(6) ZEROFILL  -- 000001, 000002...

-- BINARY: バイナリ比較（大文字小文字区別）
username VARCHAR(50) BINARY

-- AUTO_INCREMENT: 自動増分（主キー）
id INT AUTO_INCREMENT

7. NULL許可の判断基準

NULLを許可する場合

  • オプション項目（生年月日、プロフィール画像等）
  • 外部キー（所属なしの場合を想定）
  • 計算結果格納用（まだ計算されていない状態）

NULLを禁止する場合

-- 必須項目
name VARCHAR(50) NOT NULL
email VARCHAR(100) NOT NULL

-- 主キー（自動的にNOT NULL）
id INT AUTO_INCREMENT PRIMARY KEY

-- 外部キー（親が必須の場合）
user_id INT NOT NULL

8. インデックス（Index）の設定戦略

主キー（PRIMARY）

-- 単一カラム主キー
id INT AUTO_INCREMENT PRIMARY KEY

-- 複合主キー
PRIMARY KEY (user_id, project_id)

ユニークインデックス（UNIQUE）

-- 単一カラムユニーク
email VARCHAR(100) UNIQUE

-- 複合ユニーク
UNIQUE KEY unique_user_project (user_id, project_id)

一般インデックス（INDEX）

-- 検索頻度が高いカラム
KEY idx_status (status)
KEY idx_created_at (created_at)

-- 複合インデックス（カラム順序重要）
KEY idx_status_date (status, created_at)  -- status=xxでソート

フルテキストインデックス（FULLTEXT）

-- 文章検索用（MyISAMまたはInnoDB）
FULLTEXT KEY ft_content (title, content)

9. AUTO_INCREMENT（A_I）の使用指針

適用対象

  • 主キー（最も一般的）
  • 連番が必要な業務キー
  • ログのシーケンス番号

設定例

-- 基本設定
id INT AUTO_INCREMENT PRIMARY KEY

-- 開始値指定
id INT AUTO_INCREMENT PRIMARY KEY AUTO_INCREMENT=1000

-- 複合主キーの一部として
user_id INT,
sequence_id INT AUTO_INCREMENT,
PRIMARY KEY (user_id, sequence_id)

10. コメント（Comment）のベストプラクティス

良いコメント例

id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ユーザーID（主キー）',
email VARCHAR(150) NOT NULL COMMENT 'ログイン用メールアドレス（重複不可）',
status ENUM('active','suspended','deleted') COMMENT 'アカウント状態: active=通常, suspended=停止, deleted=削除済み',
last_login_at TIMESTAMP NULL COMMENT '最終ログイン日時（未ログインの場合NULL）'

コメント記載指針

  • 用途・制約を明記
  • 列挙値の意味を説明
  • NULLの意味を説明
  • 単位や形式を明記（通貨、日付フォーマット等）

11. phpMyAdmin特有の設定項目

仮想カラム（Virtual）

-- 計算結果を仮想カラムとして定義
full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
age INT AS (YEAR(CURDATE()) - YEAR(birth_date)) STORED

メディア型・表示変換

  • IMAGE: 画像ファイル表示用
  • LINK: URLリンク表示用
  • DATE: 日付フォーマット指定
  • JSON: JSON整形表示

これらの設定により、phpMyAdminでのデータ表示・入力が最適化されます。

スポンサーリンク

4.実践的なテーブル作成

-- ===========================================
-- ECサイト用テーブル設計例（phpMyAdmin最適化）
-- ===========================================

-- 1. 商品マスタテーブル
CREATE TABLE products (
    product_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT 
        COMMENT '商品ID（主キー）',
    
    product_code VARCHAR(20) NOT NULL 
        COMMENT '商品コード（JAN等）'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
    
    product_name VARCHAR(200) NOT NULL 
        COMMENT '商品名'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    category_id INT(11) UNSIGNED DEFAULT NULL 
        COMMENT 'カテゴリID（categories.idの外部キー）',
    
    brand_id INT(11) UNSIGNED DEFAULT NULL 
        COMMENT 'ブランドID（brands.idの外部キー）',
    
    price DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 
        COMMENT '販売価格（税込み、円）',
    
    cost DECIMAL(10,2) UNSIGNED DEFAULT NULL 
        COMMENT '仕入原価（円）',
    
    stock_quantity INT(11) UNSIGNED NOT NULL DEFAULT 0 
        COMMENT '在庫数量',
    
    weight DECIMAL(8,3) UNSIGNED DEFAULT NULL 
        COMMENT '重量（kg）',
    
    dimensions VARCHAR(50) DEFAULT NULL 
        COMMENT 'サイズ（WxHxD cm）',
    
    description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 
        DEFAULT NULL COMMENT '商品説明文',
    
    specifications JSON DEFAULT NULL 
        COMMENT '仕様詳細（JSON形式）',
    
    main_image VARCHAR(255) DEFAULT NULL 
        COMMENT 'メイン商品画像パス',
    
    status ENUM('draft','active','discontinued','out_of_stock') 
        NOT NULL DEFAULT 'draft'
        COMMENT '商品状態: draft=下書き, active=販売中, discontinued=廃番, out_of_stock=品切れ',
    
    is_featured BOOLEAN NOT NULL DEFAULT FALSE 
        COMMENT 'おすすめ商品フラグ',
    
    is_digital BOOLEAN NOT NULL DEFAULT FALSE 
        COMMENT 'デジタル商品フラグ（配送不要）',
    
    tax_rate DECIMAL(5,3) NOT NULL DEFAULT 10.000 
        COMMENT '税率（%）',
    
    release_date DATE DEFAULT NULL 
        COMMENT '発売日',
    
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        COMMENT '登録日時',
    
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
        COMMENT '更新日時',
    
    -- インデックス定義
    PRIMARY KEY (product_id),
    UNIQUE KEY uk_product_code (product_code),
    KEY idx_category (category_id),
    KEY idx_brand (brand_id),
    KEY idx_status (status),
    KEY idx_price (price),
    KEY idx_featured_status (is_featured, status),
    KEY idx_created_at (created_at),
    
    -- 全文検索インデックス
    FULLTEXT KEY ft_search (product_name, description)
    
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci 
  COMMENT='商品マスタテーブル';

-- 2. 顧客テーブル（個人情報を含む）
CREATE TABLE customers (
    customer_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT 
        COMMENT '顧客ID（主キー）',
    
    customer_no VARCHAR(20) NOT NULL 
        COMMENT '顧客番号（表示用）',
    
    email VARCHAR(150) NOT NULL 
        COMMENT 'ログイン用メールアドレス'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    password_hash VARCHAR(255) NOT NULL 
        COMMENT 'パスワードハッシュ（bcrypt）'
        CHARACTER SET ascii COLLATE ascii_general_ci,
    
    title ENUM('', '様', 'さん', 'Mr.', 'Ms.', 'Dr.') 
        DEFAULT '' COMMENT '敬称',
    
    first_name VARCHAR(50) NOT NULL 
        COMMENT '名前（名）'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    last_name VARCHAR(50) NOT NULL 
        COMMENT '名前（姓）'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    first_name_kana VARCHAR(50) DEFAULT NULL 
        COMMENT '名前カナ（名）'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    last_name_kana VARCHAR(50) DEFAULT NULL 
        COMMENT '名前カナ（姓）'
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    -- 仮想カラムでフルネーム生成
    full_name VARCHAR(101) AS (CONCAT(last_name, ' ', first_name)) VIRTUAL 
        COMMENT '氏名（姓名）',
    
    gender ENUM('male', 'female', 'other', 'not_specified') 
        DEFAULT 'not_specified' COMMENT '性別',
    
    birth_date DATE DEFAULT NULL 
        COMMENT '生年月日',
    
    -- 年齢を仮想カラムで自動計算
    age TINYINT(3) UNSIGNED AS (
        CASE 
            WHEN birth_date IS NULL THEN NULL
            ELSE YEAR(CURDATE()) - YEAR(birth_date) - 
                 (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(birth_date, '%m%d'))
        END
    ) VIRTUAL COMMENT '年齢（自動計算）',
    
    phone VARCHAR(20) DEFAULT NULL 
        COMMENT '電話番号（ハイフン付き可）',
    
    mobile VARCHAR(20) DEFAULT NULL 
        COMMENT '携帯電話番号',
    
    postal_code VARCHAR(8) DEFAULT NULL 
        COMMENT '郵便番号（ハイフン付き）',
    
    prefecture VARCHAR(10) DEFAULT NULL 
        COMMENT '都道府県',
    
    city VARCHAR(50) DEFAULT NULL 
        COMMENT '市区町村',
    
    address1 VARCHAR(100) DEFAULT NULL 
        COMMENT '住所1（番地まで）',
    
    address2 VARCHAR(100) DEFAULT NULL 
        COMMENT '住所2（建物名・部屋番号）',
    
    company_name VARCHAR(100) DEFAULT NULL 
        COMMENT '会社名',
    
    department VARCHAR(50) DEFAULT NULL 
        COMMENT '部署名',
    
    newsletter_subscribed BOOLEAN NOT NULL DEFAULT FALSE 
        COMMENT 'メルマガ購読フラグ',
    
    marketing_consent BOOLEAN NOT NULL DEFAULT FALSE 
        COMMENT 'マーケティング利用同意フラグ',
    
    status ENUM('active', 'inactive', 'suspended', 'deleted') 
        NOT NULL DEFAULT 'active'
        COMMENT '顧客状態',
    
    customer_rank ENUM('bronze', 'silver', 'gold', 'platinum', 'diamond') 
        DEFAULT 'bronze' COMMENT '顧客ランク',
    
    total_orders INT(11) UNSIGNED NOT NULL DEFAULT 0 
        COMMENT '累計注文回数',
    
    total_amount DECIMAL(12,2) UNSIGNED NOT NULL DEFAULT 0.00 
        COMMENT '累計購入金額（円）',
    
    last_order_at TIMESTAMP NULL DEFAULT NULL 
        COMMENT '最終注文日時',
    
    last_login_at TIMESTAMP NULL DEFAULT NULL 
        COMMENT '最終ログイン日時',
    
    email_verified_at TIMESTAMP NULL DEFAULT NULL 
        COMMENT 'メール認証完了日時',
    
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        COMMENT '登録日時',
    
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
        COMMENT '更新日時',
    
    -- インデックス定義
    PRIMARY KEY (customer_id),
    UNIQUE KEY uk_customer_no (customer_no),
    UNIQUE KEY uk_email (email),
    KEY idx_name (last_name, first_name),
    KEY idx_postal_code (postal_code),
    KEY idx_status (status),
    KEY idx_rank (customer_rank),
    KEY idx_last_order (last_order_at),
    KEY idx_created_at (created_at),
    
    -- 複合インデックス
    KEY idx_status_rank (status, customer_rank)
    
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci 
  COMMENT='顧客マスタテーブル';

-- 3. 注文テーブル（外部キー制約付き）
CREATE TABLE orders (
    order_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT 
        COMMENT '注文ID（主キー）',
    
    order_no VARCHAR(20) NOT NULL 
        COMMENT '注文番号（表示用）'
        CHARACTER SET ascii COLLATE ascii_general_ci,
    
    customer_id INT(11) UNSIGNED NOT NULL 
        COMMENT '顧客ID（customers.customer_idの外部キー）',
    
    order_status ENUM(
        'pending',        -- 注文受付
        'confirmed',      -- 注文確定
        'processing',     -- 処理中
        'shipped',        -- 出荷済み
        'delivered',      -- 配達完了
        'cancelled',      -- キャンセル
        'refunded'        -- 返金済み
    ) NOT NULL DEFAULT 'pending' COMMENT '注文状態',
    
    payment_status ENUM(
        'pending',        -- 決済待ち
        'processing',     -- 決済処理中
        'completed',      -- 決済完了
        'failed',         -- 決済失敗
        'refunded',       -- 返金済み
        'partially_refunded' -- 部分返金
    ) NOT NULL DEFAULT 'pending' COMMENT '決済状態',
    
    payment_method ENUM(
        'credit_card',    -- クレジットカード
        'bank_transfer',  -- 銀行振込
        'convenience',    -- コンビニ決済
        'cod',           -- 代金引換
        'paypal',        -- PayPal
        'amazon_pay'     -- Amazon Pay
    ) DEFAULT NULL COMMENT '決済方法',
    
    -- 金額関連（税込み）
    subtotal DECIMAL(12,2) UNSIGNED NOT NULL DEFAULT 0.00 
        COMMENT '小計（税抜き）',
    
    tax_amount DECIMAL(12,2) UNSIGNED NOT NULL DEFAULT 0.00 
        COMMENT '消費税額',
    
    shipping_fee DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 
        COMMENT '送料',
    
    discount_amount DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 
        COMMENT '割引額',
    
    total_amount DECIMAL(12,2) UNSIGNED NOT NULL DEFAULT 0.00 
        COMMENT '合計金額（税込み）',
    
    -- 配送先情報
    shipping_title VARCHAR(10) DEFAULT NULL COMMENT '配送先敬称',
    shipping_last_name VARCHAR(50) NOT NULL COMMENT '配送先姓',
    shipping_first_name VARCHAR(50) NOT NULL COMMENT '配送先名',
    shipping_postal_code VARCHAR(8) DEFAULT NULL COMMENT '配送先郵便番号',
    shipping_prefecture VARCHAR(10) DEFAULT NULL COMMENT '配送先都道府県',
    shipping_city VARCHAR(50) DEFAULT NULL COMMENT '配送先市区町村',
    shipping_address1 VARCHAR(100) DEFAULT NULL COMMENT '配送先住所1',
    shipping_address2 VARCHAR(100) DEFAULT NULL COMMENT '配送先住所2',
    shipping_phone VARCHAR(20) DEFAULT NULL COMMENT '配送先電話番号',
    
    -- 請求先情報（省略可能）
    billing_same_as_shipping BOOLEAN NOT NULL DEFAULT TRUE 
        COMMENT '請求先と配送先が同じかどうか',
    
    -- 配送関連
    shipping_method VARCHAR(50) DEFAULT NULL 
        COMMENT '配送方法（宅急便、メール便等）',
    
    tracking_number VARCHAR(50) DEFAULT NULL 
        COMMENT '配送追跡番号',
    
    delivery_date DATE DEFAULT NULL 
        COMMENT '配送希望日',
    
    delivery_time ENUM(
        'morning',        -- 午前中
        'afternoon_1',    -- 12-14時
        'afternoon_2',    -- 14-16時
        'evening_1',      -- 16-18時
        'evening_2',      -- 18-20時
        'night'          -- 20-21時
    ) DEFAULT NULL COMMENT '配送希望時間帯',
    
    -- メモ・備考
    customer_notes TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 
        DEFAULT NULL COMMENT 'お客様からの備考',
    
    internal_notes TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 
        DEFAULT NULL COMMENT '内部メモ',
    
    -- 日時関連
    ordered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        COMMENT '注文日時',
    
    confirmed_at TIMESTAMP NULL DEFAULT NULL 
        COMMENT '注文確定日時',
    
    shipped_at TIMESTAMP NULL DEFAULT NULL 
        COMMENT '出荷日時',
    
    delivered_at TIMESTAMP NULL DEFAULT NULL 
        COMMENT '配達完了日時',
    
    cancelled_at TIMESTAMP NULL DEFAULT NULL 
        COMMENT 'キャンセル日時',
    
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        COMMENT 'レコード作成日時',
    
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
        COMMENT 'レコード更新日時',
    
    -- インデックス定義
    PRIMARY KEY (order_id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_customer (customer_id),
    KEY idx_order_status (order_status),
    KEY idx_payment_status (payment_status),
    KEY idx_ordered_at (ordered_at),
    KEY idx_shipping_date (delivery_date),
    KEY idx_tracking (tracking_number),
    
    -- 複合インデックス
    KEY idx_customer_status (customer_id, order_status),
    KEY idx_status_date (order_status, ordered_at),
    
    -- 外部キー制約
    FOREIGN KEY fk_orders_customer (customer_id) 
        REFERENCES customers(customer_id) 
        ON DELETE RESTRICT ON UPDATE CASCADE
    
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci 
  COMMENT='注文マスタテーブル';

-- 4. 注文明細テーブル
CREATE TABLE order_items (
    order_item_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT 
        COMMENT '注文明細ID（主キー）',
    
    order_id BIGINT(20) UNSIGNED NOT NULL 
        COMMENT '注文ID（orders.order_idの外部キー）',
    
    product_id INT(11) UNSIGNED NOT NULL 
        COMMENT '商品ID（products.product_idの外部キー）',
    
    -- 注文時点の商品情報（マスタ変更の影響を受けないように保存）
    product_code VARCHAR(20) NOT NULL 
        COMMENT '商品コード（注文時点）',
    
    product_name VARCHAR(200) NOT NULL 
        COMMENT '商品名（注文時点）',
    
    unit_price DECIMAL(10,2) UNSIGNED NOT NULL 
        COMMENT '単価（注文時点、税抜き）',
    
    quantity SMALLINT(5) UNSIGNED NOT NULL DEFAULT 1 
        COMMENT '数量',
    
    -- 計算値（仮想カラムまたは実際の値）
    line_total DECIMAL(12,2) UNSIGNED AS (unit_price * quantity) STORED 
        COMMENT '行合計（単価×数量）',
    
    -- オプション情報（サイズ、色など）
    product_options JSON DEFAULT NULL 
        COMMENT '商品オプション（JSON形式）',
    
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        COMMENT 'レコード作成日時',
    
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
        COMMENT 'レコード更新日時',
    
    -- インデックス定義
    PRIMARY KEY (order_item_id),
    KEY idx_order (order_id),
    KEY idx_product (product_id),
    
    -- 外部キー制約
    FOREIGN KEY fk_order_items_order (order_id) 
        REFERENCES orders(order_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    
    FOREIGN KEY fk_order_items_product (product_id) 
        REFERENCES products(product_id) 
        ON DELETE RESTRICT ON UPDATE CASCADE
    
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci 
  COMMENT='注文明細テーブル';

-- 5. ログテーブル（大容量・パーティション対応）
CREATE TABLE activity_logs (
    log_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT 
        COMMENT 'ログID（主キー）',
    
    log_type ENUM(
        'user_login',     -- ユーザーログイン
        'user_logout',    -- ユーザーログアウト
        'order_created',  -- 注文作成
        'order_updated',  -- 注文更新
        'payment_completed', -- 決済完了
        'product_viewed', -- 商品閲覧
        'cart_updated',   -- カート更新
        'admin_action'    -- 管理者操作
    ) NOT NULL COMMENT 'ログ種別',
    
    user_type ENUM('customer', 'admin', 'system', 'guest') 
        NOT NULL DEFAULT 'guest' COMMENT 'ユーザー種別',
    
    user_id INT(11) UNSIGNED DEFAULT NULL 
        COMMENT 'ユーザーID（customers.customer_id等）',
    
    target_type ENUM('customer', 'product', 'order', 'admin', 'system') 
        DEFAULT NULL COMMENT '操作対象種別',
    
    target_id BIGINT(20) UNSIGNED DEFAULT NULL 
        COMMENT '操作対象ID',
    
    action VARCHAR(100) NOT NULL 
        COMMENT '実行されたアクション',
    
    -- リクエスト情報
    ip_address VARCHAR(45) NOT NULL 
        COMMENT 'IPアドレス（IPv6対応）'
        CHARACTER SET ascii COLLATE ascii_general_ci,
    
    user_agent TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 
        DEFAULT NULL COMMENT 'ユーザーエージェント',
    
    referer VARCHAR(500) DEFAULT NULL 
        COMMENT 'リファラーURL',
    
    request_uri VARCHAR(500) DEFAULT NULL 
        COMMENT 'リクエストURI',
    
    -- 詳細情報
    details JSON DEFAULT NULL 
        COMMENT '詳細情報（JSON形式）',
    
    success BOOLEAN NOT NULL DEFAULT TRUE 
        COMMENT '成功/失敗フラグ',
    
    error_message TEXT DEFAULT NULL 
        COMMENT 'エラーメッセージ（失敗時）',
    
    processing_time DECIMAL(8,3) UNSIGNED DEFAULT NULL 
        COMMENT '処理時間（秒）',
    
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        COMMENT 'ログ記録日時',
    
    -- インデックス定義
    PRIMARY KEY (log_id, created_at), -- パーティション対応のため複合主キー
    KEY idx_user (user_type, user_id),
    KEY idx_target (target_type, target_id),
    KEY idx_action (action),
    KEY idx_ip_address (ip_address),
    KEY idx_log_type_date (log_type, created_at),
    KEY idx_success (success)
    
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci 
  COMMENT='アクティビティログテーブル'

-- 月次パーティション（過去1年分 + 将来分）
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202309 VALUES LESS THAN (202310),
    PARTITION p202310 VALUES LESS THAN (202311),
    PARTITION p202311 VALUES LESS THAN (202312),
    PARTITION p202312 VALUES LESS THAN (202401),
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404),
    PARTITION p202404 VALUES LESS THAN (202405),
    PARTITION p202405 VALUES LESS THAN (202406),
    PARTITION p202406 VALUES LESS THAN (202407),
    PARTITION p202407 VALUES LESS THAN (202408),
    PARTITION p202408 VALUES LESS THAN (202409),
    PARTITION p202409 VALUES LESS THAN (202410),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- ===========================================
-- phpMyAdmin設定用のクエリ例
-- ===========================================

-- テーブル構造の確認
DESCRIBE products;
SHOW FULL COLUMNS FROM customers;

-- インデックス確認
SHOW INDEX FROM orders;

-- 外部キー制約確認
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME,
    DELETE_RULE,
    UPDATE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- テーブルサイズ確認
SELECT 
    TABLE_NAME,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size_MB',
    TABLE_ROWS,
    AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- パーティション情報確認
SELECT 
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND PARTITION_NAME IS NOT NULL;

-- ===========================================
-- サンプルデータ挿入例
-- ===========================================

-- 商品データ挿入
INSERT INTO products (
    product_code, product_name, price, stock_quantity, 
    description, status, is_featured
) VALUES 
('PRD001', 'ワイヤレスイヤホン Pro', 15800.00, 50, 
 '高音質Bluetooth 5.0対応ワイヤレスイヤホン', 'active', TRUE),
('PRD002', 'スマートウォッチ Basic', 8900.00, 30,
 '健康管理機能付きスマートウォッチ', 'active', FALSE),
('PRD003', 'モバイルバッテリー 10000mAh', 2980.00, 100,
 '大容量モバイルバッテリー、急速充電対応', 'active', TRUE);

-- 顧客データ挿入
INSERT INTO customers (
    customer_no, email, password_hash, first_name, last_name,
    phone, postal_code, prefecture, city, address1
) VALUES 
('C000001', 'tanaka@example.com', '$2y$10$example_hash_here', 
 '太郎', '田中', '03-1234-5678', '100-0001', '東京都', '千代田区', '千代田1-1-1'),
('C000002', 'sato@example.com', '$2y$10$another_hash_here',
 '花子', '佐藤', '06-9876-5432', '540-0008', '大阪府', '大阪市中央区', '大手前1-1-1');

-- 注文データ挿入
INSERT INTO orders (
    order_no, customer_id, subtotal, tax_amount, total_amount,
    shipping_last_name, shipping_first_name, shipping_postal_code,
    shipping_prefecture, shipping_city, shipping_address1
) VALUES 
('ORD20240901001', 1, 15800.00, 1580.00, 17380.00,
 '田中', '太郎', '100-0001', '東京都', '千代田区', '千代田1-1-1'),
('ORD20240901002', 2, 11880.00, 1188.00, 13068.00,
 '佐藤', '花子', '540-0008', '大阪府', '大阪市中央区', '大手前1-1-1');

-- 注文明細データ挿入
INSERT INTO order_items (order_id, product_id, product_code, product_name, unit_price, quantity)
VALUES 
(1, 1, 'PRD001', 'ワイヤレスイヤホン Pro', 15800.00, 1),
(2, 2, 'PRD002', 'スマートウォッチ Basic', 8900.00, 1),
(2, 3, 'PRD003', 'モバイルバッテリー 10000mAh', 2980.00, 1);

スポンサーリンク

5.phpMyAdminでの効率的な作業手順

1. データベース作成手順

  1. phpMyAdmin画面左側の「新規作成」をクリック
  2. データベース名を入力（例：sample_company）
  3. 照合順序で「utf8mb4_unicode_ci」を選択
  4. 「作成」ボタンをクリック

2. テーブル作成の推奨手順

  1. SQL方式（推奨）:
    • 「SQL」タブを選択
    • 上記のCREATE TABLE文をコピー＆ペースト
    • 「実行」をクリック
  2. GUI方式:
    • 「構造」タブ → 「テーブル作成」
    • カラム数を指定してカラムを一つずつ設定

3. カラム設定時のチェックポイント

必須確認項目:

  • 名前: スネークケース、予約語避ける
  • 型: 用途に応じた適切な型選択
  • 長さ: 将来性を考慮した余裕のある設定
  • デフォルト値: NULL許可の場合は適切なデフォルト設定
  • 照合順序: 日本語ならutf8mb4_unicode_ci
  • NULL許可: 必須項目はNOT NULL
  • インデックス: 検索頻度の高いカラムに設定
  • AUTO_INCREMENT: 主キーに設定
  • コメント: 用途・制約を明記

4. パフォーマンス向上のための設定

インデックス戦略:

-- 単一カラムインデックス（検索頻度高）
KEY idx_email (email)

-- 複合インデックス（範囲検索+ソート）
KEY idx_status_date (status, created_at)

-- ユニークインデックス（重複防止）
UNIQUE KEY uk_product_code (product_code)

-- 全文検索インデックス（テキスト検索）
FULLTEXT KEY ft_search (product_name, description)

5. phpMyAdmin特有の便利機能

表示・入力の最適化:

  • メディア型: 画像カラムにIMAGE設定
  • 入力変換: 日付入力にDATEFORMAT
  • ブラウザ表示変換: URLにLINK表示
  • 仮想カラム: 計算結果の自動表示

これらの設定により、phpMyAdminでのデータ管理が格段に効率化されます。

特にECサイトなどの実用的なシステムでは、
適切なテーブル設計により運用面での利便性が大幅に向上する。

スポンサーリンク

6.最後に

いつも、当サイトをご閲覧いただきありがとうございます。

今後、MySQLの記事を増やしていこうと思いますので、
ぜひ、チェックしてみてください。

スポンサーリンク

コメント

タイトルとURLをコピーしました