# ch11 — MySQL 固有の文法と運用 Source: https://php-school.pages.dev/topics/db/ch11-mysql-specific/ Site: AIと学ぶPHPの学校 (https://php-school.pages.dev) Lesson: データベース連携 Level: L4 Reference (official): https://www.php.net/manual/ja/book.pdo.php ## 本文 # ch11 — MySQL 固有の文法と運用 ## 概要 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 ` / `SHOW CREATE TABLE
` でスキーマを確認できる - MySQL のデータ型 (`TINYINT` / `INT` / `BIGINT` / `VARCHAR(n)` / `TEXT` / `DATETIME` / `TIMESTAMP`) の使い分けが言える - `TRUNCATE` と `DELETE` の違い (TRUNCATE は `AUTO_INCREMENT` もリセット) が言える - `ENGINE=InnoDB` の意味 (トランザクション・外部キーが使える) が言える ## 所要時間 スライド 6 分 + ドリル 3 問 = **約 1.5 時間** ## 前提 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` の挙動を比較 (件数の前後を観察) | ## 採点 DB について 採点ランナー (`scripts/grade.php`) は環境変数 `DOJO_DB_DRIVER` で接続先を切り替える。 - 未指定 / `sqlite` → 一時 SQLite ファイル (Docker 不要・既定) - `mysql` → `docker compose` の MySQL コンテナ (3306) - `pgsql` → `docker compose` の PostgreSQL コンテナ (5432) 各 drill には `tests/setup.sql` (SQLite 用 / 既定) のほかに、`tests/setup.mysql.sql` / `tests/setup.pgsql.sql` を置いている。採点ランナーは driver を見て自動で使い分ける。 MySQL 固有の章なので、本来は `DOJO_DB_DRIVER=mysql` で動かしてほしい。デフォルトの SQLite でも同じ標準出力になるように、各 drill は driver による分岐 (または DB に依らない結果の取り出し) を使っている。 ## ハマりどころ - `utf8` (古い・最大 3-byte) と `utf8mb4` (新しい・絵文字 OK) は別物。新規テーブルは必ず `utf8mb4` を指定する。 - `AUTO_INCREMENT` は `INT` 以上で使う。`TINYINT` (最大 127) で採番すると 128 件目で詰まる。 - `TRUNCATE` はトランザクションでロールバックできない (DDL 扱い)。本番では `DELETE FROM ... WHERE ...` を選ぶことが多い。 - `DATETIME` はサーバ TZ に依らない固定値、`TIMESTAMP` は UTC 保存 + 表示時 TZ 変換。用途で使い分ける。 ## スライド --- marp: true theme: dojo paginate: true size: 16:9 --- # MySQL 固有の文法と運用 Lesson 11 / Chapter 11 --- ## なぜ「MySQL 固有」を 1 章使うか - PDO で SQL は **書ける** が、CREATE TABLE は DB ごとに違う - 現場で一番多いのは MySQL (とその互換 MariaDB / Aurora) - 「とりあえず動く CREATE TABLE」が書けるようにする → ch10 までは「PDO 共通の書き方」、ここは「MySQL の方言」 --- ## AUTO_INCREMENT — 連番 ID を自動生成 ```sql 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 を必ず指定する ```sql 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 ```sql 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 の違い ```sql 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 の意味 ```sql CREATE TABLE ... ENGINE=InnoDB; ``` - MySQL は **複数のストレージエンジン** を選べる - 現代は `InnoDB` が事実上の標準 (MySQL 5.5+ の default) - InnoDB だけが提供する機能: - **トランザクション** (BEGIN / COMMIT / ROLLBACK) - **外部キー制約** (FOREIGN KEY) - 行ロック (同時更新に強い) - 古い MyISAM は使わない (上記が全部使えない) → 迷ったら `ENGINE=InnoDB` を付ける。新規 CREATE TABLE では明示する習慣。 --- ## ハマりどころ — utf8 vs utf8mb4 ```sql -- ✗ 古い・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 でも同じ結果になる書き方を学ぶ)