MySQLで任意の順序でソートしたい場合

はじめに

今回はMySQLを例にして書きますが、PostgreSQLやOracleでも同様のことはできるはずです。

テーブルに持たせる

頻繁にソートを使用する場合、そもそもソート順をテーブルに持たせるのが一般的だと思います。

SELECT * FROM item ORDER BY item.sort_order;

しかし「そこまでするほどでもない」「集計で使いたいだけ」といった場合もあるかと思うので、SQL文の中で順序を決定する方法を紹介します。

CASE文を使う

CASE文で優先順位を決めてあげれば簡単に実現することができます。

SELECT * from item ORDER BY
CASE WHEN item.size = 'SS' THEN 0 
CASE WHEN item.size = 'S'  THEN 1 
CASE WHEN item.size = 'M'  THEN 2
CASE WHEN item.size = 'L'  THEN 3 
CASE WHEN item.size = 'LL' THEN 4 
CASE WHEN item.size = 'XL' THEN 5 
END

しかし、このように値が増えると読みづらくなってしまうのが難点です。順番を変えたくなった時もソート順を1から振り直すのが多少面倒に思えます。

LOCATE()関数を使う

もっと簡潔に記述する方法として、LOCATE()関数を使う方法があります。文字列が見つかった最初のオフセットを返してくれるので、検索文字列の出現順にソートすることができます。しかし下記の例はうまくいきません。

SELECT * from item
 ORDER BY LOCATE(item.size, 'SS S M L LL XL');

文字列が部分一致してしまう場合は要注意

上記の例だと、キーワードの長さがそれぞれ違うため、Sを検索した場合、最初にSSの1文字目に一致してしまい、思った結果が得られないという問題があります。

デリミタを使う

まず思いつく解決策はデリミタを前後につけることです。

SELECT * from item
 ORDER BY LOCATE(CONCAT(' ', item.size, ' '), ' SS S M L LL XL ');
-- 検索対象文字列の前後にもスペースが必要

パディングする

あるいは、この現象はitem.sizeの文字列長が違うことで起こるため、LPAD()などでソートキーの長さを揃えてしまうことでも解決できます。

SELECT * from item
 ORDER BY LOCATE(LPAD(item.size, 2, '_'), 'SS _S _M _L LL XL');

他によい方法があったら教えて下さい。