MySQL 固有の文法と運用

テクノロジ系／ db (PDO / SQL / マルチDB)

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 でも同じ結果になる書き方を学ぶ)

考えてみよう

既定では正解・不正解の判定はしません。自分のペースで「答えを見る」を。採点してほしい場合だけ「採点してほしい」を押してください。

ドリル 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 を必ず付ける (順序を担保するため)

採点

php scripts/grade.php topics/db/ch11-mysql-specific/drill/01-auto-increment/

採点ランナーは tests/setup.sql (driver に応じて setup.mysql.sql / setup.pgsql.sql) を流し込み、items テーブルを空で用意してから採点する。

学ぶこと

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

ドリル 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 もこの順で返す)

採点

php scripts/grade.php topics/db/ch11-mysql-specific/drill/02-show-tables/

学ぶこと

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

ドリル 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 つ・改行で終わる点に注意

採点

php scripts/grade.php topics/db/ch11-mysql-specific/drill/03-truncate-vs-delete/

採点ランナーは driver に応じて setup.sql / setup.mysql.sql / setup.pgsql.sql を使い分け、items テーブルに 3 件入った状態から採点を始める。

学ぶこと (本質)

TRUNCATE は DDL 扱い — 速いが ROLLBACK できない
DELETE FROM は DML 扱い — トランザクションで取り消せる
件数だけ見ると差は出ないが、直後の INSERT で振られる ID に差が出る:

- TRUNCATE 後: 1 から振り直し

- DELETE 後: 続きから (例: 元が 1,2,3 なら次は 4)

本番のデータは原則 DELETE FROM ... WHERE ...。TRUNCATE は テスト DB のリセット で使う