SQLだけでは無理だと思っていたMySQLのSELECTで使えるテクニック
皆様、いかがお過ごしであろうか？ 恐らく今年最大規模の案件がやっと終わりそうで一息ついている野地である。期間中、呼吸回数と書いたコードの文字数、どちらが多いか分かったものではない。俺のDBサーバーが火を噴いている。
今回の案件では複数サーバーの連携やRedisの使用等、初挑戦した技術が沢山あったので大変だった半面かなり楽しかったのだが、中でも個人的に嬉しかったのが、ある程度は使えるようになったと勘違いしていたMySQLに関する知見をさらに一段階深くすることができたことである。
一般的にSQLにおけるCRUD処理の内、開発に割く時間の割合は
CREATE: 10%
READ: 80%
UPDATE: 8%
DELETE: 2%
と圧倒的にREAD(SELECT)なのだが、今回はそのREAD処理の内、これは特徴的だなと思った4つのテクニックを紹介したいと思う。
Web系の業務であればかなりのシェアがあるであろうMySQLのこれらテクニックを、是非皆さんの開発に役立ててほしい。
- UNIX_TIMESTAMP/NOWで時間比較を操る
- CASE/THEN/ELSE/ENDでデフォルト値を設定したり、分類を大雑把にする
- INNER JOIN/CEIL/RAND/MAXでランダムなレコードを取得
- GROUP BY/HAVING/COUNTで従属するレコードを○個持っているレコードに限定する
- まとめ
UNIX_TIMESTAMP/NOWで時間比較を操る
大抵の場合、日時を扱うレコードはDATETIME型のフィールドにデータを格納しておくものだが、複雑な絞り込みや算出をする場合はUNIX_TIMESTANP関数を使うと便利だ。
reg_timeという登録日時を格納するDATETIME型のuserテーブルがあったとしよう。
SELECT *FROM userWHERE UNIX_TIMESTAMP(reg_time) >= (UNIX_TIMESTAMP(NOW()) - 600)
タイムスタンプに変換した後は秒による比較が可能になるので、このようにすると現在時刻から5分以内に登録されたユーザーを取得できる。
もちろんMySQLスタイルのDATETIME文字列(2018-01-18 10:09:57 等)をアプリケーション側で用意してSQLに組み込んでもいいが、そのような文字列を柔軟に用意するのが面倒なケースではこのようなDBだけで完結する方法を用いるのもアリだろう。
さらに、このuserテーブルに最終ログイン時刻を格納するlogin_timeというDATETIME型のフィールドがあった場合は、
SELECT UNIX_TIMESTAMP(login_time) - UNIX_TIMESTAMP(reg_time) AS timeFROM user
とするとユーザーのサービス利用時間が秒で取得できる。
CASE/THEN/ELSE/ENDでデフォルト値を設定したり、分類を大雑把にする
一般的なプログラミング言語とは多少使いどころが違うが、SQLに用意されている条件分岐もうまく使えば有用なケースがある。
画像パスをpathというVARCHAR型のフィールドに保存しているmediaというテーブルがあったとしよう。
さらに、サムネイルとしてthumbにmediaテーブルのid(オートインクリメント設定のPRIMARYフィールド想定)を格納しているpostテーブルを考える。
SELECT id, path,FROM postLEFT JOIN mediaON post.thumb = media.id
とすればサムネイルとして画像パスを取得できるが、mediaテーブルの該当レコードが削除されていたり、post.thumbかmedia.idのどちらかがNULLを許容していた場合はpathにNULLが返ってきてしまう。
大抵はアプリケーション側のコードで対応可能かとは思われるが、場合によっては必ずpathに画像パスが入っていて欲しいこともあるだろう。
そんなときに、SQL側でNULLをデフォルトの画像パスに置き換える例が以下だ。
SELECTid,CASE WHEN post.thumb IS NULLTHEN 'http://noji.dev/hoge/fuga/piyo.jpg'ELSE post.thumb END AS pathFROM postLEFT JOIN mediaON post.thumb = media.id
こうしておけばpathがNUNLだった場合はデフォルトの画像パスとしてhttp://noji.dev/hoge/fuga/piyo.jpgが返るようになる。
さらに別のケースを考えてみよう。都道府県のデータとして名前をnameというVARCHAR型のフィールド、北海道 = 1,関東 = 4といった分類をareaというINT型のフィールドに格納しているprefectureというテーブルがあったとする。
仮にareaおける北海道が1、東北が2、北陸が3、関東が4だったとすると、
SELECT nameFROM prefectureWHERE area > 5ORDER BY area DESC
とすれば4つのエリアに属する都道府県のみがareaによってソートされて出力される。一覧画面などではこのSQLのDESC部分をASCとトグルさせる機能を作るケースも多いだろう。
さて、追加機能として「東北と北陸とそれ以外」でソートする機能を作る場合はどうだろうか。
一見、北海道と関東は1と4で数値が離れてしまっているため、SQLの力では簡単にソートできないように思われる。
SELECTname,CASE areaWHEN 2 THEN 2WHEN 3 THEN 3ELSE 0 END AS areaFROM prefectureWHERE area > 5ORDER BY area DESC
しかし、このようにareaが2か3以外のレコードを全て0扱いにしてしまえばアプリケーション側で並び替えせずともDB側で並び替えが完結する。
INNER JOIN/CEIL/RAND/MAXでランダムなレコードを取得
レコード数が少ない場合はそこまで苦労しないが、巨大なデータを抱えるテーブルからランダムなレコードを取得するという処理は意外と時間がかかる。
だが、以下のSQLを使えば比較的低負荷でランダムなレコードを取得できるだろう。
オートインクリメント設定のPRIMARY KEYとしてidフィールドを持つproductテーブルを例に考えてみる。
SELECT product.*FROM productINNER JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM product)) AS rand_id) AS tmpON product.id = tmp.rand_id
RAND関数は0から1の間からランダムな浮動小数点値を返すので、それをproductテーブル内で最大のidに掛けてCEIL関数で切り上げてやればランダムなIDが取得できる。
それら処理で取得したランダムなIDで内部結合してやればランダムなレコードが一件取得できるのだ。
なお、これはidに歯抜けが無いテーブルでのみ使えるテクニックだ。物理削除される可能性があるテーブルでは使えないので注意。
GROUP BY/HAVING/COUNTで従属するレコードを○個持っているレコードに限定する
従属テーブルを含めた絞り込みが単発のテーブルの絞り込みより面倒なのは周知の事実だが、中々に面倒なのが「直属の部下を3人持つ社員」といったような絞り込み条件だ。
一筋縄ではいかなそうな絞り込み条件だが、一般的なWHERE句ではなくHAVING句を使用することでこの要件は達成できる。
例として、社員番号としてオートインクリメント設定のPRIMARY KEYであるidフィールド、上司のidを格納するbossフィールドを持つemployeeテーブルを考えてみよう。
SELECT employee.*FROM employeeLEFT JOIN employee AS subordinateON employee.id = subordinate.bossGROUP BY employee.idHAVING COUNT(subordinate.id) = 3
このようにすることで直属の部下をちょうど3人持つ社員のみを取得できる。
HAVING COUNT(subordinate.id) = 3の=を不等号に変えてやれば範囲指定も可能だし、勿論COUNTだけでなくSUMやMAX、MIN等も使用できるので従属テーブルの複雑な絞り込みを行うときはHAVINGの存在を思い出して欲しい。
まとめ
サーバーサイドのプログラミングを学んだ当初はSQLでできることの知識が少なく、結合すらも知らなかった頃はPHPの力でゴリゴリのループを回していたこともあったが、勉強すればするほどSQLはパワフルな言語だと気づかされるし、魅力的だと感じるものだ。
ただ、アプリケーション側での処理に任せた方が良いケースも沢山あるのも最近学んだことの一つである。
速さは正義であり、良いサービスの第一条件であるわけだが、それによって複雑怪奇なSQLを書くと後で修正できなくなるなんていうのは恐ろしいことだ。
今後のエンジニア人生、速度と修正コスト、実装の手間や実行環境の互換性等、色んな要素のバランス感覚を身に着けたいと思う今日この頃である。
Comments
-
The 188bet apk is the only way to go mobile. 188betzokb offers apk link, easy to download! Download it here: 188bet apk.
-
Heard some folks talking about s777casino. Gave it a go, and it’s not bad! Got a decent selection of games to keep you entertained. Worth a look if you’re bored. Find it here: s777casino
-
When Todd enters a courtroom, the exhibits sit up straight.
-
Последние новинки музыки 2026 скачать бесплатно https://shorturl.fm/cCQW9
Warning: Undefined variable $aria_req in /var/www/html/noji.dev/wp-content/themes/ODD_CODES/comments.php on line 15
Warning: Undefined variable $aria_req in /var/www/html/noji.dev/wp-content/themes/ODD_CODES/comments.php on line 17
[…] SQLだけでは無理だと思っていたMySQLのSELECTで使えるテクニック […]