SQL 深掘り– category –
本線の第5段階の続き。JOIN・集計・ウィンドウ関数・実行計画を、データを入れて確かめる。
-
SQL 深掘り
GROUP BYと集計関数 — 付けたら行が減った
GROUP BY を付けた瞬間にエラーが出る、行が減る、売上ゼロの部署が消える。原因はほぼ全部「集計とは行を潰すことだ」という一点に戻ります。練習用テーブルを自分で作って、COUNT(*) と COUNT(列) の差、AVG が NULL を無視すること、ONLY_FULL_GROUP_BY が黙って外れていると嘘の値が返ること、LEFT JOIN で0件を出す方法、WITH ROLLUP の小計、条件付き集計での横持ちまでを、実際に打って確かめます。 -
SQL 深掘り
JOINの仕組み — 表を繋いでいない
JOIN は「2つの表を横に繋ぐ命令」だと説明されがちですが、それでは LEFT JOIN が突然 INNER JOIN に化ける理由を説明できません。この記事では練習用の小さな表を2つ作り、まず CROSS JOIN で全組み合わせ20行を目で見ます。INNER JOIN がその20行を条件で絞ったものだと分かると、LEFT JOIN も自己結合も3表結合も、同じ1つの仕組みの言い換えになります。WHERE に書くか ON に書くかで結果が変わる件、RIGHT JOIN を使わなくていい理由、MySQL に FULL OUTER JOIN が無い件、そして20万行で EXPLAIN を読むところまで。 -
SQL 深掘り
SELECTの実行順序とNULL — WHEREで別名が使えない
SELECT * FROM t; は書けるのに、なぜその順で返るのかを説明できない。この記事はそこを埋めます。まず練習用の社員テーブルを60行、NULL入りで作ります。そのうえで「SQLは書いた順に実行されない」ことを、WHEREで列の別名を使ってエラーを出すという形で目に見せます。次にNULLを掘ります。= NULL が何も返さないこと、NOT IN にNULLが1つ混ざるだけで結果が全部消えること。最後にORDER BYの同値、OFFSETが遅くなる理由とキーセットページネーション、DISTINCTとGROUP BYの違いまで。 -
SQL 深掘り
ウィンドウ関数 — GROUP BYだと名前が消える
「各部署で一番売った人」を出そうとすると、GROUP BY では金額しか出せません。名前が消えるからです。集計は行を潰しますが、ウィンドウ関数は潰しません。同じ SUM を GROUP BY と OVER () の両方で打って行数を比べるところから始めて、ROW_NUMBER と RANK と DENSE_RANK の違いを同点データで3列並べ、LAG で前月比を出し、累計でフレームの既定が RANGE であるという罠を踏み、WHERE でウィンドウ関数が使えない理由まで行きます。MySQL 8.0 以降が必要です。 -
SQL 深掘り
NOT NULL・UNIQUE・CHECK制約 — アプリで弾いた値が入る
入力チェックはアプリに書いてある、だから変な値は入らない。そう思っていた表を SELECT したら、名前が空文字の会員と、数量がマイナスの注文と、同じメールアドレスが2件入っていました。アプリは1つとは限らず、手でSQLを打つ日も来ます。最後に値を止められるのはデータベースだけです。この記事では制約を1つも付けない表にわざとゴミを4種類入れて見せたあと、NOT NULL・UNIQUE・主キー・CHECK を順に足していきます。UNIQUE なのに NULL が何個でも入ること、CHECK に NULL が混ざると真偽が決まらず通ってしまうこと、INSERT IGNORE が他のエラーまで握り潰すこと、制約に名前を付けないとエラーメッセージから原因にたどり着けないこと。MySQL 8.0.16 以降が必要です。 -
SQL 深掘り
DDL(CREATE・ALTER・DROP) — ALTER TABLEは取り消せない
SELECT を間違えても誰も困りませんが、ALTER TABLE は本番を止めます。この記事は「表を作る・変える・消す」だけを1本かけて掘ります。最初にやるのは、DDL が暗黙にコミットされる ―― つまり ROLLBACK が効かない ―― ことの実演です。そのうえで CREATE TABLE が実際に何を作ったかを SHOW CREATE TABLE で確かめ、LIKE と SELECT のコピーの差を見て、山場の ALTER TABLE へ入ります。ALGORITHM が INSTANT なのか COPY なのかを自分で調べる方法、80万行の表で COPY が走ったときに書き込みが何秒待たされるかの実測、INSTANT でも表が止まるメタデータロック、DROP と TRUNCATE と DELETE の違い、RENAME による無停止の入れ替え、一時テーブル、JSON に索引を張る生成列まで。 -
SQL 深掘り
DML(INSERT・UPDATE・DELETE) — WHERE無しのUPDATEは戻せない
WHERE を書き忘れた UPDATE を打って、30人全員の等級が同じ値になりました。ROLLBACK と打っても戻りません。autocommit が有効だったからです。この記事は「書き換える文を打つ前に必ずやる4手」を体に入れるところから始めます。わざと間違えた UPDATE を打って ROLLBACK で助かる体験と、助からない体験の両方をやります。そのうえで ON DUPLICATE KEY UPDATE と REPLACE の違い、LAST_INSERT_ID が複数行では先頭を返すこと、更新対象の表をサブクエリで読めない MySQL の制限、外部キーで DELETE が拒否される場合と連鎖する場合、そして論理削除が必ず連れてくる手間まで掘ります。 -
SQL 深掘り
MySQLのデータ型選び — FLOATで金額は1円ずれる
型を選ぶのは「入るか入らないか」の話ではありません。間違えると、エラーも出さずに値そのものが変わります。この記事では、郵便番号の先頭の0が消えるところから始めて、TINYINTに128を入れてOut of rangeを出し、DOUBLEで0.1+0.2が0.3にならないことをSQLで実演し、DECIMALに直します。CHARとVARCHARで後ろの空白の扱いが違うこと、TEXTには索引が張れないこと、ENUMが数値として比較されること。そして山場は暗黙の型変換で、WHERE 文字列列 = 1 と書くと索引が丸ごと効かなくなるのをEXPLAINで見ます。最後にJSON型の索引の張り方、NOT NULLを既定にする理由、ALTER TABLE で型を変えるときにテーブルがロックされる話まで。 -
SQL 深掘り
リレーショナルモデルと集合演算 — SQLは「表」を扱っていない
16本かけてSQLを打ってきました。その全部が何の上に載っていたのかを、最後に言語化します。リレーショナルモデルが言う「関係」は表ではなく集合で、行にも列にも順序はなく、重複行も存在しません。ところがSQLはその通りではありません。同じ行が3つ入ることも、ORDER BY を書かないと順序が決まらないことも、実際にSQLで確かめます。そのうえで集合演算(UNION / UNION ALL / INTERSECT / EXCEPT)を打ち、MySQL 8.0.31 より古い環境での書き換えも用意します。なぜ表を分けるのか、なぜSQLは「どう取るか」を書かないのか、そして「関係」を捨てたNoSQLが何を手に入れて何を失ったのか。SQL深掘りシリーズの最終回です。 -
SQL 深掘り
GRANTと最小権限 — rootで繋ぐと全部持っていかれる
アプリの .env に書いてあるDBのユーザー名は何ですか。root なら、アプリに1つ穴が開いた瞬間に、全部のデータベースが読まれて消せます。この記事はDB側だけでできる守りに絞ります。いまの権限を SELECT user, host と SHOW GRANTS で棚卸ししたあと、アプリ用・マイグレーション用・読み取り専用の3つを最小権限で作り直し、実際にそのユーザーで繋いで DROP TABLE が拒否されるところまで確かめます。列単位の GRANT SELECT と、行を絞るビュー。'app'@'localhost' と 'app'@'%' の差。bind-address と ss -tlnp で3306が外に出ていないかの確認、SSHトンネル、REQUIRE SSL。パスワードがコマンド履歴に残る話、general_log を本番で使わない理由、バックアップ用ユーザーに必要な最小権限、そして最後に10項目の点検チェックリスト。パスワードはすべてダミーです。
12