読者です 読者をやめる 読者になる 読者になる

MySQLでバージョンナンバーをソートする

2.6.4のようなバージョンを表す文字列でソートしたい。

普通にorder byすると「2.0.0」よりも「11.0.0」が小さいと判定されてしまう。

mysql> SELECT version FROM app ORDER BY version DESC;

                    • +
version
                    • +
2.0
11.0
1.1.1
1.0.1
1.0.0
1.0
1
                    • +

7 rows in set (0.00 sec)

各バージョン番号を.でsplitして別々のカラムに保存するのがよいのだけど、そういったカラム追加ができない場合はどうすればよいだろうか


調べた結果、INET_ATONを使用する方法があることがわかった。

INET_ATONはIPv4IPアドレスを数値で返す関数である。

参考:MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 7.11.4 その他の関数

mysql> SELECT version, FROM app ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version,'.0.0.0'),'.',4)) DESC;

                    • +
version
                    • +
11.0
2.0
1.1.1
1.0.1
1.0.0
1.0
1
                    • +

7 rows in set (0.01 sec)


CONCAT、SUBSTRING_INDEX、INET_ATONで値は以下のように変化する。

mysql> SELECT version, CONCAT(version,'.0.0.0'), SUBSTRING_INDEX(CONCAT(version,'.0.0.0'),'.',4), INET_ATON(SUBSTRING_INDEX(CONCAT(version,'.0.0.0'),'.',4)) FROM app ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version,'.0.0.0'),'.',4)) DESC;

                                                                                                                                                                                                                                                                                                        • +
version CONCAT(version,'.0.0.0') SUBSTRING_INDEX(CONCAT(version,'.0.0.0'),'.',4) INET_ATON(SUBSTRING_INDEX(CONCAT(version,'.0.0.0'),'.',4))
                                                                                                                                                                                                                                                                                                        • +
11.0 11.0.0.0.0 11.0.0.0 184549376
2.0 2.0.0.0.0 2.0.0.0 33554432
1.1.1 1.1.1.0.0.0 1.1.1.0 16843008
1.0.1 1.0.1.0.0.0 1.0.1.0 16777472
1.0.0 1.0.0.0.0.0 1.0.0.0 16777216
1.0 1.0.0.0.0 1.0.0.0 16777216
1 1.0.0.0 1.0.0.0 16777216
                                                                                                                                                                                                                                                                                                        • +

7 rows in set (0.00 sec)

本来IPアドレス表現をは変換するための関数なので、各バージョン番号が255を超える場合は、正しい結果にならないことに注意


参考:mysql sorting of version numbers - Stack Overflow