MySQLでIPアドレスを格納した列を正しくソートする方法

MySQLでIPアドレスを文字列として保存している場合、単純に ORDER BY を使うと文字列として比較されるため、意図しない順序になってしまうことがあります。例えば「192.168.110.87」が「192.168.110.78」より前に来てしまうといったケースです。

この問題を解決するには、INET_ATON() 関数を使用します。INET_ATON() はIPアドレスの文字列を数値に変換する関数で、これを ORDER BY と組み合わせることで、数値として正しくソートできるようになります。

テーブルの作成

まず、サンプル用のテーブルを作成しましょう。

mysql> create table DemoTable
-> (
-> IpAddress varchar(50)
-> );
Query OK, 0 rows affected (1.36 sec)

データの挿入

次に、INSERT文を使っていくつかのレコードを挿入します。

mysql> insert into DemoTable values('192.168.110.78');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values('192.168.110.87');
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable values('192.168.110.75');
Query OK, 1 row affected (0.26 sec)

登録データの確認

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

mysql> select *from DemoTable;

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

+----------------+
|      IpAdress  |
+----------------+
| 192.168.110.78 |
| 192.168.110.87 |
| 192.168.110.75 |
+----------------+
3 rows in set (0.00 sec)

INET_ATON()を使ったソート

ここで、IPアドレスを正しい順序で並べ替えるクエリを見てみましょう。ORDER BY の中で INET_ATON() を使うのがポイントです。

mysql> select *from DemoTable
-> order by inet_aton(IpAddress);

実行すると、以下のようにIPアドレスが数値的な順序で正しくソートされます。

+----------------+
|     IpAddress  |
+----------------+
| 192.168.110.75 |
| 192.168.110.78 |
| 192.168.110.87 |
+----------------+
3 rows in set (0.00 sec)

補足：逆変換にはINET_NTOA()

なお、INET_ATON() の逆の処理、つまり数値からIPアドレス文字列への変換を行いたい場合は、INET_NTOA() 関数を使用します。この2つの関数を組み合わせれば、IPアドレスの保存形式と表示形式を柔軟に扱うことができます。

また、MySQL 5.6.3以降ではIPv6アドレスにも対応した VARBINARY 型での保存や、IS_IPV4()・IS_IPV6() などの関数も利用できるため、より厳密な管理が必要な場合はこれらの活用も検討するとよいでしょう。