MySQLでVARCHAR型として保存された数値を並べ替える方法｜「085」「090」など先頭に0が付く番号のソート

問題点：VARCHAR型の数値は文字列として並べ替えられる

MySQLでは、数値がVARCHAR型（文字列型）で保存されていると、辞書順（文字列順）でソートされます。そのため、「085」や「090」のように先頭に0が付く番号が含まれる場合、意図した通りの順序で並ばないことがあります。

この問題を解決するには、カラムに「*1」を掛けて暗黙的に数値へ変換してから並べ替えます。

基本構文

以下がその構文です −

select *from yourTableName order by yourColumnName*1,yourColumnName;

ポイントは、yourColumnName*1 で文字列を数値として評価して一次ソートを行い、同値の場合（例：「45」と「045」）には元のカラムで二次ソートする点です。これにより、先頭ゼロの有無に関わらず自然な順序で並べ替えができます。

実際に試してみよう

1. サンプルテーブルの作成

まずテーブルを作成します −

mysql> create table DemoTable
(
    Value varchar(100)
);
Query OK, 0 rows affected (0.62 sec)

2. レコードの挿入

insertコマンドを使用してレコードを挿入します −

mysql> insert into DemoTable values('90');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('86');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('45');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('85');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('085');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('090');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('045');
Query OK, 1 row affected (0.10 sec)

3. レコードの確認

select文ですべてのレコードを表示します −

mysql> select *from DemoTable;

この場合、次のような出力が得られます −

+-------+
| Value |
+-------+
|    90 |
|    86 |
|    45 |
|    85 |
|   085 |
|   090 |
|   045 |
+-------+
7 rows in set (0.00 sec)

先頭に0が付く番号を含むVARCHAR型の数値を並べ替えるクエリ

以下が、先頭ゼロ付きの番号を含むVARCHAR型の数値を正しくソートするためのクエリです −

mysql> select *from DemoTable order by Value*1,Value;

実行結果は次のとおりです −

+-------+
| Value |
+-------+
|   045 |
|    45 |
|   085 |
|    85 |
|    86 |
|   090 |
|    90 |
+-------+
7 rows in set (0.00 sec)

出力を見ると、「045」→「45」、「085」→「85」、「090」→「90」のように、先頭ゼロ付きの値が同じ数値の直前に配置され、全体が数値順にきれいに並んでいることがわかります。

補足：CAST関数を使う方法

「*1」による暗黙的な変換の代わりに、CAST関数を使って明示的に数値へ変換することもできます。

select *from DemoTable order by CAST(Value AS SIGNED),Value;

どちらの方法でも結果は同じですが、CAST関数の方が意図が明確で可読性が高いというメリットがあります。パフォーマンスを考慮する場合は、インデックスが効かなくなる点に注意してください。