MySQLのORDER BYが文字列としてソートされる問題の解決法｜VARCHAR型カラムを数値順に並べ替えるテクニック

VARCHAR型カラムを数値として並べ替える方法

MySQLでは、数値データがVARCHAR型（文字列型）のカラムに格納されている場合、ORDER BY句を使うと文字列として比較・ソートされてしまいます。その結果、「98」「50」「-100」のような値が辞書順に並び、意図した数値順の並びになりません。

このような場合は、カラム名に「+0」を加えることで、文字列を数値に変換してソートできます。基本的な構文は以下の通りです。

SELECT yourColumnName FROM yourTableName ORDER BY yourColumnName + 0 DESC;

検証用テーブルの作成

実際の動作を確認するため、以下のクエリでテーブルを作成します。

mysql> create table selectOrderdemo
    -> (
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Name varchar(100),
    -> RankNumber varchar(100)
    -> );
Query OK, 0 rows affected (0.55 sec)

レコードの挿入

INSERTコマンドを使って、テーブルにいくつかのレコードを挿入します。

mysql> insert into selectOrderdemo(Name,RankNumber) values('Larry',-100);
Query OK, 1 row affected (0.23 sec)
mysql> insert into selectOrderdemo(Name,RankNumber) values('John',50);
Query OK, 1 row affected (0.13 sec)
mysql> insert into selectOrderdemo(Name,RankNumber) values('Bob',0);
Query OK, 1 row affected (0.14 sec)
mysql> insert into selectOrderdemo(Name,RankNumber) values('Carol',-110);
Query OK, 1 row affected (0.23 sec)
mysql> insert into selectOrderdemo(Name,RankNumber) values('David',98);
Query OK, 1 row affected (0.21 sec)

テーブルの全レコードを表示

SELECT文で全レコードを表示してみましょう。

mysql> select * from selectOrderdemo;

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

+----+-------+------------+
| Id | Name  | RankNumber |
+----+-------+------------+
| 1  | Larry | -100       |
| 2  | John  | 50         |
| 3  | Bob   | 0          |
| 4  | Carol | -110       |
| 5  | David | 98         |
+----+-------+------------+
5 rows in set (0.00 sec)

数値として並べ替えるクエリの実例

ここからは、「+0」を使って数値としてソートするクエリを3つのケースに分けて紹介します。

ケース1：降順（DESC）でソートする場合

結果を降順で取得したい場合は、以下のクエリを使用します。

mysql> select RankNumber FROM selectOrderdemo ORDER BY RankNumber+0 DESC;

実行結果：

+------------+
| RankNumber |
+------------+
| 98         |
| 50         |
| 0          |
| -100       |
| -110       |
+------------+
5 rows in set (0.00 sec)

ケース2：昇順（ASC）でソートする場合

結果を昇順で取得したい場合は、DESCを外した以下のクエリを使用します。

mysql> select RankNumber FROM selectOrderdemo ORDER BY RankNumber+0;

実行結果：

+------------+
| RankNumber |
+------------+
| -110       |
| -100       |
| 0          |
| 50         |
| 98         |
+------------+
5 rows in set (0.00 sec)

ケース3：すべてのカラムを取得する場合

すべてのカラムを含めてソートしたい場合は、以下のクエリを使用します。

mysql> select * FROM selectOrderdemo ORDER BY RankNumber+0 DESC;

実行結果：

+----+-------+------------+
| Id | Name  | RankNumber |
+----+-------+------------+
| 5  | David | 98         |
| 2  | John  | 50         |
| 3  | Bob   | 0          |
| 1  | Larry | -100       |
| 4  | Carol | -110       |
+----+-------+------------+
5 rows in set (0.00 sec)

まとめ

VARCHAR型のカラムに格納された数値を正しくソートするには、ORDER BY句でカラム名に「+0」を付けるのが最も簡単な方法です。これは文字列が暗黙的に数値へ変換される仕組み（暗黙の型変換）を利用したテクニックです。なお、大量のデータを扱う場合はこの方法だとインデックスが利用できずパフォーマンスに影響する可能性があるため、可能であれば数値カラムはINT型など適切なデータ型で定義することをおすすめします。