MySQL | SQLite経験者向け、30日で習得するMySQL：差分理解 － Day7 復習課題

SQL MySQL
スポンサーリンク

Day7 前半のゴール

「“SQLiteで書けたCREATE TABLE”を、MySQL流に書き直せるようになる」

Day7 は、ここまで学んだ差分を一気に使う「総復習の入り口」です。
テーマはシンプルで、

SQLiteで作ったテーブル定義を
MySQLらしいテーブル定義に“設計し直す”

ことです。

ただの書き換え作業ではなく、

データ型をちゃんと選ぶ
PRIMARY KEY を見直す
FOREIGN KEY をちゃんと張る
文字コード・照合順序を意識する
必要ならインデックスも考える

という「設計のリライト」をやっていきます。
前半では、具体例を1つ決めて「どこをどう変えるか」を丁寧に分解します。

まず題材を決める

「よくある“EC風”のSQLiteテーブルをスタート地点にする」

例として、SQLiteでこんなテーブルを作っていたとします。

CREATE TABLE users (
  id        INTEGER PRIMARY KEY,
  name      TEXT,
  email     TEXT,
  created_at TEXT
);

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER,
  total      INTEGER,
  ordered_at TEXT
);
SQL

ありがちなポイントがいくつかあります。

PRIMARY KEY は INTEGER PRIMARY KEY（SQLite流の自動採番）
文字列は全部 TEXT
日時も TEXT
FOREIGN KEY は張っていない（user_id は“なんとなく”整数）

SQLite ではこれでも普通に動きます。
でも MySQL に持っていくなら、ここを一段“ちゃんとした設計”に変えていきます。

ステップ1：文字コードとデータベースの前提を決める

「MySQLでは“最初に土台を決める”ところから始まる」

SQLite では「ファイルを作ったらすぐCREATE TABLE」でよかったですが、
MySQL ではまず「データベース（スキーマ）」を作るところから始まります。

ここで、文字コードと照合順序を決めます。

CREATE DATABASE shop_app
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_general_ci;
SQL

これで、

このデータベース内で新しく作るテーブルは、
何も指定しなければ utf8mb4 ＋ utf8mb4_general_ci になる

という状態になります。

Day4 でやったとおり、

utf8mb4 を選ぶことで絵文字なども含めて安全に扱える
collation を決めることで比較・ソートのルールが安定する

という「土台」が整います。
SQLiteではあまり意識しなかった部分ですが、MySQLではここが重要です。

ステップ2：users テーブルを MySQL流に書き直す

「型・AUTO_INCREMENT・NOT NULL を意識して設計する」

SQLite版の users はこうでした。

CREATE TABLE users (
  id        INTEGER PRIMARY KEY,
  name      TEXT,
  email     TEXT,
  created_at TEXT
);
SQL

これを MySQL で「ちゃんと設計する」と、こうなります。

CREATE TABLE users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(50)   NOT NULL,
  email      VARCHAR(255)  NOT NULL,
  created_at DATETIME      NOT NULL
) ENGINE=InnoDB;
SQL

1行ずつ、何を意図して変えているかを見ていきます。

id INT AUTO_INCREMENT PRIMARY KEY

SQLite の INTEGER PRIMARY KEY は、
「そのカラムがROWIDになる」という特殊な意味を持っていました。

MySQL では、

INT AUTO_INCREMENT PRIMARY KEY

と書くことで、

整数型のID
自動採番
主キー（行を一意に識別する代表）

を明示します。

ここで大事なのは、

メールアドレスなど“意味のある値”を主キーにせず、
内部用の連番IDをPRIMARY KEYにしている

という設計方針です。
これはセキュリティと運用の両方で効いてきます。

name / email を VARCHAR にする

SQLite では TEXT でしたが、MySQL では VARCHAR を使います。

name → VARCHAR(50)
email → VARCHAR(255)

「だいたいこのくらいの長さだろう」という現実を反映させています。
さらに NOT NULL を付けて、

名前もメールも必須項目

というルールをDBに持たせています。

アプリ側のバリデーションに加えて、
DB側でも「空のままは許さない」と言えるようになります。

created_at を DATETIME にする

SQLite では TEXT で日時を入れていたかもしれませんが、
MySQL では DATETIME を使います。

DATETIME にしておくと、

期間で絞り込む
日付だけ取り出す
日別集計をする

といった操作が自然に書けます。

Day2・Day3でやったように、
INSERT 時には NOW() を使うのが定番です。

ステップ3：orders テーブルを MySQL流に書き直す

「FOREIGN KEY を“ちゃんと張る”のがSQLiteとの一番の差」

SQLite版の orders はこうでした。

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER,
  total      INTEGER,
  ordered_at TEXT
);
SQL

MySQL版は、こう書き直します。

CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT          NOT NULL,
  total      INT          NOT NULL,
  ordered_at DATETIME     NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
) ENGINE=InnoDB;
SQL

ここでのポイントは2つです。

user_id を NOT NULL ＋ FOREIGN KEY にする

SQLite版では、user_id はただの INTEGER でした。
MySQL版では、

INT NOT NULL
FOREIGN KEY (user_id) REFERENCES users(id)

とすることで、

必ず何かしらのユーザーに紐づく
そのユーザーは users テーブルに実在していなければならない

という2つのルールをDBに持たせています。

これにより、

存在しない user_id で注文を作ろうとするとエラー
ユーザーを消そうとしたとき、注文が残っていればエラー

という「整合性のガード」が働きます。
SQLiteではアプリ側で頑張っていた部分を、
MySQL＋InnoDBではDBに任せられるようになります。

ordered_at を DATETIME にする

users と同じく、日時は DATETIME にします。

これで、

特定期間の注文だけ集計する
日別売上を出す

といったクエリが書きやすくなります。

ステップ4：インデックスをどこまで考えるか

「Day6までの知識を“軽く”乗せておく」

Day7 前半では深追いしませんが、
「どこにインデックスを張る候補があるか」だけ意識しておきます。

users
PRIMARY KEY(id) に自動インデックス
email でよく検索するなら、email にインデックスを張る候補

orders
PRIMARY KEY(id) に自動インデックス
user_id で絞るクエリが多いなら、user_id にインデックス
user_id ＋ ordered_at で複合インデックス、という選択肢もあり

この「どこが検索条件に使われるか」を考える癖が、
MySQLでのテーブル再設計ではとても大事です。

後半では、
実際にどういうクエリを想定してインデックスを張るか、
もう一歩踏み込んで考えていきます。

SQLite版とMySQL版を並べて見る

「“動くスキーマ”から“守れるスキーマ”への進化」

ここまでの変化を、ざっくり並べてみます。

SQLite版 users

CREATE TABLE users (
  id        INTEGER PRIMARY KEY,
  name      TEXT,
  email     TEXT,
  created_at TEXT
);
SQL

MySQL版 users

CREATE TABLE users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(50)   NOT NULL,
  email      VARCHAR(255)  NOT NULL,
  created_at DATETIME      NOT NULL
) ENGINE=InnoDB;
SQL

SQLite版 orders

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER,
  total      INTEGER,
  ordered_at TEXT
);
SQL

MySQL版 orders

CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT          NOT NULL,
  total      INT          NOT NULL,
  ordered_at DATETIME     NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
) ENGINE=InnoDB;
SQL

見た目は少し長くなりましたが、その分だけ、

型がはっきりした
必須項目が明確になった
テーブル間の関係がDBに守られるようになった

という「守りの強さ」が上がっています。

Day7 前半のまとめ

Day7 の復習課題は、「SQLiteで動いていたテーブル定義」を、そのままコピペするのではなく、MySQL流に“設計し直す”ことがテーマになる。
具体的には、データベース作成時に DEFAULT CHARACTER SET utf8mb4 と DEFAULT COLLATE を決め、各テーブルでは INT AUTO_INCREMENT PRIMARY KEY・VARCHAR・DATETIME・ENGINE=InnoDB を意識して書き直すことで、「型が曖昧なTEXTだらけのスキーマ」から卒業する。
users / orders のような親子関係のあるテーブルでは、SQLiteでただのINTEGERだった user_id を、MySQLでは NOT NULL ＋ FOREIGN KEY (user_id) REFERENCES users(id) にすることで、「存在しないユーザーに紐づく注文」や「親が消えたのに子だけ残る」といった不整合をDBレベルで防げるようになる。
インデックスについても、PRIMARY KEYに自動で付くものに加えて、「email で検索するなら email に」「user_id で絞って ordered_at で並べるなら複合インデックス」といった形で、“どのクエリを速くしたいか”を意識した設計に一歩踏み出すことが、SQLiteからMySQLへの本当の差分になる。

後半では、
この再構築したテーブルに対して実際にINSERT / SELECTを流しながら、
FOREIGN KEYがどう効くか、インデックスをどう考えるか、
そして「自分のSQLiteプロジェクトをどうMySQLに持っていくか」の手順を、
もう一段具体的に整理していきます。

次へ
12
スポンサーリンク
タイトルとURLをコピーしました