IPv4アドレスを整数で保存
MySQLのテーブルにIPアドレス用カラムを作る場合、文字列か整数型の選択がありますが、 ここでは整数で保存する場合について説明します。
-
テーブル定義でIPアドレス用カラムの指定
IPv4アドレス用カラムには、INTEGERにUNSIGNEDを付けて、32bit符号なし整数で指定するか、 VARBINARY(4)を指定するかの2通りがあります。確認した範囲では、どちらも同じように動作しました。
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"になってしまいます。
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()は使用できます。
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アドレスのレコードを指定することも可能です。
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から追加されたようです。
- IPv6 and internationalized domain (IDNA) functions for MySQL
- Changes in MySQL 5.6.3 (2011-10-03, Milestone 6)