ch11 12章中 11章目

ch11 — MySQL 固有の文法と運用

この章を AI と学ぶ

💬 AI に聞く質問例 3 件

「ch11 — MySQL 固有の文法と運用」のつまずきやすいポイントを、初学者向けに3つ教えてください

ChatGPT Claude Gemini
「ch11 — MySQL 固有の文法と運用」について、自分の手で試せる練習問題を3つ出してください (難易度を上げながら)

ChatGPT Claude Gemini
「ch11 — MySQL 固有の文法と運用」を学んだ後、次に学ぶと良いトピックは何ですか？理由も教えてください

ChatGPT Claude Gemini

概要

ch01〜ch10 では PDO で「3 DB 共通の世界」を歩いてきた。この章からは 個別の DB に降りていく。MySQL は WordPress / Laravel など PHP 圏で最も採用例が多く、RDS / Cloud SQL でもデフォルト級で業務では避けて通れない。

MySQL 固有で最初に出会うのが AUTO_INCREMENT。id INT AUTO_INCREMENT PRIMARY KEY で INSERT 時に自動で連番 ID を振る。文字コードは utf8mb4 を必ず指定する (古い utf8 は最大 3 byte で絵文字や一部漢字が壊れる)。ENGINE=InnoDB を選べばトランザクションと外部キー制約が使える。

スキーマ確認は SHOW TABLES / DESCRIBE / SHOW CREATE TABLE のように SHOW 系の独自構文。データ型は TINYINT INT BIGINT VARCHAR(n) TEXT DATETIME TIMESTAMP が定番で、特に TIMESTAMP は UTC 保存・取り出し時に TZ 変換 されるので注意。TRUNCATE は DELETE と違って AUTO_INCREMENT をリセットしロールバック不可なので使い分けを意識する。

学習目標

このチャプターが終わると、次のことが言える / 書ける。

AUTO_INCREMENT で連番 ID を自動生成できる (SQLite の AUTOINCREMENT / PostgreSQL の SERIAL との違いを言える)
utf8mb4 を使う理由 (絵文字を含む 4-byte UTF-8 対応) を言える
SHOW TABLES / DESCRIBE <table> / SHOW CREATE TABLE <table> でスキーマを確認できる
MySQL のデータ型 (TINYINT / INT / BIGINT / VARCHAR(n) / TEXT / DATETIME / TIMESTAMP) の使い分けが言える
TRUNCATE と DELETE の違い (TRUNCATE は AUTO_INCREMENT もリセット) が言える
ENGINE=InnoDB の意味 (トランザクション・外部キーが使える) が言える

前提

db トピック ch01〜ch10 を理解していること (PDO 接続 / SELECT / INSERT / UPDATE / DELETE / prepare / execute / トランザクション / マルチ DB 対応まで)。

ドリル

no	内容
01	`AUTO_INCREMENT` を持つテーブルに 3 件 INSERT して自動採番 ID を確認
02	`SHOW TABLES` 相当の操作でテーブル一覧を出力 (driver 別分岐)
03	`TRUNCATE` と `DELETE` の挙動を比較 (件数の前後を観察)

ハマりどころ

utf8 (古い・最大 3-byte) と utf8mb4 (新しい・絵文字 OK) は別物。新規テーブルは必ず utf8mb4 を指定する。
AUTO_INCREMENT は INT 以上で使う。TINYINT (最大 127) で採番すると 128 件目で詰まる。
TRUNCATE はトランザクションでロールバックできない (DDL 扱い)。本番では DELETE FROM ... WHERE ... を選ぶことが多い。
DATETIME はサーバ TZ に依らない固定値、TIMESTAMP は UTC 保存 + 表示時 TZ 変換。用途で使い分ける。

解説スライド全 10 枚

右の › ボタンか → キー(またはスワイプ)で次のスライドに進めます。下の「縦表示」を開くと全スライドを一気に読めます。

MySQL 固有の文法と運用

Lesson 11 / Chapter 11
なぜ「MySQL 固有」を 1 章使うか
- PDO で SQL は 書ける が、CREATE TABLE は DB ごとに違う
- 現場で一番多いのは MySQL (とその互換 MariaDB / Aurora)
- 「とりあえず動く CREATE TABLE」が書けるようにする
→ ch10 までは「PDO 共通の書き方」、ここは「MySQL の方言」
AUTO_INCREMENT — 連番 ID を自動生成
```
CREATE TABLE items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
- id を指定せずに INSERT すると 1, 2, 3, ... と自動採番
- 取得は $pdo->lastInsertId() (ch05 と同じ)
- DB ごとの書き方:
DB 書き方

MySQL INT PRIMARY KEY AUTO_INCREMENT

SQLite INTEGER PRIMARY KEY AUTOINCREMENT

PostgreSQL SERIAL PRIMARY KEY (または GENERATED ... AS IDENTITY)
utf8mb4 を必ず指定する
```
CREATE TABLE posts (...) DEFAULT CHARSET=utf8mb4;
```
- MySQL の utf8 は 最大 3-byte までしか入らない(歴史的な妥協)
- 4-byte 文字 (絵文字 / 一部の漢字) を入れるとエラー or 文字化け
- utf8mb4 (mb4 = max bytes 4) なら絵文字 OK
- 接続側も mysql:...;charset=utf8mb4 を忘れない (ch10 で見た DSN)

SHOW TABLES / DESCRIBE / SHOW CREATE TABLE

SHOW TABLES;                    -- DB 内のテーブル一覧
DESCRIBE users;                 -- users の列定義
SHOW CREATE TABLE users;        -- users の CREATE TABLE 文を復元

これは MySQL 専用の 管理コマンド(他 DB では別の書き方)
他 DB の対応:

目的	MySQL	SQLite	PostgreSQL
テーブル一覧	`SHOW TABLES`	`SELECT name FROM sqlite_master WHERE type='table'`	`SELECT tablename FROM pg_tables`
列定義	`DESCRIBE t`	`PRAGMA table_info(t)`	`\d t` (psql コマンド)

TRUNCATE と DELETE の違い

DELETE FROM items;          -- 1 件ずつ消す。AUTO_INCREMENT はそのまま
TRUNCATE TABLE items;       -- テーブルを「作り直す」感覚。AUTO_INCREMENT は 1 に戻る

観点	DELETE	TRUNCATE
速度	遅い (件数比例)	速い (一定時間)
WHERE	使える	使えない (全件)
ロールバック	できる	できない (DDL 扱い)
AUTO_INCREMENT	リセットされない	1 から振り直し
トリガ	発火する	発火しない

→ 本番は基本 DELETE。テスト DB のリセット用に TRUNCATE。

MySQL のデータ型 — まず覚える 7 つ

型	用途	例
`TINYINT`	0/1 フラグや 0-255 の小さい数	is_active
`INT`	一般的な整数 (-21 億〜 +21 億)	id, age
`BIGINT`	21 億を超えるカウンタ	view_count
`VARCHAR(n)`	上限ありの可変長文字列	name VARCHAR(255)
`TEXT`	長文 (最大 65,535 byte)	body
`DATETIME`	TZ を持たない日時	created_at
`TIMESTAMP`	UTC 保存 + 表示時に TZ 変換	updated_at

→ 迷ったら INT / VARCHAR(255) / DATETIME の 3 種で大半済む。

ENGINE=InnoDB の意味
```
CREATE TABLE ... ENGINE=InnoDB;
```
- MySQL は 複数のストレージエンジン を選べる
- 現代は InnoDB が事実上の標準 (MySQL 5.5+ の default)
- InnoDB だけが提供する機能:
  - トランザクション (BEGIN / COMMIT / ROLLBACK)
  - 外部キー制約 (FOREIGN KEY)
  - 行ロック (同時更新に強い)
- 古い MyISAM は使わない (上記が全部使えない)
→ 迷ったら ENGINE=InnoDB を付ける。新規 CREATE TABLE では明示する習慣。
ハマりどころ — utf8 vs utf8mb4
```
-- ✗ 古い・3-byte まで・絵文字でエラーになる
CREATE TABLE t (...) DEFAULT CHARSET=utf8;

-- ✓ 新しい・4-byte OK・絵文字も入る
CREATE TABLE t (...) DEFAULT CHARSET=utf8mb4;
```
- 既存テーブルの変換は ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 で可能
- 接続側も DSN に charset=utf8mb4 を必ず付ける
- 「絵文字を入れたらエラーになる」報告は 99% これ
このチャプターでできるようになること

✅ AUTO_INCREMENT で連番 ID テーブルを作れる ✅ utf8mb4 を指定する理由を説明できる ✅ SHOW TABLES / DESCRIBE でスキーマを確認できる ✅ TRUNCATE と DELETE を使い分けられる ✅ INT / VARCHAR / DATETIME の使い分けが言える ✅ ENGINE=InnoDB の意味と必要性が言える

→ ドリルへ (デフォルトの SQLite で動かしつつ、MySQL でも同じ結果になる書き方を学ぶ)

1 / 10

📜 スライドを全部一気に読む(縦表示)

Lesson 11 / Chapter 11

なぜ「MySQL 固有」を 1 章使うか

PDO で SQL は 書ける が、CREATE TABLE は DB ごとに違う
現場で一番多いのは MySQL (とその互換 MariaDB / Aurora)
「とりあえず動く CREATE TABLE」が書けるようにする

→ ch10 までは「PDO 共通の書き方」、ここは「MySQL の方言」

AUTO_INCREMENT — 連番 ID を自動生成

CREATE TABLE items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

id を指定せずに INSERT すると 1, 2, 3, ... と自動採番
取得は $pdo->lastInsertId() (ch05 と同じ)
DB ごとの書き方:

DB	書き方
MySQL	`INT PRIMARY KEY AUTO_INCREMENT`
SQLite	`INTEGER PRIMARY KEY AUTOINCREMENT`
PostgreSQL	`SERIAL PRIMARY KEY` (または `GENERATED ... AS IDENTITY`)

utf8mb4 を必ず指定する

CREATE TABLE posts (...) DEFAULT CHARSET=utf8mb4;

MySQL の utf8 は 最大 3-byte までしか入らない(歴史的な妥協)
4-byte 文字 (絵文字 / 一部の漢字) を入れるとエラー or 文字化け
utf8mb4 (mb4 = max bytes 4) なら絵文字 OK
接続側も mysql:...;charset=utf8mb4 を忘れない (ch10 で見た DSN)

SHOW TABLES / DESCRIBE / SHOW CREATE TABLE

SHOW TABLES;                    -- DB 内のテーブル一覧
DESCRIBE users;                 -- users の列定義
SHOW CREATE TABLE users;        -- users の CREATE TABLE 文を復元

これは MySQL 専用の 管理コマンド(他 DB では別の書き方)
他 DB の対応:

目的	MySQL	SQLite	PostgreSQL
テーブル一覧	`SHOW TABLES`	`SELECT name FROM sqlite_master WHERE type='table'`	`SELECT tablename FROM pg_tables`
列定義	`DESCRIBE t`	`PRAGMA table_info(t)`	`\d t` (psql コマンド)

TRUNCATE と DELETE の違い

DELETE FROM items;          -- 1 件ずつ消す。AUTO_INCREMENT はそのまま
TRUNCATE TABLE items;       -- テーブルを「作り直す」感覚。AUTO_INCREMENT は 1 に戻る

観点	DELETE	TRUNCATE
速度	遅い (件数比例)	速い (一定時間)
WHERE	使える	使えない (全件)
ロールバック	できる	できない (DDL 扱い)
AUTO_INCREMENT	リセットされない	1 から振り直し
トリガ	発火する	発火しない

→ 本番は基本 DELETE。テスト DB のリセット用に TRUNCATE。

MySQL のデータ型 — まず覚える 7 つ

型	用途	例
`TINYINT`	0/1 フラグや 0-255 の小さい数	is_active
`INT`	一般的な整数 (-21 億〜 +21 億)	id, age
`BIGINT`	21 億を超えるカウンタ	view_count
`VARCHAR(n)`	上限ありの可変長文字列	name VARCHAR(255)
`TEXT`	長文 (最大 65,535 byte)	body
`DATETIME`	TZ を持たない日時	created_at
`TIMESTAMP`	UTC 保存 + 表示時に TZ 変換	updated_at

→ 迷ったら INT / VARCHAR(255) / DATETIME の 3 種で大半済む。

ENGINE=InnoDB の意味

CREATE TABLE ... ENGINE=InnoDB;

MySQL は 複数のストレージエンジン を選べる
現代は InnoDB が事実上の標準 (MySQL 5.5+ の default)
InnoDB だけが提供する機能:
- トランザクション (BEGIN / COMMIT / ROLLBACK)
- 外部キー制約 (FOREIGN KEY)
- 行ロック (同時更新に強い)
古い MyISAM は使わない (上記が全部使えない)

→ 迷ったら ENGINE=InnoDB を付ける。新規 CREATE TABLE では明示する習慣。

ハマりどころ — utf8 vs utf8mb4

-- ✗ 古い・3-byte まで・絵文字でエラーになる
CREATE TABLE t (...) DEFAULT CHARSET=utf8;

-- ✓ 新しい・4-byte OK・絵文字も入る
CREATE TABLE t (...) DEFAULT CHARSET=utf8mb4;

既存テーブルの変換は ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 で可能
接続側も DSN に charset=utf8mb4 を必ず付ける
「絵文字を入れたらエラーになる」報告は 99% これ

このチャプターでできるようになること

✅ AUTO_INCREMENT で連番 ID テーブルを作れる ✅ utf8mb4 を指定する理由を説明できる ✅ SHOW TABLES / DESCRIBE でスキーマを確認できる ✅ TRUNCATE と DELETE を使い分けられる ✅ INT / VARCHAR / DATETIME の使い分けが言える ✅ ENGINE=InnoDB の意味と必要性が言える

→ ドリルへ (デフォルトの SQLite で動かしつつ、MySQL でも同じ結果になる書き方を学ぶ)

この章で取り組む演習問題の流れ (3ステップ)

演習問題の詳細

この章の演習問題の内容を読めます。実際に手元で解くには教材リポジトリを clone してください。

ドリル 01 — AUTO_INCREMENT で連番 ID を自動採番する

問題

items テーブル (採点ランナーが空で用意) に対して、PDO の prepared statement で次の 3 件を順番に INSERT する。

りんご
みかん
ぶどう

INSERT 後に SELECT id, name FROM items ORDER BY id で取り出し、{id}: {name} の形式で 1 行ずつ出力する。

期待される出力

1: りんご
2: みかん
3: ぶどう

ヒント

scripts/shared/db-connect.php の dojo_db_connect() を使うと driver を意識せず接続できる
INSERT INTO items (name) VALUES (?) を 1 本だけ prepare() し、execute() を 3 回呼ぶ
id は採点ランナーが用意したテーブルが SQLite なら AUTOINCREMENT、MySQL なら AUTO_INCREMENT、PostgreSQL なら SERIAL で勝手に振られる
取得時のソートは ORDER BY id を必ず付ける (順序を担保するため)

学ぶこと

MySQL の AUTO_INCREMENT、SQLite の AUTOINCREMENT、PostgreSQL の SERIAL は 文法は違うが結果は同じ
PDO 側からは「id を指定せず INSERT すれば勝手に振られる」だけを知っていれば良い

🧪 ブラウザで試す

スライドのコードを写経して ▶ 実行、自分で書いて 📝 採点 で答え合わせ。初回のみ PHP ランタイム読込で数秒かかります。

<?php

require __DIR__ . '/../../../../../scripts/shared/db-connect.php';

$pdo = dojo_db_connect();

// TODO:
// 1) prepare("INSERT INTO items (name) VALUES (?)") を 1 本作る
// 2) execute() を 3 回呼んで 'りんご' / 'みかん' / 'ぶどう' を順に INSERT
// 3) SELECT id, name FROM items ORDER BY id で全件取り出す
// 4) 各行を "{id}: {name}\n" の形式で出力する

PHP ランタイム未読込

ドリル 02 — テーブル一覧を取得する (driver 別の SHOW TABLES 相当)

問題

採点ランナーが users と posts の 2 テーブルを用意する。これらのテーブル名を アルファベット順 で 1 行ずつ出力する。

DB ごとにテーブル一覧の取り方が違うので、DOJO_DB_DRIVER (sqlite / mysql / pgsql) で分岐する。

driver	取り方
sqlite	`SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name`
mysql	`SHOW TABLES`
pgsql	`SELECT tablename FROM pg_tables WHERE schemaname = current_schema() ORDER BY tablename`

期待される出力

posts
users

ヒント

getenv('DOJO_DB_DRIVER') ?: 'sqlite' で driver 取得
PHP 8 の match 式で driver ごとに SQL を切り替えると見通しが良い
SHOW TABLES は 1 列だけ返るので FETCH_NUM で [0] を取り出すと簡単
MySQL/PostgreSQL は連想配列のキー名が DB によって変わる (e.g. Tables_in_<dbname>) ので、列インデックス取得が無難
アルファベット順なので posts → users の順 (採点ランナーは MySQL の SHOW TABLES もこの順で返す)

学ぶこと

「DB 内のテーブル一覧」は PDO で標準化されていない。DB ごとに違うクエリを叩く必要がある
逆に言えば、PDO のレイヤを抜けると DB 固有のメタデータ操作にすぐ手が届く

🧪 ブラウザで試す

スライドのコードを写経して ▶ 実行、自分で書いて 📝 採点 で答え合わせ。初回のみ PHP ランタイム読込で数秒かかります。

<?php

require __DIR__ . '/../../../../../scripts/shared/db-connect.php';

$pdo = dojo_db_connect();
$driver = getenv('DOJO_DB_DRIVER') ?: 'sqlite';

// TODO:
// 1) $driver の値で SQL を分岐:
//    sqlite -> SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name
//    mysql  -> SHOW TABLES
//    pgsql  -> SELECT tablename FROM pg_tables WHERE schemaname = current_schema() ORDER BY tablename
// 2) クエリを実行し、テーブル名の配列を作る
// 3) 1 行ずつ echo して出力 (アルファベット順)

PHP ランタイム未読込

ドリル 03 — TRUNCATE と DELETE の挙動を比較する

問題

採点ランナーが items テーブルに 3 件のレコードを用意する。

SELECT COUNT(*) FROM items で件数を取得し、before: {N} を出力
全件削除する
- MySQL なら TRUNCATE TABLE items
- SQLite / PostgreSQL は DELETE FROM items (SQLite に TRUNCATE は無い・PostgreSQL は TRUNCATE もあるがここでは DELETE で統一)
削除後にもう一度 SELECT COUNT(*) FROM items で件数を取り、after delete: {N} を出力

期待される出力

before: 3
after delete: 0

ヒント

scripts/shared/db-connect.php の dojo_db_connect() を使う
件数取得は (int) $pdo->query("SELECT COUNT(*) FROM items")->fetchColumn() で 1 行
driver は getenv('DOJO_DB_DRIVER') ?: 'sqlite'
文字列なので before: 3 の : の後ろにスペース 1 つ・改行で終わる点に注意

学ぶこと (本質)

TRUNCATE は DDL 扱い — 速いが ROLLBACK できない
DELETE FROM は DML 扱い — トランザクションで取り消せる
件数だけ見ると差は出ないが、直後の INSERT で振られる ID に差が出る:
- TRUNCATE 後: 1 から振り直し
- DELETE 後: 続きから (例: 元が 1,2,3 なら次は 4)
本番のデータは原則 DELETE FROM ... WHERE ...。TRUNCATE は テスト DB のリセット で使う

🧪 ブラウザで試す

スライドのコードを写経して ▶ 実行、自分で書いて 📝 採点 で答え合わせ。初回のみ PHP ランタイム読込で数秒かかります。

<?php

require __DIR__ . '/../../../../../scripts/shared/db-connect.php';

$pdo = dojo_db_connect();
$driver = getenv('DOJO_DB_DRIVER') ?: 'sqlite';

// TODO:
// 1) SELECT COUNT(*) FROM items で件数を取り、"before: {N}" を出力
// 2) driver が 'mysql' なら TRUNCATE TABLE items、それ以外 (sqlite / pgsql) は DELETE FROM items を実行
// 3) もう一度 COUNT(*) を取り、"after delete: {N}" を出力
//
// 期待される出力:
//   before: 3
//   after delete: 0

PHP ランタイム未読込

演習問題(3問)

ドリル 01 — AUTO_INCREMENT で連番 ID を自動採番する

README.md starter.php answer.php
ドリル 02 — テーブル一覧を取得する (driver 別の SHOW TABLES 相当)

README.md starter.php answer.php
ドリル 03 — TRUNCATE と DELETE の挙動を比較する

README.md starter.php answer.php

サイト内で問題文・雛形・解答例を確認できます。実際に手元で解くには教材リポジトリを clone してください。

完了にすると進捗に反映されます

ch11 — MySQL 固有の文法と運用

🤖 この章を AI と学ぶ

概要

学習目標

前提

ドリル

ハマりどころ

🎞 解説スライド 全 10 枚

MySQL 固有の文法と運用

なぜ「MySQL 固有」を 1 章使うか

AUTO_INCREMENT — 連番 ID を自動生成

utf8mb4 を必ず指定する

SHOW TABLES / DESCRIBE / SHOW CREATE TABLE

TRUNCATE と DELETE の違い

MySQL のデータ型 — まず覚える 7 つ

ENGINE=InnoDB の意味

ハマりどころ — utf8 vs utf8mb4

このチャプターでできるようになること

なぜ「MySQL 固有」を 1 章使うか

AUTO_INCREMENT — 連番 ID を自動生成

utf8mb4 を必ず指定する

SHOW TABLES / DESCRIBE / SHOW CREATE TABLE

TRUNCATE と DELETE の違い

MySQL のデータ型 — まず覚える 7 つ

ENGINE=InnoDB の意味

ハマりどころ — utf8 vs utf8mb4

このチャプターでできるようになること

📚 演習問題の詳細

ドリル 01 — AUTO_INCREMENT で連番 ID を自動採番する

問題

期待される出力

ヒント

学ぶこと

🧪 ブラウザで試す

ドリル 02 — テーブル一覧を取得する (driver 別の SHOW TABLES 相当)

問題

期待される出力

ヒント

学ぶこと

🧪 ブラウザで試す

ドリル 03 — TRUNCATE と DELETE の挙動を比較する

問題

期待される出力

ヒント

学ぶこと (本質)

🧪 ブラウザで試す

📝 演習問題(3問)

ドリル 01 — AUTO_INCREMENT で連番 ID を自動採番する

ドリル 02 — テーブル一覧を取得する (driver 別の SHOW TABLES 相当)

ドリル 03 — TRUNCATE と DELETE の挙動を比較する

🔗 この章をシェア

この章を AI と学ぶ

解説スライド全 10 枚

演習問題の詳細

演習問題(3問)

この章をシェア