概要

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 対応まで)。

ドリル

ハマりどころ

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

問題

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

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

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 すれば勝手に振られる」だけを知っていれば良い

問題

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

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

期待される出力

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 固有のメタデータ操作にすぐ手が届く

問題

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

1. SELECT COUNT(*) FROM items で件数を取得し、before: {N} を出力
2. 全件削除する
   • MySQL なら TRUNCATE TABLE items
   • SQLite / PostgreSQL は DELETE FROM items (SQLite に TRUNCATE は無い・PostgreSQL は TRUNCATE もあるが ここでは DELETE で統一)
3. 削除後にもう一度 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 のリセット で使う