MySQLで日付形式ではないカラムをORDER BYで正しくソートする方法

VARCHAR型などで保存された「DD/MM/YYYY」形式の日付文字列は、そのままORDER BY句で並び替えると文字列として比較されるため、正しい日付順になりません。このような場合には、STR_TO_DATE関数を使って文字列を日付値に変換してからソートすることで解決できます。

基本構文

select *from yourTableName
order by
STR_TO_DATE(yourColumnName,'%d/%m/%Y') DESC;

フォーマット指定子の「%d」は日、「%m」は月、「%Y」は4桁の年を表します。昇順で並べたい場合は「DESC」を「ASC」に変更してください。

動作確認用テーブルの作成

それでは、実際に構文を確認するためのサンプルテーブルを作成しましょう。以下のクエリを実行します。

mysql> create table orderByDateFormatDemo
    -> (
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> ArrivalDatetime varchar(100)
    -> );
Query OK, 0 rows affected (0.73 sec)

ここではArrivalDatetimeカラムをvarchar(100)型として定義し、あえて日付型ではない状態でデータを格納できるようにしています。

テストデータの挿入

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

mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('01/10/2012');
Query OK, 1 row affected (0.20 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('03/11/2010');
Query OK, 1 row affected (0.15 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('04/09/2018');
Query OK, 1 row affected (0.14 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('31/01/2019');
Query OK, 1 row affected (0.17 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('21/04/2017');
Query OK, 1 row affected (0.21 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('23/05/2016');
Query OK, 1 row affected (0.12 sec)

登録データの確認

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

mysql> select *from orderByDateFormatDemo;

出力結果は次のとおりです。

+----+-----------------+
| Id | ArrivalDatetime |
+----+-----------------+
|  1 | 01/10/2012      |
|  2 | 03/11/2010      |
|  3 | 04/09/2018      |
|  4 | 31/01/2019      |
|  5 | 21/04/2017      |
|  6 | 23/05/2016      |
+----+-----------------+
6 rows in set (0.00 sec)

データは文字列として保存されているため、単純なORDER BYでは日付順に並び替えることができません。

STR_TO_DATE関数で日付順にソートする

以下が、日付形式ではないカラムをORDER BYで正しく並び替えるクエリです。

mysql> select *from orderByDateFormatDemo
    -> order by
    -> STR_TO_DATE(ArrivalDatetime,'%d/%m/%Y') DESC;

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

+----+-----------------+
| Id | ArrivalDatetime |
+----+-----------------+
|  4 | 31/01/2019      |
|  3 | 04/09/2018      |
|  5 | 21/04/2017      |
|  6 | 23/05/2016      |
|  1 | 01/10/2012      |
|  2 | 03/11/2010      |
+----+-----------------+
6 rows in set (0.04 sec)

新しい日付から古い日付へ、降順（DESC）で正しく並び替えられていることが確認できます。

補足：パフォーマンスと設計上の注意点

STR_TO_DATE関数をORDER BY内で使用すると、行ごとに関数が評価されるためインデックスが利用できず、レコード数が多い場合はパフォーマンスが低下する可能性があります。可能であれば、日付カラムはDATE型やDATETIME型で定義し、表示時にDATE_FORMAT関数で整形する設計が推奨されます。既存テーブルの変更が難しい場合でも、生成カラム（Generated Column）にインデックスを作成することで、ソート処理を高速化できます。