5:23 AM投稿記事の長さ:天声人語 × 12.8個 くらい

SQLだけでは無理だと思っていたMySQLのSELECTで使えるテクニック

サムネイル画像

皆様、いかがお過ごしであろうか? 恐らく今年最大規模の案件がやっと終わりそうで一息ついている野地である。期間中、呼吸回数と書いたコードの文字数、どちらが多いか分かったものではない。俺のDBサーバーが火を噴いている。

今回の案件では複数サーバーの連携やRedisの使用等、初挑戦した技術が沢山あったので大変だった半面かなり楽しかったのだが、中でも個人的に嬉しかったのが、ある程度は使えるようになったと勘違いしていたMySQLに関する知見をさらに一段階深くすることができたことである。

一般的にSQLにおけるCRUD処理の内、開発に割く時間の割合は

CREATE: 10%
READ: 80%
UPDATE: 8%
DELETE: 2%

と圧倒的にREAD(SELECT)なのだが、今回はそのREAD処理の内、これは特徴的だなと思った4つのテクニックを紹介したいと思う。

Web系の業務であればかなりのシェアがあるであろうMySQLのこれらテクニックを、是非皆さんの開発に役立ててほしい。

  1. UNIX_TIMESTAMP/NOWで時間比較を操る
  2. CASE/THEN/ELSE/ENDでデフォルト値を設定したり、分類を大雑把にする
  3. INNER JOIN/CEIL/RAND/MAXでランダムなレコードを取得
  4. GROUP BY/HAVING/COUNTで従属するレコードを○個持っているレコードに限定する
  5. まとめ

UNIX_TIMESTAMP/NOWで時間比較を操る

大抵の場合、日時を扱うレコードはDATETIME型のフィールドにデータを格納しておくものだが、複雑な絞り込みや算出をする場合はUNIX_TIMESTANP関数を使うと便利だ。

reg_timeという登録日時を格納するDATETIME型のuserテーブルがあったとしよう。

    SELECT * 
    FROM user 
    WHERE 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 time 
    FROM user

とするとユーザーのサービス利用時間が秒で取得できる。

CASE/THEN/ELSE/ENDでデフォルト値を設定したり、分類を大雑把にする

一般的なプログラミング言語とは多少使いどころが違うが、SQLに用意されている条件分岐もうまく使えば有用なケースがある。

画像パスをpathというVARCHAR型のフィールドに保存しているmediaというテーブルがあったとしよう。
さらに、サムネイルとしてthumbmediaテーブルのid(オートインクリメント設定のPRIMARYフィールド想定)を格納しているpostテーブルを考える。

    SELECT id, path, 
    FROM post 
    LEFT JOIN media 
    ON post.thumb = media.id

とすればサムネイルとして画像パスを取得できるが、mediaテーブルの該当レコードが削除されていたり、post.thumbmedia.idのどちらかがNULLを許容していた場合はpathNULLが返ってきてしまう。

大抵はアプリケーション側のコードで対応可能かとは思われるが、場合によっては必ずpathに画像パスが入っていて欲しいこともあるだろう。

そんなときに、SQL側でNULLをデフォルトの画像パスに置き換える例が以下だ。

    SELECT 
    id, 
    CASE WHEN post.thumb IS NULL 
      THEN 'http://noji.wpblog.jp/hoge/fuga/piyo.jpg' 
      ELSE post.thumb END AS path 
    FROM post 
    LEFT JOIN media 
    ON post.thumb = media.id

こうしておけばpathNUNLだった場合はデフォルトの画像パスとしてhttp://noji.wpblog.jp/hoge/fuga/piyo.jpgが返るようになる。

さらに別のケースを考えてみよう。都道府県のデータとして名前をnameというVARCHAR型のフィールド、北海道 = 1,関東 = 4といった分類をareaというINT型のフィールドに格納しているprefectureというテーブルがあったとする。

仮にareaおける北海道が1、東北が2、北陸が3、関東が4だったとすると、

    SELECT name
    FROM prefecture 
    WHERE area > 5 
    ORDER BY area DESC

とすれば4つのエリアに属する都道府県のみがareaによってソートされて出力される。一覧画面などではこのSQLのDESC部分をASCとトグルさせる機能を作るケースも多いだろう。

さて、追加機能として「東北と北陸とそれ以外」でソートする機能を作る場合はどうだろうか。
一見、北海道と関東は1と4で数値が離れてしまっているため、SQLの力では簡単にソートできないように思われる。

    SELECT 
    name, 
    CASE area 
      WHEN 2 THEN 2 
      WHEN 3 THEN 3 
      ELSE 0 END AS area 
    FROM prefecture 
    WHERE area > 5 
    ORDER BY area DESC

しかし、このようにareaが2か3以外のレコードを全て0扱いにしてしまえばアプリケーション側で並び替えせずともDB側で並び替えが完結する。

INNER JOIN/CEIL/RAND/MAXでランダムなレコードを取得

レコード数が少ない場合はそこまで苦労しないが、巨大なデータを抱えるテーブルからランダムなレコードを取得するという処理は意外と時間がかかる。
だが、以下のSQLを使えば比較的低負荷でランダムなレコードを取得できるだろう。

オートインクリメント設定のPRIMARY KEYとしてidフィールドを持つproductテーブルを例に考えてみる。

    SELECT product.* 
    FROM product 
    INNER JOIN ( 
      SELECT CEIL(RAND() * (SELECT MAX(id) FROM product)) AS rand_id 
    ) AS tmp 
    ON 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 employee 
    LEFT JOIN employee AS subordinate 
    ON employee.id = subordinate.boss 
    GROUP BY employee.id 
    HAVING COUNT(subordinate.id) = 3

このようにすることで直属の部下をちょうど3人持つ社員のみを取得できる。
HAVING COUNT(subordinate.id) = 3=を不等号に変えてやれば範囲指定も可能だし、勿論COUNTだけでなくSUMMAXMIN等も使用できるので従属テーブルの複雑な絞り込みを行うときはHAVINGの存在を思い出して欲しい。

まとめ

サーバーサイドのプログラミングを学んだ当初はSQLでできることの知識が少なく、結合すらも知らなかった頃はPHPの力でゴリゴリのループを回していたこともあったが、勉強すればするほどSQLはパワフルな言語だと気づかされるし、魅力的だと感じるものだ。

ただ、アプリケーション側での処理に任せた方が良いケースも沢山あるのも最近学んだことの一つである。

速さは正義であり、良いサービスの第一条件であるわけだが、それによって複雑怪奇なSQLを書くと後で修正できなくなるなんていうのは恐ろしいことだ。

今後のエンジニア人生、速度と修正コスト、実装の手間や実行環境の互換性等、色んな要素のバランス感覚を身に着けたいと思う今日この頃である。

コメントを付ける

入力エリアすべてが必須項目です。

内容をよくご確認の上、送信してください