MySQLでVARCHAR型フィールドを数値順にソートする方法

MySQLでVARCHAR型を数値としてソートするには？

MySQLでVARCHAR型（文字列型）のフィールドに格納された数値を正しくソートするには、LPAD(lower(カラム名)) 関数を使用します。文字列として保存された数値は、そのまま ORDER BY で並べ替えると「1」「199」「78」「9」「99」「999」のように文字コード順になってしまい、意図した結果になりません。本記事では、LPAD関数を使って数値順にソートする具体的手順を解説します。

1. テーブルを作成する

まず、CREATE文を使ってサンプル用のテーブルを作成します。

mysql> create table SortingvarcharDemo
   -> (
   -> List varchar(10)
   -> );
Query OK, 0 rows affected (0.82 sec)

2. レコードを挿入する

続いて、INSERT文でテストデータを挿入します。

mysql> insert into SortingvarcharDemo values("99");
Query OK, 1 row affected (0.12 sec)

mysql> insert into SortingvarcharDemo values("9");
Query OK, 1 row affected (0.17 sec)

mysql> insert into SortingvarcharDemo values("199");
Query OK, 1 row affected (0.19 sec)

mysql> insert into SortingvarcharDemo values("1");
Query OK, 1 row affected (0.11 sec)

mysql> insert into SortingvarcharDemo values("999");
Query OK, 1 row affected (0.10 sec)

mysql> insert into SortingvarcharDemo values("78");
Query OK, 1 row affected (0.17 sec)

3. データを確認する

SELECT文で登録されたレコードを表示してみましょう。

mysql> select *from SortingvarcharDemo;

実行結果は以下の通りです。

+------+
| List |
+------+
| 99   |
| 9    |
| 199  |
| 1    |
| 999  |
| 78   |
+------+
6 rows in set (0.05 sec)

上記の出力を見ると、データがバラバラの順序で表示されており、昇順にも降順にもなっていないことがわかります。これは値が文字列として扱われているためです。

4. LPAD関数で数値順にソートする

VARCHAR型の値を数値順（昇順・降順）に並べ替えるには、以下の構文を使用します。

SELECT * FROM テーブル名 ORDER BY LPAD(lower(カラム名), 桁数, 埋め込み文字) asc;

LPAD関数は文字列の左側を指定した文字で埋め、桁数を揃えることで文字列比較でも数値と同じ順序になるようにします。実際のクエリは次のようになります。

mysql> SELECT * FROM SortingvarcharDemo ORDER BY LPAD(lower(List), 6,0) asc;

実行結果は以下の通りです。

+------+
| List |
+------+
| 1    |
| 9    |
| 78   |
| 99   |
| 199  |
| 999  |
+------+
6 rows in set (0.17 sec)

このように、LPAD関数で各値を6桁になるよう左側を「0」で埋めることで、文字列のままでも正しく昇順にソートされました。「1」→「000001」、「999」→「000999」と変換されるため、辞書順の比較結果が数値順と一致する仕組みです。

補足：CASTを使う代替手段

格納されている値がすべて純粋な数値である場合は、ORDER BY CAST(List AS UNSIGNED) のようにCAST関数で数値型に変換してソートする方法もあります。用途やデータの性質に応じて使い分けるとよいでしょう。