1. IPv4アドレスを整数で保存

  2. IPv6アドレスを整数で保存

    IPv4アドレスを整数で保存

    MySQLのテーブルにIPアドレス用カラムを作る場合、文字列か整数型の選択がありますが、 ここでは整数で保存する場合について説明します。

    • テーブル定義でIPアドレス用カラムの指定

      • IPv4アドレス用カラムには、INTEGERにUNSIGNEDを付けて、32bit符号なし整数で指定するか、 VARBINARY(4)を指定するかの2通りがあります。確認した範囲では、どちらも同じように動作しました。

      例 unsigned integerで定義 
      CREATE TABLE log(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  ip INTEGER UNSIGNED
);

      これで作成したテーブルは以下のようになっています。 

      mysql> desc log;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11)          | NO   | PRI | NULL    | auto_increment |
| ip    | int(10) unsigned | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

      注意. UNSIGNEDを指定せずに符号付き整数で定義すると、符号により正しくIPアドレスが保存できません。 32ビット符号付き整数値に変換した場合にマイナスになるIPアドレスは、全て "127.255.255.255"になってしまいます。

      例 IPアドレスのカラムを符号付き整数型にして、 "192.168.0.1"（32ビットの符号付き整数でマイナスになる値)をインサートする。 
      mysql> CREATE TABLE test(
    -> id INTEGER PRIMARY KEY AUTO_INCREMENT,
    -> ip INTEGER
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test(ip) VALUES(INET_ATON('192.168.0.1'));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT INET_NTOA(ip) FROM test;
+-----------------+
| INET_NTOA(ip)   |
+-----------------+
| 127.255.255.255 |
+-----------------+
1 row in set (0.00 sec)

    • 文字列のIPアドレスを整数に変換

      • 文字列のIPアドレスをテーブルに保存するためには整数値に変換する必要があります。 この場合、MySQLのINET_ATON()関数を使います。 この関数は、パラメータに指定した文字列のIPアドレスを整数値に変換してくれます。

      ・INET_ATON()　　(MySQL 5.6 Reference Manual) 

      mysql> SELECT inet_aton('127.0.0.1');
+------------------------+
| inet_aton('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+

      INET_ATON()で変換すると普通の整数値になるので、インサートでは以下のようになります。 

      INSERT INTO log(ip) VALUES(INET_ATON('127.0.0.1'));
INSERT INTO log(ip) VALUES(INET_ATON('255.255.255.254'));
INSERT INTO log(ip) VALUES(INET_ATON('192.168.1.1'));
INSERT INTO log(ip) VALUES(INET_ATON('10.1.2.3'));
      これを実行してIPアドレスを追加し、SELECTで確認すると以下のようになっています。 
      mysql> SELECT * FROM log;
+----+------------+
| id | ip         |
+----+------------+
|  1 | 2130706433 |
|  2 | 4294967294 |
|  3 | 3232235777 |
|  4 |  167838211 |
+----+------------+

      INSET_ATON()を使わず、整数値でインサートすることもできます。 例えば "10.0.0.1" の整数値は 10*256^3 + 0*256^2 + 0*256^1 + 1*256^0 と計算できるので、以下のようにすることもできます。 

      mysql> INSERT INTO log(id, ip) VALUES(5, 10*256*256*256 + 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT id, INET_NTOA(ip) FROM log where id=5;
+----+---------------+
| id | INET_NTOA(ip) |
+----+---------------+
|  5 | 10.0.0.1      |
+----+---------------+

    • 整数を文字列のIPアドレスに変換

      • 整数値を文字列のIPアドレスに変換する場合、MySQLのINET_NTOA()関数を使います。

      ・INET_NTOA()　　(MySQL 5.6 Reference Manual) 

      mysql> SELECT id, INET_NTOA(ip) FROM log;
+----+-----------------+
| id | INET_NTOA(ip)   |
+----+-----------------+
|  1 | 127.0.0.1       |
|  2 | 255.255.255.254 |
|  3 | 192.168.1.1     |
|  4 | 10.1.2.3        |
+----+-----------------+

    • WHERE句でのIPアドレス指定

      • WHERE句でもINET_ATON()は使用できます。

      例　'192.168.0.0'　～　'255.255.255.0'の範囲のIPアドレスを表示する。 
      mysql> SELECT id, INET_NTOA(ip) FROM log WHERE ip BETWEEN INET_ATON('192.168.0.0') AND INET_ATON('255.255.255.0');
+----+---------------+
| id | INET_NTOA(ip) |
+----+---------------+
|  3 | 192.168.1.1   |
+----+---------------+
1 row in set (0.00 sec)

      特定のIPアドレスのレコードを指定することも可能です。

      例　以下のようにIPアドレスが保存されている場合 
      mysql> SELECT id, INET_NTOA(ip) FROM log;
+----+-----------------+
| id | INET_NTOA(ip)   |
+----+-----------------+
|  1 | 127.0.0.1       |
|  2 | 255.255.255.254 |
|  3 | 192.168.1.1     |
|  4 | 10.1.2.3        |
+----+-----------------+
4 rows in set (0.00 sec)
      ここで "255.255.255.254" を "255.255.255.1" に更新(UPDATE)する場合 
      mysql> UPDATE log SET ip=INET_ATON('255.255.255.1') WHERE ip=INET_ATON('255.255.255.254');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

      正しく更新が行われていることを以下で確認。 

      mysql> SELECT id, INET_NTOA(ip) FROM log;
+----+---------------+
| id | INET_NTOA(ip) |
+----+---------------+
|  1 | 127.0.0.1     |
|  2 | 255.255.255.1 |
|  3 | 192.168.1.1   |
|  4 | 10.1.2.3      |
+----+---------------+
4 rows in set (0.00 sec)

    IPv6アドレスを整数で保存

    IPv6アドレスについて未確認ですが、VARBINARY(16)で型を定義して、INET6_ATON()、INET6_NTOA()を使うようです。

    ただし、INET6_ATON()、INET6_NTOA()は、バージョン 5.6.3から追加されたようです。