MySQLでCAST演算子を使ってVARCHAR列を数値（FLOAT）としてソートする方法

MySQLでは、VARCHAR型の列に数値データが格納されている場合、そのままORDER BY句でソートすると文字列として比較されるため、意図した順序にならないことがあります。CAST()演算子がうまく機能しない場合は、列名に「*1」を掛ける方法でも数値としてソートできます。

方法1：CAST()演算子を使用する

CAST()演算子を使ってVARCHAR型をDECIMAL型に変換してソートする構文は以下の通りです。

SELECT yourColumnName1,yourColumnName2,...N FROM yourTableName ORDER BY CAST(yourColumnName AS DECIMAL(8,2)) DESC;

方法2：列名に「*1」を掛ける

列名に「*1」を掛けることで、値が暗黙的に数値へ変換され、正しくソートできます。構文は以下の通りです。

SELECT yourColumnName1,yourColumnName2,...N FROM yourTableName ORDER BY yourColumnName*1 DESC;

動作確認用のテーブルを作成する

上記の構文を理解するために、実際にテーブルを作成してみましょう。テーブル作成のクエリは以下の通りです。

mysql> create table VarcharColumnAsFloatDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Amount varchar(20),
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (1.01 sec)

次に、insertコマンドを使ってテーブルにレコードを挿入します。クエリは以下の通りです。

mysql> insert into VarcharColumnAsFloatDemo(Amount) values('3446.23');
Query OK, 1 row affected (0.10 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('2464.46');
Query OK, 1 row affected (0.16 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('6465.78');
Query OK, 1 row affected (0.13 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('6464.98');
Query OK, 1 row affected (0.44 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('645.90');
Query OK, 1 row affected (0.19 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('6465.99');
Query OK, 1 row affected (0.23 sec)
mysql> insert into VarcharColumnAsFloatDemo(Amount) values('3745.76');
Query OK, 1 row affected (0.14 sec)

select文を使ってテーブル内の全レコードを表示します。クエリは以下の通りです。

mysql> select *from VarcharColumnAsFloatDemo;

以下が出力結果です。

+----+---------+
| Id | Amount  |
+----+---------+
|  1 | 3446.23 |
|  2 | 2464.46 |
|  3 | 6465.78 |
|  4 | 6464.98 |
|  5 | 645.90  |
|  6 | 6465.99 |
|  7 | 3745.76 |
+----+---------+
7 rows in set (0.00 sec)

CAST演算子でVARCHARを数値としてソートする

CAST演算子を使ってVARCHAR型の列を数値としてソートするクエリは以下の通りです。

mysql> select Id,Amount from VarcharColumnAsFloatDemo order by cast(Amount as DECIMAL(8,2)) DESC;

以下が出力結果です。Amountの値が数値順に降順ソートされていることがわかります。

+----+---------+
| Id | Amount  |
+----+---------+
|  6 | 6465.99 |
|  3 | 6465.78 |
|  4 | 6464.98 |
|  7 | 3745.76 |
|  1 | 3446.23 |
|  2 | 2464.46 |
|  5 | 645.90  |
+----+---------+
7 rows in set (0.00 sec)

「*1」を使ったソート方法

2つ目の方法は、列名に「*1」を掛ける方法です。クエリは以下の通りです。

mysql> select Id,Amount from VarcharColumnAsFloatDemo order by Amount*1 desc;

以下が出力結果です。CAST()を使った場合と同じ結果が得られます。

+----+---------+
| Id | Amount  |
+----+---------+
|  6 | 6465.99 |
|  3 | 6465.78 |
|  4 | 6464.98 |
|  7 | 3745.76 |
|  1 | 3446.23 |
|  2 | 2464.46 |
|  5 | 645.90  |
+----+---------+
7 rows in set (0.00 sec)

どちらの方法でも、VARCHAR型に格納された数値データを正しく数値順にソートできます。CAST()は変換の意図が明確で可読性が高い一方、「*1」は記述が簡潔というメリットがあります。用途に応じて使い分けるとよいでしょう。