Day7 後半のゴール
「“自分のSQLiteテーブル”をMySQL流に設計し直す手順を、具体的にイメージできるようにする」
前半では、例として users / orders を取り上げて、
SQLite版 → MySQL版 への書き換えをやりました。
後半のゴールはこうです。
実際にINSERT / SELECTして、制約がどう効くかをイメージできる
「SQLiteでこう書いてたら、MySQLではこう直す」という変換パターンを持てる
自分のSQLiteプロジェクトをMySQLに持っていくときの手順を説明できる
ここからは、さっき作った MySQL版テーブルを“動かしてみる”イメージで進めます。
MySQL版 users / orders にデータを入れてみる
「AUTO_INCREMENT と DATETIME と FOREIGN KEY が一気に動く」
前半で作った 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;
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;
ここに、実際にデータを入れてみるイメージを持ちます。
users にINSERTする
INSERT INTO users (name, email, created_at)
VALUES
('山田太郎', 'taro@example.com', NOW()),
('佐藤花子', 'hanako@example.com', NOW());
ここで起きていることはこうです。
id は書いていないが、AUTO_INCREMENT で 1,2 が自動採番される
created_at は DATETIME として「今の時刻」が入る
name / email は NOT NULL なので、空だとエラーになる
SQLiteのときは「なんとなくTEXTで入っていた」ものが、
MySQLでは「型と必須制約が効いている」状態になっています。
orders にINSERTする
INSERT INTO orders (user_id, total, ordered_at)
VALUES
(1, 5000, NOW()),
(2, 3000, NOW());
ここでは、
user_id=1,2 は users.id に存在している
→ FOREIGN KEY 的にもOK
ということで、普通に通ります。
この時点で、
users … 2件
orders … 2件（それぞれ user_id=1,2）
という、きれいな親子関係ができています。
FOREIGN KEY を“わざと踏み抜いて”みる
「SQLiteでは通っていた“変なデータ”を、MySQLが拒否する感覚」
次に、あえておかしなINSERTをしてみるイメージを持ちます。
存在しない user_id で注文を作る
INSERT INTO orders (user_id, total, ordered_at)
VALUES (999, 1000, NOW());
users テーブルには id=999 のユーザーはいません。
SQLiteでFOREIGN KEYを使っていなかった場合、
こういう行も普通に入ってしまっていたはずです。
MySQL＋InnoDB＋FOREIGN KEY では、ここでエラーになります。
「fk_orders_user の外部キー制約に違反している」
という趣旨のエラーが返ってくるイメージです。
ここが、SQLiteからMySQLに来たときの一番大きな差です。
アプリがバグっていても、
DBが「そのIDのユーザーはいないからダメ」と言ってくれる。
これが、データの信頼性を一気に上げてくれます。
親ユーザーを削除しようとしたときの挙動
「“親を消すときにどうするか”も設計の一部になる」
次に、users からユーザーを消そうとしてみます。
デフォルト挙動（RESTRICT相当）のイメージ
今の定義では、ON DELETE を何も書いていないので、
「子がいる親は消せない」挙動になります。
DELETE FROM users
WHERE id = 1;
このとき、orders には user_id=1 の注文が残っています。
FOREIGN KEY が効いていれば、ここでもエラーになります。
「まだこのユーザーを参照している注文があるから、削除禁止」
というわけです。
SQLiteでは、
users からだけ消してしまって、
orders に“親のいない注文”が残ることがよくありました。
MySQLでは、
「親を消すなら、子をどうするか」を設計で決める必要があります。
ON DELETE CASCADE を付けた場合
もし「ユーザーを消したら、その人の注文も全部消してよい」という仕様なら、
FOREIGN KEY をこう書き換えます。
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE
この場合、
DELETE FROM users WHERE id = 1;
を実行すると、
users.id=1 の行が消える
それに紐づく orders.user_id=1 の行も自動で消える
という挙動になります。
ここで大事なのは、
SQLiteでは「アプリ側のif文」でやっていたようなことを、
MySQLでは「FOREIGN KEYの設定」でDBに任せられる
という発想の転換です。
SQLiteテーブルをMySQLに持っていくときの“思考の順番”
「単なる文法変換ではなく、“設計のアップグレード”として考える」
ここまでの例を踏まえて、
自分のSQLiteテーブルをMySQLに移すときの考え方を整理します。
1. まず“意味”を読み解く
SQLiteのCREATE TABLEを見て、こう自問します。
どのカラムが“ID”として使われているか
どのカラムが“必須”のつもりだったか
どのカラムが“他テーブルのID”を指しているか
日時はどのカラムか
SQLiteでは全部INTEGER / TEXTでも、
頭の中では「これはID」「これは必須」「これは日時」と思っていたはずです。
その“つもり”を言語化します。
2. MySQLの型・制約に落とし込む
次に、それをMySQLの機能にマッピングします。
ID → INT AUTO_INCREMENT PRIMARY KEY
必須 → NOT NULL
他テーブルのID → FOREIGN KEY
日時 → DATETIME（＋INSERT時にNOW()）
文字列 → VARCHAR(長さ)
この段階で、
「動くスキーマ」から「守れるスキーマ」に変わり始めます。
3. テーブル間の関係を明示する
SQLiteでは「user_id INTEGER」のように、
“なんとなく”関係を表現していた部分を、
MySQLでは必ず FOREIGN KEY にします。
FOREIGN KEY (user_id) REFERENCES users(id)
これで、
存在しないIDを参照するデータ
親が消えたのに子だけ残るデータ
をDBが拒否してくれるようになります。
インデックスをどこまで考えるか（軽くおさらい）
「よく使うクエリを1つ思い浮かべて、それに合わせて張る」
Day6までの内容も、ここで少しだけ使います。
例えば、orders に対してよく書くクエリがこれだとします。
特定ユーザーの注文履歴を、新しい順に10件だけ欲しい
SELECT *
FROM orders
WHERE user_id = 1
ORDER BY ordered_at DESC
LIMIT 10;
この場合、(user_id, ordered_at) の複合インデックスが候補になります。
CREATE INDEX idx_orders_user_date
ON orders (user_id, ordered_at);
Day7の時点では、
「よく書くクエリを1つ思い浮かべて、それに合うインデックスを1つ考える」
くらいで十分です。
全部を一気に最適化しようとせず、
「よく使うところから順に」という感覚を持っておくと、
現実的な設計になります。
セキュリティ・品質の観点から見た“再構築”の意味
「SQLiteで“なんとなく許されていた甘さ”を、MySQLで締め直す」
ここまでの流れを、少し高い視点からまとめると、
PRIMARY KEY をちゃんと決める
→ 意味のある値を主キーにしないことで、漏えい時のリスクを下げる
FOREIGN KEY を張る
→ テーブル間の整合性をDBが保証してくれる
→ アプリのバグや不正リクエストからデータを守れる
型とNOT NULLをきちんと付ける
→ ありえない値・空の値が入りにくくなる
→ 後からの集計・分析がやりやすくなる
インデックスを考える
→ ログや履歴を長く残しても、実用的な速度を保てる
→ 「遅いから消す」という雑な運用を避けられる
つまり、
SQLiteで“とりあえず動いていたDB”を、
MySQLで“長く運用に耐えるDB”に育て直す
というのが、Day7の復習課題の本質です。
Day7 後半のまとめ
MySQL版の users / orders に実際にINSERTしてみると、INT AUTO_INCREMENT PRIMARY KEY による自動採番、DATETIME＋NOW() による時刻の自動格納、NOT NULL による必須チェックが同時に働き、SQLiteでTEXTやNULLを許していた頃よりも「データの型と必須性」がはっきり守られる。
orders.user_id に対して FOREIGN KEY (user_id) REFERENCES users(id) を張ることで、「存在しないユーザーIDで注文を作ろうとするとエラー」「注文が残っているユーザーを削除しようとするとエラー（またはON DELETE CASCADEで連動削除）」といった“整合性のガード”がDBレベルで働き、SQLiteで起きがちだった“親のいない子レコード”を防げる。
SQLiteのCREATE TABLEをMySQLに持っていくときは、まず「どのカラムがID／必須／他テーブル参照／日時か」という“意味”を読み解き、それを AUTO_INCREMENT PRIMARY KEY・NOT NULL・FOREIGN KEY・DATETIME・VARCHAR などの機能に落とし込むことで、「動くスキーマ」から「守れるスキーマ」へと設計をアップグレードしていく。
さらに、よく使うクエリ（例：WHERE user_id = ? ORDER BY ordered_at DESC LIMIT ?）を1つ思い浮かべ、そのクエリに合わせて (user_id, ordered_at) のような複合インデックスを検討することで、性能面でも“とりあえず”から一歩進んだ設計ができるようになり、結果としてセキュリティ・品質・運用性のすべてが底上げされる。
ここまでイメージできていれば、
「自分のSQLiteプロジェクトをMySQLに移すとき、どこをどう変えるか」を
かなり具体的に語れる状態になっています。
