目次

MySQL - 数値に変換してソート

backup_sizeがvarcharの為、普通にソートするとこのようになってしまいます。 

mysql> select disk_id,backup_size from backups order by backup_size desc;
+---------+-------------+
| disk_id | backup_size |
+---------+-------------+
|      67 | 359728      |
|      65 | 359656      |
|    4570 | 240392      |
|    3949 | 18931152    |
|    3869 | 18379424    |
|    3869 | 18086912    |
|    3173 | 17994708    |
|    3173 | 17870688    |
|    3173 | 17870688    |
|    4933 | 170412      |
|    3829 | 14502832    |
|    3781 | NULL        |
+---------+-------------+
12 rows in set (0.01 sec)

こうすれば、数値に変換してソートできる。

0を足して、無理やり数値へ変換する。 

mysql> select disk_id,backup_size from backups order by backup_size + 0 desc;
+---------+-------------+
| disk_id | backup_size |
+---------+-------------+
|    3949 | 18931152    |
|    3869 | 18379424    |
|    3869 | 18086912    |
|    3173 | 17994708    |
|    3173 | 17870688    |
|    3173 | 17870688    |
|    3829 | 14502832    |
|      67 | 359728      |
|      65 | 359656      |
|    4570 | 240392      |
|    4933 | 170412      |
|    3781 | NULL        |
+---------+-------------+
12 rows in set (0.00 sec)

castを利用する場合

mysql> select disk_id,backup_size from backups order by cast( backup_size as SIGNED ) desc;
+---------+-------------+
| disk_id | backup_size |
+---------+-------------+
|    3949 | 18931152    |
|    3869 | 18379424    |
|    3869 | 18086912    |
|    3173 | 17994708    |
|    3173 | 17870688    |
|    3173 | 17870688    |
|    3829 | 14502832    |
|      67 | 359728      |
|      65 | 359656      |
|    4570 | 240392      |
|    4933 | 170412      |
|    3781 | NULL        |
+---------+-------------+
12 rows in set (0.00 sec)