MySQLで英数字の列を正しく並べ替える方法（ORDER BYの使い方）

MySQLでは、「2X」「100Y」「100Z」のような英数字が混在した文字列をそのまま ORDER BY で並べ替えると、文字列として比較されるため「100X」が「2X」より先に来てしまうことがあります。数値部分を正しく認識して自然な順序（2 → 100）でソートするには、少し工夫が必要です。

この記事では、英数字の列を数値順＋文字順で並べ替える具体的な方法を、テーブル作成から順に解説します。

1. サンプルテーブルを作成する

まず、英数字のIDを格納するテーブルを作成します。

mysql> create table DemoTable
-> (
-> StudentId varchar(100)
-> );
Query OK, 0 rows affected (0.52 sec)

2. テストデータを挿入する

INSERT コマンドを使って、いくつかのレコードを登録します。

mysql> insert into DemoTable values('2X');
Query OK, 1 row affected (0.21 sec)

mysql> insert into DemoTable values('100Y');
Query OK, 1 row affected (0.20 sec)

mysql> insert into DemoTable values('100X');
Query OK, 1 row affected (0.12 sec)

mysql> insert into DemoTable values('2Z');
Query OK, 1 row affected (0.14 sec)

mysql> insert into DemoTable values('2Y');
Query OK, 1 row affected (0.23 sec)

mysql> insert into DemoTable values('100Z');
Query OK, 1 row affected (0.17 sec)

3. 現在のデータを確認する

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

mysql> select *from DemoTable;

実行結果は以下のとおりです。挿入した順序のまま表示されていることがわかります。

+-----------+
| StudentId |
+-----------+
| 2X        |
| 100Y      |
| 100X      |
| 2Z        |
| 2Y        |
| 100Z      |
+-----------+
6 rows in set (0.00 sec)

4. 英数字列を並べ替えるクエリ

ここがポイントです。数値部分と末尾の文字部分を分けてソートキーとして指定します。

mysql> select *from DemoTable order by (StudentId+0), right(StudentId, 1);

このクエリの仕組みは以下のとおりです。

• (StudentId+0)：文字列に 0 を加算することで、MySQLが自動的に数値へ変換（暗黙の型変換）し、「2」と「100」を数値として比較できるようにします。
• right(StudentId, 1)：文字列の右端から1文字（この例では X / Y / Z の部分）を取り出し、同じ数値の場合はアルファベット順に並べるための第二ソートキーとして機能します。

5. 実行結果

上記のクエリを実行すると、次のように数値の昇順、さらに同じ数値内ではアルファベット順に整然と並びます。

+-----------+
| StudentId |
+-----------+
| 2X        |
| 2Y        |
| 2Z        |
| 100X      |
| 100Y      |
| 100Z      |
+-----------+
6 rows in set, 6 warnings (0.00 sec)

補足：注意点と応用

• 実行結果に「6 warnings」と表示されていますが、これは文字列から数値への暗黙の型変換による警告です。動作には問題ありませんが、気になる場合は CAST(StudentId AS UNSIGNED) を使うとより明示的になります。
• 末尾の文字が常に1文字とは限らないデータの場合は、RIGHT() の代わりに正規表現や SUBSTRING() を組み合わせて、数値部と文字部を分離する方法も検討しましょう。
• 大量データを扱う場合、関数を使用した ORDER BY はインデックスを利用できずパフォーマンスに影響することがあるため、必要に応じて数値用・接尾辞用のカラムを分けて設計するのも有効です。

このように、「数値化した値 + 末尾の文字」の2段階のソートキーを使えば、英数字混在の列でも直感的な順序で並べ替えることができます。